Creating list of events (filtered to next seven days and grouped by day)

[slaterino]

Okay,

I just got some amazing help on a different thread and wanted to start a new thread to ask this question, as it's definitely a different query altogether!

 

I have a list of events, which are either single day events or multiple day events. If they are a single day they simply have a startDate field. If they are a multiple day event they have a startDate and an endDate.

 

What I want is just to show the next seven days events, but split the events into each day. So, there would be a header for Monday and then a list of Monday's events, and so on. If the event lasted all week it would show under each day.

 

Okay, now I have this short piece of code that works out the next seven days and their headers. How would I begin to incoporate the events into this loop? Any help would be massively appreciated!

 

<?php
$date = time();
for($i=0; $i<7; $i++) {
   echo date('D d', strtotime("+$i days", $date)) . "<br />";
}
?>

 

There are a few different fields in the events table but the important ones are:

 

eventName

startDate (formatted like this: 2011-06-06 21:00:00)

endDate (as above)

[jcbones]

Is the startDate, and endDate actual datetime, date, or timestamp columns in your database?

[slaterino]

as datetime - that's the way to do it right?

[jcbones]

as datetime - that's the way to do it right?

 

That is correct.  Try this:

 

<?php

$sql = "SELECT eventName, DATE_FORMAT(startDate,'%m/%d/%Y %H:%i:%s') AS eventStart FROM events WHERE DATE(startDate) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) ORDER BY startDate";
$result = mysql_query($sql) or trigger_error($sql . ' has falted. <br />' . mysql_error());

if(mysql_num_rows($result) > 0) {
while($r = mysql_fetch_assoc($result)) {
	echo $r['eventStart'] . '=> ' . $r['eventName'] . "<br />\n";
}
}
else {
echo 'No rows to show!';
}

?>

 

Post back with the results please!

[Pikachu2000]

jcbones, if I'm not mistaken, that will pull an event that starts or ends within the next week, but in the case that the event started and ended outside of the next week, it would miss it. For example, an event that started 2 days ago, and ends 10 days in the future wouldn't show up in the results.

 

This should build a query that will pull the events that have any active day that occurs on the current day through the next 6 days.

 

$clause = array();
$query ="SELECT startDate, endDate FROM teams WHERE ";
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(startDate) AND DATE(endDate)";
}

$query .= implode(' OR ', $clause);
echo $query; // for demo purposes //

[slaterino]

Right, thanks for the help!

 

And jcbones, it is true that I am missing some date. These are my results:

 

06/08/2011 21:00:00=> Event 2

06/10/2011 21:00:00=> Event 1

06/13/2011 21:00:00=> Event 3

 

Which is coming from this data. It is missing event 4, which starts before today and finishes in just over 7 days time.

 

Event 1 2011-06-10 21:00:00 NULL 1

Event 2 2011-06-08 21:00:00 2011-06-09 21:00:00 2

Event 3 2011-06-13 21:00:00 NULL 3

Event 4 2011-06-06 21:00:00 2011-06-15 21:00:00 4

 

However, Pikachu2000, I don't know if I completely understand your code. I just tried it but it just printed the SELECT query out. How would I used the code practically?

 

Thanks again for your help on this!

[jcbones]

jcbones, if I'm not mistaken, that will pull an event that starts or ends within the next week, but in the case that the event started and ended outside of the next week, it would miss it. For example, an event that started 2 days ago, and ends 10 days in the future wouldn't show up in the results.

 

This should build a query that will pull the events that have any active day that occurs on the current day through the next 6 days.

 

$clause = array();
$query ="SELECT startDate, endDate FROM teams WHERE ";
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(startDate) AND DATE(endDate)";
}

$query .= implode(' OR ', $clause);
echo $query; // for demo purposes //

 

Welp, I just plugged the startDate in, and didn't even put anything doing with the endDate.  So, it would only pull dates that Started in the next week.  Your query is probably more what the OP is wanting, as you tend to think much to much for me...      But, in reality, I understand what your query does, and yes, it would fit this problem better.

[jcbones]

Pik's code added to mine:

<?php

$sql = "SELECT eventName, DATE_FORMAT(startDate,'%m/%d/%Y %H:%i:%s') AS eventStart, DATE_FORMAT(endDate,'%m/%d/%Y %H:%i:%s') AS eventEnd FROM events WHERE";
$clause = array();
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(startDate) AND DATE(endDate)";
}

$sql .= implode(' OR ', $clause);
$sql .= ' ORDER BY startDate';
$result = mysql_query($sql) or trigger_error($sql . ' has falted. <br />' . mysql_error());

