Attempting to group events by date in PHP

I'm grabbing a list of events from a database and am trying to associate like dates with one header. As of the moment I'm using <dl> <dt> <dd>, etc as per request. This may change but I don't think it impacts the overall impact of my question.

Basically I am trying to grab the 5 newest events via a query:

global $wpdb;

$sql = "SELECT e.*, c.*, ese.start_time, ese.end_time
        FROM wp_events_detail e
        JOIN wp_events_category_rel r ON r.event_id = e.id
        JOIN wp_events_category_detail c ON c.id = r.cat_id
        LEFT JOIN wp_events_start_end ese ON ese.event_id= e.id 
        WHERE e.is_active =  'Y'
        AND c.category_identifier = 'main'
        ORDER BY e.start_date ASC
        LIMIT 0, 5";
$results = $wpdb->get_results($sql);

I then loop them through a foreach():

if(count($results) > 0) {

    echo '<div class="calendar-list">';

    foreach($results as $event) {

        $event_id = $event->id;
        $event_name = $event->event_name;
        $start_date = $event->start_date;
        $start_time = $event->start_time;

        $start_time = strtotime($start_time);
        $start_time = date('g:ia', $start_time);

        $the_time =  strtotime($start_date);

        $the_day = date('d', $the_time);
        $the_month = date('M', $the_time);

        echo '<dl class="calendar-day first-child">';
        echo '<dt>';
        echo '<p class="the-month">' . $the_month . '</p>';
        echo '<p class="the-day">' . $the_day . '</p>';
        echo '</dt>';
        echo '<dd>';
        echo '<h4><a href="' . $event_id . '">' . $event_name . '</a></h4>';
        echo '<h5><span class="time">' . $start_time . '</span></h5>';
        echo '</dd>';
        echo '</dl>';

    }

    echo '</div>';
}

My main problem is I need to figure out a way to associate dates with one another. For example, June 27th. If there are two dates I would like it to look like:

<div class="calendar-list">
    <dl class="calendar">
        <dt>
            <p class="the-month">JUN</p>
            <p class="the-day">27</p>
        </dt>
        <dd>
            <h4><a href="#">Title</a></h4>
                        <h5><span class="time">Time</span></h5>
        </dd>
        <dd>
            <h4><a href="#">Title</a></h4>
                        <h5><span class="time">Time</span></h5>
        </dd>
    </dl>
    <dl class="calendar">
        <dt>
            <p class="the-month">JUN</p>
            <p class="the-day">28</p>
        </dt>
        <dd>
            <h4><a href="#">Title</a></h4>
                        <h5><span class="time">Time</span></h5>
        </dd>
        </dl>
</div>

I am quite unsure how to achieve this. I have been playing around with setting and unsetting some variables but have been unable to achieve the desirable outcome.

If someone could point me in the right direction of what to do, it would be most appreciated.

Thank you!

Tre

Answers


Basically you have to remember $the_time from every row at the end of your loop, and compare it to the current $the_time to decide if you have to echo a new <dt>. The fact that each day has its own <dl> complicates things a bit, though. Here is an ugly solution:

// Init empty previous time
$previous_time = null;
// Open first definition list
echo '<dl class="calendar-day first-child">';
foreach($results as $event) {

    $event_id = $event->id;
    $event_name = $event->event_name;
    $start_date = $event->start_date;
    $start_time = strtotime($event->start_time);
    $the_time = $start_time;
    $the_day = date('d', $start_time);
    $the_month = date('M', $start_time);
    $start_time = date('g:ia', $start_time);

    // Date changed
    if($the_time != previous_time) {
        // Unless first iteration, close previous list
        if(!empty($previous_time)) {
            echo '</dl>';
        }
        // Open new list
        echo '<dl class="calendar-day first-child">'
        // Show definition term for the group of events
        echo '<dt>';
        echo '<p class="the-month">' . $the_month . '</p>';
        echo '<p class="the-day">' . $the_day . '</p>';
        echo '</dt>';
    }

    // Display current event
    echo '<dd>';
    echo '<h4><a href="' . $event_id . '">' . $event_name . '</a></h4>';
    echo '<h5><span class="time">' . $start_time . '</span></h5>';
    echo '</dd>';

    // Remember date from previous row
    previous_time = $the_time;
}
// Close last definition list
echo '</dl>';

<?php
//***************************************
/// Read the readme.txt file before using ///////
// This is downloaded from www.plus2net.com //
/// You can distribute this code with the link to www.plus2net.com ///
//  Please don't  remove the link to www.plus2net.com ///
// This is for your learning only not for commercial use. ///////
//The author is not responsible for any type of loss or problem or damage on using   this script.//
/// You can use it at your own risk. /////
//*****************************************

$dbservertype='mysql';
$servername='ur host';
// username and password to log onto db server
$dbusername='ur user';
$dbpassword='ur pwd';
// name of database
$dbname='ur db';

////////////////////////////////////////
////// DONOT EDIT BELOW  /////////
///////////////////////////////////////
connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}
//////// End of connecting to database ////////

// Init empty previous time
 $previous_time = null;

 // Open first definition list
     echo '<dl class="calendar-day first-child">';
    $q1="SELECT * FROM msg WHERE sendto = '1' || sender = '1' ORDER BY time desc";
 $n1=mysql_query($q1);
    while($nt1=mysql_fetch_array($n1)){

 $start_time = strtotime($nt1['time']); //change to string
    $the_time = date('j m', $start_time); //select wat u need i.e day and month

     //$the_time = $nt1['time']);  //for troublshooting db

   // Date changed
if($the_time != $previous_time) {
    // Unless first iteration, close previous list
    if(!empty($previous_time)) {
        echo '</dl>';
    }
    // Open new list
    echo '<dl class="calendar-day first-child">';
    // Show definition term for the group of events
   echo '<h4>'.$nt1['time'].'</h4>';
 }

 // Display current event
echo '<dd>';
echo $nt1['message'] .' '. $nt1['time'];
echo '</dd>';

// Remember date from previous row
$previous_time = $the_time;
 }
 // Close last definition list
 echo '</dl>';

?>


 </body>

       </html>

brethren with this edited piece of code you can group anything by time


Need Your Help

Auto-layout: What creates constraints named UIView-Encapsulated-Layout-Width & Height?

ios uitableview uicollectionview autolayout

My layout constraints are fine in Interface Builder but an exception occurs at runtime thanks to some part of the framework applying fixed height and width constraints that I really don't want. Why...