multiple select query help

[dannyone]

hello everyone, i am really struggling with the concept on doing multiple querys and returning data if it matchs.

 

i am trying to get a user to log in using a session, then search two tables:

 

timetable table:

id, course_id, RoomName,time_slot,day

 

Student_ID table:

id, Student_ID, FName, LName, module1, module2, module3, module4, module5, module6, module7

 

i am trying to get the query to display the timetable for the student in a table, were Student_ID.module1 == timetable.course_id, Student_ID.module2 == timetable.course_id etc...

 

can anyone help me with this? here is my attempt at it so far, it current output is a table showing all of the courses listed in timetable, i need it to only display the ones that the student has logged in is taking.

 

my code:

 

<?php

 

session_start();

if(!session_is_registered(myusername)){

header("location:Template.html");

}

$myusername = ($_GET['myusername']);

 

// Create an array of days

$days = array('mon' => '', 'tue' => '', 'wed' => '', 'thu' => '', 'fri' => '');

 

// Add our list of days to our time slots

$times = array(

'9-10' => $days,

'10-11' => $days,

'11-12' => $days,

'12-1' => $days,

'1-2' => $days,

'2-3' => $days,

'3-4' => $days,

'4-5' => $days,

'5-6' => $days);

 

 

// Connect to DB and run our query

$sql = new mysqli('localhost', 'user', 'pass', 'Timeslot_Allocation');

$run = $sql->query ("SELECT * FROM timetable");

 

while($row = $run->fetch_object())

{

// $times[$row->time_slot][$row->day] = "modules: $row->$course_id<br />Room: $row->RoomName";

$times[$row->time_slot][$row->day] = "modules: ".$row->course_id."<br>Room: ".$row->RoomName;

}

 

mysql_connect("localhost", "user", "pass");

mysql_select_db("Timeslot_Allocation")or die(mysql_error());

 

$query = "SELECT Student_ID.module1, Student_ID.module2, Student_ID.module3, Student_ID.module4, Student_ID.module5, Student_ID.module6, Student_ID.module7, timetable.course_id, timetable.time_slot, timetable.RoomName, timetable.day ".

"FROM Student_ID, timetable ".

"WHERE Student_ID.module1 = timetable.course_id AND Student_ID.module2 = timetable.course_id AND Student_ID.module3 = timetable.course_id AND Student_ID.module4 = timetable.course_id AND Student_ID.module5 = timetable.course_id AND Student_ID.module6 = timetable.course_id AND Student_ID.module7 = timetable.course_id ";

$result = mysql_query($query) or die(mysql_error());

 

while($row = mysql_fetch_array($result))

{

 

$slot[$result->module1][$result->course_id];

$slot[$result->module2][$result->course_id];

$slot[$result->module3][$result->course_id];

$slot[$result->module4][$result->course_id];

$slot[$result->module5][$result->course_id];

$slot[$result->module6][$result->course_id];

$slot[$result->module7][$result->course_id];

 

}

 

$body = <<< endBody

<center>

<table border="1" cellspacing="0" cellpadding="2">

<tr>

<th>Time Slot</th>

<th>Monday</th>

<th>Tuesday</th>

<th>Wednesday</th>

<th>Thursday</th>

<th>Friday</th>

</tr>

endBody;

 

// For each time slot, check each day for a value

// If one exists, display it, if not, display an empty table data (<td>)

 

 

foreach($times as $timeslot => $days)

{

$body .= "\n\t\t<tr>\n\t\t\t<td>$timeslot</td>";

foreach($days as $slot)

{

$td = (strlen($slot) > 0) ? $slot : ' ';

$body .= "\n\t\t\t<td>$td</td>";

}

$body .= "\n\t\t</tr>";

}

 

$body .= "\n\t</table>\n</center";

 

echo $body;

?>

 

thanks in advance

 

Danny