Help required! How do I store more than one value in a field

shuffweb

Hi everyone, ive checked the boards to see if there is a similar topic with no joy.
I have a basic problem that I cant really find a solution to, and if possible would like one of you MySQL gurus to help me out.

Scenario:
For my final year uni project at uni I have to create an online attendance system using PHP & MySQL, I have a query that retrieves a list of teaching sessions timetabled for the current week and day for a chosen teaching module.

Bearing in mind the nature of the system, the week element is not in synch with a calendar year, for this reason I have created a reference table called 'week' which stores the 'start_date' and 'end_date' for 31 weeks.

In a seperate table named 'session' is a list of all timetabled sessions, which leads to my problem.

Problem: In the field named 'week_id'in the 'session' table I wish to store a number of values, for example a session could be timetabled to take place on weeks 1-9 and another weeks 24-31.
What field type should I use to do this, I have tried SET and ENUM to no avail.

This is the relevant query in my code, would it need to be changed at all, I think the 'IN' condition in the clause is incorrect.

"SELECT session.session_id, session.session_type, session.time, session.room_no, module.module_title 						
						FROM session, module 
						WHERE module.module_id = '{$_SESSION['module_id']}' AND session.module_id = '{$_SESSION['module_id']}'
						AND session.day = '{$_SESSION['today']}'
						AND session.week_id IN '{$_SESSION['week_number']}' 
						ORDER BY time ASC ";

Any help or tips would be muchly appreciated,

Many Thanks - Dan.c

sneakyimp

The 'IN' clause in SQL expects a comma delimited list of values like (1,2,3,4). If $_SESSION['week_number'] is an array , you can use [man]implode[/man] to convert it ....perhaps like this:

"SELECT session.session_id, session.session_type, session.time, session.room_no, module.module_title 						
						FROM session, module 
						WHERE module.module_id = '{$_SESSION['module_id']}' AND session.module_id = '{$_SESSION['module_id']}'
						AND session.day = '{$_SESSION['today']}'
						AND session.week_id IN '" . implode(',', $_SESSION['week_number']) . "' 
						ORDER BY time ASC ";

As for 'storing multiple values in a single field' that is an entirely different issue. Proper database design would recommend that you create an additional table for the 'multiple values' object to associate with your main record. In a situation where you have 'customers' and 'orders' and each customer may have several orders on record, you should have a table for customers and a table for orders.

In some cases, you can get around this by using [man]serialize[/man]. This is a function which will turn an array or object into a string. Think of it as a 'dehydrated' or compressed version of the original data object. You can store a the results of that command in any varchar or text field. But you have to be careful. It's easy to create objects that serialize to more than 255 characters -- the upper limit for a varchar field.