+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Help needed with SQL query (/showthread.php?tid=39379)
Help needed with SQL query - El Forum - 04-22-2011
[eluser]RussC[/eluser]
I'm having trouble with the foreach loop you described. I applied it to my own code as so:
Code:
<dl>
<?php
foreach($day->all as $d): ?>
<dt><?php echo $d->day;?></dt>
<?php foreach($times->all as $t):?>
<?php if ($d->day == $t->day){?>
<dd><?php echo $t->start." - ".$t->end;?></dd>
<?php } ?>
<?php endforeach; ?>
<?php endforeach; ?>
</dl>..the loop does what I want within the DL, but it does so for each row. Here is the output:
2
15:30:00 - 16:00:00
5
12:00:00 - 12:30:00
16:00:00 - 16:30:00
5
12:00:00 - 12:30:00
16:00:00 - 16:30:00
Of course I only want day 5 to show up once. This seems like any easy fix, but I'm stuck.
Help needed with SQL query - El Forum - 04-22-2011
[eluser]Brad K Morse[/eluser]
Do a print_r for $day and $times and paste those here.
Help needed with SQL query - El Forum - 04-22-2011
[eluser]RussC[/eluser]
print_r outputs a lot here's part of it:
Course Object ( [has_many] => Array ( [0] => user ) [has_one] => Array ( [0] => time ) [error] => stdClass Object ( [all] => Array ( ) [string] => [id] => [type] => [teacher_id] => [day] => [start] => [end] => [active] => [inactive] => [recurrence] => [timezone] => ) [stored] => stdClass Object ( [id] => 2 [type] => Lesson [teacher_id] => 2 [day] => 2 [start] => 15:30:00 [end] => 16:00:00 [active] => 2011-04-10 [inactive] => 2011-05-15 [recurrence] => 7 [timezone] => 0 ) [prefix] => [join_prefix] => [table] => courses [model] => course [error_prefix] =>
If it makes a difference, I used the same query method for $day and $times because this is all from the same table.
Help needed with SQL query - El Forum - 04-22-2011
[eluser]Brad K Morse[/eluser]
You want to print out the start/end time for each day, in this format:
Day #
start time - end time
correct?
You have table that contains all the days, then a table that contains all the end times, and another that contains all the begin times?
Can you describe your table(s) structure and supply the query?
Help needed with SQL query - El Forum - 04-22-2011
[eluser]RussC[/eluser]
Yes, that is what I am aiming for.
Deciding on the best table structure for this has been the hardest part here. I have a courses table which has start, end, and day in it. The courses table has a many to many relationship with my users table (joined together in courses_users via DataMapper). Those relationships all print out fine, but I keep running into this problem with the days. Do I need to separate out the courses table into related tables as well? Maybe have just the start and end time in the course table, then have a day table and join them in a courses_days table? Thanks for all your help, this has been holding me up for a while.
Help needed with SQL query - El Forum - 04-22-2011
[eluser]Brad K Morse[/eluser]
You should have a table for courses, times, days, users, users_courses
Courses table will stores all the courses available: c.id, c.title
Users table will store users info: u.id, u.first_name, u.last_name
Time table will store each courses day, start time, end time: t.id, t.day_id, t.course_id, t.start_time, t.end_time
Days table will store all the days: d.id, d.name
users_courses table will store: uc.user_id, uc.course_id
Help needed with SQL query - El Forum - 04-22-2011
[eluser]RussC[/eluser]
If day, start time, and end time are all in the same table, then how would I loop though them echoing out the day only once?
Help needed with SQL query - El Forum - 04-22-2011
[eluser]Brad K Morse[/eluser]
What dictates the start and end time for each day?
Help needed with SQL query - El Forum - 04-22-2011
[eluser]RussC[/eluser]
This application is for a recurring weekly teaching schedule. The start and end times are in reference to the courses. The day is in reference to day of the week. So every monday would have a 3:00 - 3:30 slot. I hope that answers your question.
Help needed with SQL query - El Forum - 04-22-2011
[eluser]Brad K Morse[/eluser]
You're passing in the teacher's id, to grab all the course's start-end times each day of the week?