print recursive list in php

Question

I am trying to print a recursive list where every date has a sublist of events ordered by date.

For example on the database I have:

+------+----------+--------+
| date | event_id | post_id|
+------+----------+--------+
|date1 | event1   | post1  |
|date1 | event2   | post2  |
|date1 | event3   | post3  |
|date2 | event4   | post4  |
|date2 | event5   | post5  |
+------+----------+--------+

I need to print

<ul>
    <li>date1</li>
    <ul>
        <li>event1, post1</li>
        <li>event2, post2</li>
        <li>event3, post3</li>
    </ul>
    <li>Date 2</li>
    <ul>
        <li>event4, post4</li>
        <li>event5, post5</li>
    </ul>
</ul>

how can I print in php the

select date, event_id, post_id from tablename

query in php to have this?

Answer 1

After a while with php and mysql, I do the least I can in mysql, even if it's often more elegant. Performance of mysql is crap. And even if what you query is fast, it can still slow down another query elsewhere done by another user. So here is a response with the least load on the DB. One query, no grouping. just select your 3 fields, and you can apply this.

$lastDate = null;  // lastDate will be updated at each row, so we can check if it has changed the next one.

echo '<ul>';
foreach ($queryResult as $row)
{
    if ( $lastDate != $row['date'] )
    {
        if ( $lastDate) { echo '</ul>'; }  // if lastdate "exists", it's not the first, so let't close the "date" list...

        echo '<li>', $row['date'], '</li><ul>'; // ... and start a new one
    }
    echo '<li>', $row['event_id'], ', ',$row['post_id'], '</li>';
    $lastDate = $row['date'];  

}
echo '</ul>'; // close the very last date list
echo '</ul>'; // close the the full list

Answer 2

It looks like you need MySQL GROUP_CONCAT().

SELECT   date,
         GROUP_CONCAT(event_id) AS 'event_ids',
         GROUP_CONCAT(post_id) AS 'post_ids'
FROM     tablename
GROUP BY date

This will return results with three fields. date will be the date field, event_ids and post_ids will be comma separated lists of the events and posts that fall under that date.