MySQL query question

software development

#1

I’m trying to develop a website for my work and hae been stuck for three weeks on one issue. Hope someone can help me.

We have scheduled sessions with on-line clients, and want to build a table for each employee and their schedule. We are at work from 5 to 10PM. Our sessions are 30 minute blocks.

I have a table that holds the contacts data, name, phone num, unique ID etc…, and a table with the employees info. Lastly I have a third table that has 4 columns. ID (unique), teacher_id, student_id, and time.

My query is thus:
$get_sked = “SELECT student_list.id, student_list.name, student_list.skype_id, teacher_sked.time FROM teacher_sked, student_list
WHERE student_list.id = teacher_sked.student_id AND teacher_sked.teacher_id = “.$_SESSION[“id”].” ORDER BY time”;

My while loop is:
while ($query = mysqli_fetch_array($get_sked_res)) {
$student = $query[“name”];
$student_id = $query[“id”];
$time = date(“g:i”, strtotime($query[“time”]));
$skype_id = $query[“skype_id”];

    $teacher_sked .= "<tr><td height=50px style='text-align:center;>".$time
".$student."Call } $teacher_sked .= "";

I’d like to print (on screen) an entire daily schedule, where there is no appointment that it should say “None” or something similiar. This seems to be outside my ability but hope someone can help. This is working but to add the last piece of info is to hard for me.


#2

Consider writing it down in English first, known as pseudo-code.

So if you were to do this by hand, you would start with the first “time” of 5pm, check the database if there is an appointment at 5pm, write down either the appointment information or “None”, and move on until the next “time” until you are at 10pm.

Or in pseudo-code:

for every possible time check the database for an appointment at that time if there is an appointment write the time and appointment information otherwise write the time and "None"

So the obvious problem is your database doesn’t keep records for appointment times, just appointments. You should consider uising a lookup table to avoid making a database query for each possible appointment time.
[hr]
Another way to go about it of course is to keep track of the last appointment time, and if the current appointment time is more than 1 block later, output the skipped appointment time(s) first.