sonphish

PFMaBiSmAd, Thanks for walking me through these steps. The code seems to be working perfectly. I've tested it in 3 different places and it's outputting exactly how I want it to. The CSS I've applied is even working it's magic. Creating headers sure saves time, and unclutters the code. Now I get to go back to some other files and clean them up. Wish I would have known this before, would have saved me lots of time. I appreciate the time you have taken and your patience in walking me step by step.

Here's the latest code which has gotten the heading [date] to work. $weekly_formation = mysql_query(" (SELECT Id, date, time, title, campus, room, ministry, category FROM formation LEFT JOIN dates on formation.dateId=dates.dateId LEFT JOIN time ON formation.timeId=time.timeId LEFT JOIN campus ON formation.campusId=campus.campusId LEFT JOIN rooms ON formation.roomId=rooms.roomId LEFT JOIN ministries on formation.ministryId=ministries.ministryId LEFT JOIN category on formation.categoryId=category.categoryId WHERE category='formation' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL +6 DAY)) UNION (SELECT Id, date, time, title, campus, room, ministry, category FROM events LEFT JOIN dates on events.dateId=dates.dateId LEFT JOIN time ON events.timeId=time.timeId LEFT JOIN campus ON events.campusId=campus.campusId LEFT JOIN rooms ON events.roomId=rooms.roomId LEFT JOIN ministries on events.ministryId=ministries.ministryId LEFT JOIN category on events.categoryId=category.categoryId WHERE category='events' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL +6 DAY)) ORDER BY date, time " ) or die(mysql_error()); $last_heading = null; while($row = mysql_fetch_assoc( $weekly_formation )){ $date=date_create($row['date']);//grab date from db $startDate=date_format($date,'m/d/Y');//format date for viewing on page $time=date_create($row['time']);//grab time for db $startTime=date_format($time, 'g:i a');//format time for viewing on page $new_heading = $row['date']; // get the column in the data that represents the heading if($last_heading != $new_heading){ // heading changed or is the first one if($last_heading != null){ // not the first section, close out the previous section here... echo ''; //(optionally) do what is needed to close out the previous section; } else { // is the first section, output the one-time heading here... echo ''; //$row['date'] . '<br />'; } $last_heading = $new_heading; // remember the new heading // start a new section, output the heading here... echo "$startDate" . "<br />"; } echo "$startTime" . " " . $row['title'] . '<br />'; } if($last_heading != null){ // there was at least one section, close out the last section here... echo ""; } ?> I am struggling to get the sub_header to work. wondering where in WHILE statement to drop the sub_header part?? AFter some trial and error, I gave up and figured some experience would save me some time. thx

Thank you for the reply and the link. I will be playing with it shortly. From the table set up, i guess you can see that I'm relatively new to PHP MYSQL. I have an html form that is populating the tables. The form works in such a way that a user (me) selects the date and time by drop down boxes. I have created two separate tables which give selectable values for the the drop downs so that a date was not tied to a time. Currently the form that gathers event information is not set up for reoccurances. I was going to begin working on making the events reoccur, once I had the information rendering properly into my site. Based on your suggestion and the examples of others, I'll look into a datetime table. Unfortunately, in my ignorance, I'm not only creating/learing. I'm also needing to keep a website current with the event information. thanks again for your suggestions

Thank you for taking some time to help me figure out how to render the information the way I'd like to. I have several tables that JOIN the main table. For simplicity we can work with just the three tables that are immediately relevant. EVENTS TABLE eventId - title - - dateId - timeId 1 event 1 1 2 event 3 4 3 event 3 2 4 event 3 3 5 event 4 3 DATES TABLE dateId - date 1 2012-01-20 2 2012-01-21 3 2012-01-22 4 2012-01-23 TIME TABLE timeId - time 1 06:00 2 12:00 3 16:00 4 18:00 5 18:30 SELECT title, date, time FROM events LEFT JOIN dates ON events.dateId=dates.dateId LEFT JOIN time ON events.timeId=time.timeId ORDER BY date,time THis renders will render the list no problem. I want to be able to determine what day it is: I use php to figure out the day, then render information based on the dates being equal: $today=date('Y-m-d') SELECT title, date, time FROM events LEFT JOIN dates ON events.dateId=dates.dateId LEFT JOIN time ON events.timeId=time.timeId WHERE date='$today' ORDER BY time This works great for pulling information that matches the current date. I'm having trouble grabbing information for the following days in the (for this example) 4 day span, where day two may not have any events. If I wrap the php in a WHILE statment, the date would repeat for each event and the time would repeat for each event if the events share the same time. I would like to render it like this. January, 20 2012 (I know how to format date) 6:00 a.m. - event (again, I know how to format the date) January 21, 2012 (skip, not events) January 22, 2012 12:00p.m. - event (dates formatted and events sorted by time - get it, understand it) 4:00 p.m - event 6:00 p.m. - event January 23, 2012 4:00p.m - event (For this date, the time is only listed once since both events share the same time) event I've been using php to determind todays date, tomorrows date, day after tomorrows date. I use the calculated date as the WHERE value which matches to the date of the event Since I only wanted the date to appear once, I've echoed it outside (above) the WHILE statement. I've thought about creating an IF statent to determine if the date has any events, I've been unable to figure out the how to do it. Then decided if there was any approach anyone could offer. Here we sit. Thanks again for your insight...and anyone's.

