Page 1 of 1

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

Posted: Mon Apr 14, 2008 1:44 pm
by 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.

Code: Select all

 
"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

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

Posted: Mon Apr 14, 2008 1:54 pm
by onion2k
You can't. You'll need a second table to store the week data, and then you'd join the two tables together on the session id. I'd probably do it by range ... so if you have record 1 in the session table you'd have 2 records in the timetable table that would store..

Code: Select all

session_id start_week end_week
1          1          9
1          24         31
Of course, that would make it a bit tricky to do a query for, say, all the sessions during a specific week, but that's still possible with some looping in PHP. How you store the data really depends on what you're planning to do with it.

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

Posted: Tue Apr 15, 2008 5:43 am
by shuffweb
Cheers your a star, I will have a play around with it today, but that sounds like a solution to me.
However, I could be back if I get stuck on the query.

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

Posted: Tue Apr 15, 2008 12:27 pm
by shuffweb
Just as expected, im officially stuck, I created a table called 'session_week' like you suggested and populated it with the required data.
On my PHP page I tried the following query, to retrieve any timetabled session for the current day and week, but I get the following error..

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\project\www\registersystem\session.php on line 138"


Here is the query...

Code: Select all

 
 
$query  = 
                        "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 '(SELECT session.session_id, session.session_type, session_week.start_week, session_week.end_week
                            FROM session
                            LEFT OUTER JOIN session_week
                            ON session.session_id = session_week.session_id;)'
                        AND '{$_SESSION['week_number']}' BETWEEN session_week.start_week AND session_week.end_week
                        ORDER BY time ASC ";
                        
 
Can you see anything that is incorrect in the query, which would display the mentioned error message.


Thaks in advance - Dan C