slaterino Posted June 7, 2011 Share Posted June 7, 2011 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) Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/ Share on other sites More sharing options...
jcbones Posted June 7, 2011 Share Posted June 7, 2011 Is the startDate, and endDate actual datetime, date, or timestamp columns in your database? Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226745 Share on other sites More sharing options...
slaterino Posted June 7, 2011 Author Share Posted June 7, 2011 as datetime - that's the way to do it right? Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226746 Share on other sites More sharing options...
jcbones Posted June 7, 2011 Share Posted June 7, 2011 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! Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226749 Share on other sites More sharing options...
Pikachu2000 Posted June 7, 2011 Share Posted June 7, 2011 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 // Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226752 Share on other sites More sharing options...
slaterino Posted June 7, 2011 Author Share Posted June 7, 2011 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! Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226755 Share on other sites More sharing options...
jcbones Posted June 7, 2011 Share Posted June 7, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226757 Share on other sites More sharing options...
jcbones Posted June 7, 2011 Share Posted June 7, 2011 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!'; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226759 Share on other sites More sharing options...
slaterino Posted June 7, 2011 Author Share Posted June 7, 2011 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! Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226761 Share on other sites More sharing options...
Pikachu2000 Posted June 7, 2011 Share Posted June 7, 2011 Sometimes I think too much for me! I need to go get a burger and a milkshake. Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226762 Share on other sites More sharing options...
jcbones Posted June 7, 2011 Share Posted June 7, 2011 Let me load it up on my dev. I'll get back in a minute or two. Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226764 Share on other sites More sharing options...
jcbones Posted June 8, 2011 Share Posted June 8, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1226775 Share on other sites More sharing options...
slaterino Posted June 8, 2011 Author Share Posted June 8, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/238731-creating-list-of-events-filtered-to-next-seven-days-and-grouped-by-day/#findComment-1227048 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.