GROUP BY. When I try to GROUP BY date it only returns the first entry for that date. If I try to GROUP BY time it only returns the first entry for that time. Was thinking of trying to implement a foreach or for loop, but am lost.

I am trying to create a daily event listing. The list will have Day & Date as the heading. Then it will list the events ORDERED BY the time. First thing I'd like to do is skip or not show the days that do not have an event. Ex. Monday, January 23, 2012 7am event 8am event 6pm event since Tuesday has no events, I'd like tuesday to not be in the list, but if the next tuesday has an event It'll need be visible. Tuesday, January 24, 2012 (No events) Wednesday, January 25, 2012 9am event 12 pm event 8pm event The 2nd thing I'd like to do is If a particular day and time has more than one event, I'd like the time to show once and then list the events. ex. Monday, January 23, 2012 7am event event event 8am event 6pm event Here's the code: since the listing is for a range of 7 days I've only included 3 days worth of code. I've include the mysql_statements for pulling the information as well as the php code to show how it's being rendered and placed. HTML excluded. <?php //TODAY $today2 = date('l, F j, Y');//php to format date $today_rehearsals = mysql_query(" SELECT Id, date, time, title, campus, room, ministry, category FROM events LEFT JOIN dates on events.dateId=dates.dateId LEFT JOIN time ON events.timeId=time.timeId LEFT JOIN campus ON events.campusId=campus.campusId LEFT JOIN rooms ON events.roomId=rooms.roomIdLEFT JOIN ministries on events.ministryId=ministries.ministryId LEFT JOIN category on events.categoryId=category.categoryIdWHERE ministry='music' AND date='$today' OR category='music' AND date='$today' ORDER By date, time") //////DAY ONE $day1= mktime(0,0,0, date("m"), date("d")+1, date("Y")); //php to get current date and add 1 day $day_1 = date("l, F j, Y", $day1); //php to format date $day1_rehearsal = mysql_query("SELECT Id, date, time, title, campus, room, ministry, category FROM events LEFT JOIN dates ON events.dateId=dates.dateIdLEFT JOIN time ON events.timeId=time.timeId LEFT JOIN campus ON events.campusId=campus.campusId LEFT JOIN rooms ON events.roomId=rooms.roomId LEFT JOIN ministries ON events.ministryId=ministries.ministryId LEFT JOIN category ON events.categoryId=category.categoryId WHERE ministry='music' AND date=CURDATE()+1 OR category='music' AND date=CURDATE()+1 ORDER BY time" ) or die(mysql_error()); /////DAY 2 $day2= mktime(0,0,0, date("m"), date("d")+2, date("Y")); //php to get current date and add 2 days $day_2 = date("l, F j, Y", $day2); //php to format date $day2_rehearsal = mysql_query("SELECT Id, date, time, title, campus, room, ministry, category FROM events LEFT JOIN dates ON events.dateId=dates.dateIdLEFT JOIN time ON events.timeId=time.timeId LEFT JOIN campus ON events.campusId=campus.campusId LEFT JOIN rooms ON events.roomId=rooms.roomId LEFT JOIN ministries ON events.ministryId=ministries.ministryId LEFT JOIN category ON events.categoryId=category.categoryId WHERE ministry='music' AND date=CURDATE()+2 OR category='music' AND date=CURDATE()+2 ORDER By time")or die(mysql_error()); ////TODAY echo "$today2"; //renders Today's date while($row = mysql_fetch_array( $today_rehearsals )){ $time=date_create($row['time']);//grab time for db $for_time=date_format($time, 'g:i a');//format time for viewing on page echo "$for_time"; echo $row['title']; echo $row['campus'] . " " . $row['room']; } ////day1 echo "$day_1"; //renders day_1's (tomorrow's) date while($row = mysql_fetch_array( $day1_rehearsal )){ $time=date_create($row['time']);//grab time for db $for_time=date_format($time, 'g:i a');//format time for viewing on page echo "$for_time"; echo $row['title']; echo $row['campus'] . " " . $row['room']; } ////day2 echo "$day_2"; //renders day_2's (day after tommorrow) date while($row = mysql_fetch_array( $day2_rehearsal )){ $time=date_create($row['time']);//grab time for db $for_time=date_format($time, 'g:i a');//format time for viewing on page echo "$for_time"; echo $row['title']; echo $row['campus'] . " " . $row['room']; } MOD EDIT: code tags fixed . . .