A shedule like this is itself an array, where each cell can be identified by a day of the week and an hour, so it seems like I should be able to build an array, then populate the schedule with a single call to the database. It would be easy to build the schedule by making 70 separate queries, one for each cell, but there must be a more efficient way.
Here is the code I am currently using:
Code: Select all
$sql=$dbh->prepare("SELECT * FROM course_dates, courses WHERE course_dates.course_id=courses.course_id AND date_or_days='days'AND substr(course_start_time, 1,2)=:hour");
$sql->bindValue("hour", "06");
$sql->execute();
while($sql2 = $sql->fetch(PDO::FETCH_ASSOC)){
extract($sql2);
$course_end_time=substr($course_end_time, 1, 4);
if ($sunday==1){
echo "<td style='background-color:#$color'><a href='/course.php?$course_id'>$course_title</a></td>";
} else { echo "<td> </td>";}
In the above, I change the value of hour for each row, and each column is populated beased on whether Sunday==1, Monday==1, etc. But the result is incorrect because, as you can see in the image, the pink items, which should populate on Sunday and Saturday, are coming after the record that populates on Wednesday and Friday.
So my thought is that this must be a pattern that some of you have solved before, and even though the field names would be different, the right code would be easy to modify for the current sitation. I'm hoping there is a way to query the database once, building the array, then populate the table with the result. That's why I'm thinking there might be a tutorial or example out there that someone can direct me to.
ADDED LATER: I have realized that instead of giving each day of the week a separate column in the table I should have one column, "day" and assign the days of the week numverically, thus sunday=1, tuesday=3. This should make it easier to build a multidimensional array using time and day as the two keys.