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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shuffweb
Forum Newbie
Posts: 5
Joined: Fri Apr 11, 2008 11:28 am

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

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

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

Post 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.
shuffweb
Forum Newbie
Posts: 5
Joined: Fri Apr 11, 2008 11:28 am

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

Post 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.
shuffweb
Forum Newbie
Posts: 5
Joined: Fri Apr 11, 2008 11:28 am

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

Post 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
Post Reply