Page 1 of 1

Probably an array issue

Posted: Tue Dec 20, 2011 6:52 pm
by rhecker
I am trying to populate a weekly schedule html table based on values from a mysql table. In the example image, you see that the pink items appear off the grid whearas they should actually appear on sunday and saturday. The problem is that the database query is processing one row at a time, so a second record with entries for 6AM gets pushed to the side.

I could provide code, but since I think my code is so very wrong, I am hoping someone can just point me in the right direction for handling a situation like this correctly. Maybe atutorial or an example somewhere.
grid.jpg

Re: Probably an array issue

Posted: Tue Dec 20, 2011 7:43 pm
by social_experiment
rhecker wrote:I could provide code
Please do :)

Re: Probably an array issue

Posted: Wed Dec 21, 2011 9:21 am
by rhecker
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>&nbsp;</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.

Re: Probably an array issue

Posted: Wed Dec 21, 2011 10:04 pm
by rhecker
I have been working on this problem and made some improvements. I now have a more specific question. I have changed the query to the following:

Code: Select all

$sql=$dbh->prepare("SELECT course_dates.course_id, date_id, course_start_time, course_end_time, course_day, color, hour(course_start_time)AS starttime FROM course_dates, courses WHERE course_dates.course_id=courses.course_id AND date_or_days='days' ORDER BY starttime, course_day");
The starttime alias gives me an integer for the hour and courseday gives me an integer for the day of the week. Together the two indicate a particular cell in the schedule grid. So if the query returns 30 records, I need to populate cell 9:3 with the $course_title (etc) for the record that is at 9 AM on the third day of the week (Tuesday), and I would like to do this for all records with just one query.