If Statement inside While statements...I think

[sonphish]

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 . . .

[RussellReal]

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.

 

GROUP BY date, time

 

First thing I'd like to do is skip or not show the days that do not have an event.

Ex.

 

You could do that with mysql, but I have no idea about your db structure, so my best advice would be to skip that with php..

 

 

[grissom]

If your 7 days are consecutive, you could also put them in a loop.

[sonphish]

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.

[RussellReal]

do you have an events table?

 

do you have a datetimes table..

 

mysql is a relational database.. You're gonna wanna have tables like this:

 

Events:

idevent_text

1Baseball Game

2Science Convention

 

Datetimes:

iddate (YYYY-MM-DD)hour (HH)events_id (foreign key)

12012-01-20131

22012-01-20141

32012-01-20152

 

 

SELECT * FROM datetimes JOIN events WHERE events.id = events_id ORDER BY datetimes.date, datetimes.hour

 

sorry, I said group by instead of order by, but, group by could work also

[sonphish]

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.

[PFMaBiSmAd]

Your goal is to get the query to return the data you want in the order that you want it. To produce your output, where you have headings/subheadings for the date and times, you would remember the 'last_heading' and 'last_subheading' (initialized to some value that will never appear as data, such as a null value.) Then as you iterate over the result set, you would detect when the heading and subheading changes, output the new heading/subheading, and save the new heading/subheading values for the next comparison. See the following logic for an example of how to do this - http://www.phpfreaks.com/forums/index.php?topic=349740.msg1650897#msg1650897

 

As to your separate DATES and TIME tables. Unless you are specifically doing this exercise to learn joins, I would not do that. Dates and times are final/literal data values. You should store the DATETIME of each event in a DATETIME column in the events table.

 

Edit: You will probably want to split and format the DATETIME into separate date and time values, with the final format you have shown, directly in the query so that the query will return separate values that can be used in the 'last_heading' and 'last_subheading' logic.

[sonphish]

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

[sonphish]

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

[PFMaBiSmAd]

<?php
if($last_heading != $new_heading){

....

}
// the sub-heading (time) logic would go here, using an if($last_subheading != $new_subheading){} similar to that for the heading logic

// the code to output just the event title would go here

[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.