if(mysql_num_rows($result) > 0) {
while($r = mysql_fetch_assoc($result)) {
	echo $r['eventStart'] . ' until ' . $r['eventEnd'] . ' => ' . $r['eventName'] . "<br />\n";
}
}
else {
echo 'No rows to show!';
}

?>

[slaterino]

Okay, so the new code is outputting this:

 

06/06/2011 21:00:00 until 06/15/2011 21:00:00 => Event 4

06/08/2011 21:00:00 until 06/09/2011 21:00:00 => Event 2

 

However, thinking about this, and using this as my test data,

 

eventName startDate endDate ID

Event 1    2011-06-10 21:00:00    NULL    1

Event 2    2011-06-08 21:00:00    2011-06-09 21:00:00    2

Event 3    2011-06-13 21:00:00    NULL    3

Event 4    2011-06-06 17:00:00    2011-06-15 17:00:00    4

 

Then this is what I would need to output:

 

06/08/2011 17:00:00=> Event 4

06/08/2011 21:00:00=> Event 2

06/09/2011 17:00:00=> Event 4

06/09/2011 21:00:00=> Event 2

06/10/2011 17:00:00=> Event 4

06/10/2011 21:00:00=> Event 1

06/11/2011 17:00:00=> Event 4

06/12/2011 17:00:00=> Event 4

06/13/2011 21:00:00=> Event 3

06/13/2011 17:00:00=> Event 4

06/14/2011 17:00:00=> Event 4

 

This is becoming clearer in my head what I need to achieve - it's just having the coding prowess to achieve it that's my problem!

[Pikachu2000]

Sometimes I think too much for me! I need to go get a burger and a milkshake.

[jcbones]

Let me load it up on my dev.  I'll get back in a minute or two.

[jcbones]

OK, here is where we are.  First I would like to say, there has to be a better way, but it is way to late for me to think clearly.  This works, but I'm sure someone could clean it up abit.

 

Secondly, you MUST have an end date in the database, or the query will not return THAT row.

<?php
include 'config.php';
$sql = "SELECT eventName,
		DATE_FORMAT(startDate,'%m/%d/%Y') AS eventStart, 
		DATE_FORMAT(endDate,'%m/%d/%Y') AS eventEnd, 
		DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
		DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek 
		FROM events WHERE ";
$clause = array();
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(startDate) AND DATE(endDate)";
}

$sql .= implode(' OR ', $clause);
$sql .= ' ORDER BY startDate';
$result = mysql_query($sql) or trigger_error($sql . ' has falted. <br />' . mysql_error()); //pull data from database.
$dates = array(); //define dates array.
for($i = 0; $i < 7; $i++) {
$dates[] = date('m/d/Y', strtotime("+$i days")); //fill dates array with every date from now until 7 days from now.
}
foreach($dates as $days) { //go through dates array.
$data[$days] = NULL;  //fill data array with keys for every date from now until 7 days.
}
if(mysql_num_rows($result) > 0) {
while($r = mysql_fetch_assoc($result)) { //while data exists in the database result resource.
	if(strtotime($r['eventStart']) < strtotime($r['today'])) { //if the event started before today.
		$r['eventStart'] = $r['today']; //make it's startdate today.
	}
	if(strtotime($r['eventEnd']) > strtotime($r['endWeek'])) { //if the event ends after this week is over.
		$r['eventEnd'] = $r['endWeek']; //make the end of the week, it's end.
	}
	//echo '<pre>' . print_r($r,true) . '</pre>'; //de-bugging.
	$started = false; //define a false variable.
	foreach($data as $key => $value) { //loop through our data array.
		if($key == $r['eventStart'] && $started == false) { //if the event starts at the present key, put it in the array.
			$data[$key][] = $r['eventName'];
			$started = ($key == $r['eventEnd']) ? false : true; //set started to true.
		}			
		elseif($key == $r['eventEnd']) { //if the eventEnd is the present key, put it in the array,
			$data[$key][] = $r['eventName'];
			$started = false; //set started to false;
		}
		elseif($started == true) { //if started is true, then put the event in the array.
			$data[$key][] = $r['eventName'];
		}
	}
}
foreach($data as $date => $v) { //loop through the data array.
	echo $date . '<br />---------<br />'; //echo the $date, followed by a line.
	foreach($v as $event) {
		echo $event . '<br />'; //each event is then echo'd to the page, followed by a break rule.
	}
	echo '<br />'; //after all of the events on this day, print another break rule, double spacing before the next date.
}
}
else {
echo 'No rows to show!';
}

?>

edit: fixed code, please re-run.

[slaterino]

jcbones, you're an absolute genius! Thanks so much for your help on this!

 

Thanks for the comments too! I actually think I understand a lot of what's going on!

 

Best,

Russ