[RESOLVED] Grouping By Time of Day

cap97

I can't seem to wrap my head around this. I have the following table:

dates_id
dates_events_id
dates_time
dates_notes

I want to display my dates/times like this on my site:

Aug 29, 30, Sept 5, 6 at 8:00 pm
Aug 29, 31, Sept 4, 8 at 2:00 pm

where each line is grouped by time. I have the following query:

SELECT DATE_FORMAT(dates_time,'%b') AS 'month', DATE_FORMAT(dates_time,'%e') AS 'day', DATE_FORMAT(dates_time,'%l:%i %p') AS 'time' FROM dates WHERE dates.dates_events_id = 11 ORDER BY time DESC, month, day

And uh...yeah now I'm stuck. I've tried various variables to hide repeating months and times, but it just isn't working how I want the above example to look. Any thoughts?

sneakyimp

try putting a GROUP BY clause in...maybe something like this?

GROUP BY DATE_FORMAT(dates_time, '&#37;b%e%l')

No idea if that will work. I would recommend experimenting with GROUP BY. However, be warned that GROUP BY can produce some results where the selected fields that you see returned aren't the ones that belong to all the grouped results.

cap97

Grouping doesn't really make a difference in terms of the query results. I think it will have to be within the PHP code that gets me what I want, but I just can't wrap my head around how to do it. It's probably the simplest thing too!

UPDATE: Been working on it a bit more and have got closer, but still not quite right. Firstly, I discovered that I was sorting my query wrong, so I changed it to the following:

$dsql = "SELECT DATE_FORMAT(dates_time,'%b') AS 'month', 
              DATE_FORMAT(dates_time,'%e') AS 'day', 
              DATE_FORMAT(dates_time,'%l:%i %p') AS 'time' 
              FROM dates 
              WHERE dates.dates_events_id = 11 
              ORDER BY DATE_FORMAT(dates_time, '%H') DESC, 
              DATE_FORMAT(dates_time,'%m'), 
              DATE_FORMAT(dates_time,'%d')

Then I did the following php, which got me mostly what I wanted, though there's possibly an easier way to do this:

$dresult = dbquery($dsql);
$last_time = "";
$last_month = "";
$cur = 1;
while ($ddata = dbarray($dresult)) {
                        if ($ddata['time'] != $last_time) {
                                if ($cur != 1) {
                                        echo "<br />\n";
                                }
                                echo $ddata['time'] . ": ";
                                $last_time = $ddata['time'];
                                $cur3 = 1;
                        }
                        if ($ddata['month'] != $last_month) {
                                if ($cur3 != 1) {
                                        echo ", ";
                                }
                                echo $ddata['month'] . " ";
                                $last_month = $ddata['month'];
                                $cur2 = 1;
                        }
                        if ($cur2 != 1) {
                                echo ", ";
                        }
                        echo $ddata['day'];
                        $cur++;
                        $cur2++;
                        $cur3++;
                }

Which produced this:

8:00 PM: Aug 29, 30, Sep 5, 12, 13, 19, 20
2:00 PM: Aug 31, Sep 7, 14

I'd still rather have the time at the end of the line, instead of the beginning, but I am at a loss and this will do for now. But any suggestions would be welcome!

bradgrafelman

Is the "Aug 29, 30, Sep 5, 12, ..." behavior essential? In other words, showing the month name only for the first date in the list. If not, you could simplify the code, otherwise you might do something like:

$query = "SELECT DATE_FORMAT(dates_time, '&#37;l:%i %p') AS time,
    GROUP_CONCAT(DATE_FORMAT(dates_time, '%b %e') ORDER BY dates_time ASC) AS date_list
FROM dates
GROUP BY time
ORDER BY dates_time ASC";
$exec = mysql_query($query);

while($row = mysql_fetch_assoc($exec)) {
	$dates = explode(',', $row['date_list']);
	$list = '';

for($i = 0, $prev_month = ''; isset($dates[$i]); $i++) {
	list($month, $day) = explode(' ', $dates[$i]);

	if($month != $prev_month) {
		$list .= "$month $day, ";
		$prev_month = $month;
	} else
		$list .= "$day, ";
}

echo rtrim($list, ', ') . ' at ' . $row['time'] . "<br>\n";
}

cap97

That's perfect! I have no idea why that works, and don't understand a few of the things you threw in there, but it works! Thanks very much! Now I need to do some reading!

bradgrafelman

Feel free to point out sections and ask why/how it works.

cap97

It's mostly the "GROUP_CONCAT" bit that is stumping me. Never heard of this and after reading about it at mysql, I'm still not exactly sure what it's doing. 😕

bradgrafelman

Basically, when you add GROUP BY to your query, MySQL groups all the different rows together based on some criteria. So if 10 rows matched the "10:00 AM" time, they would all be grouped together into one virtual row. If you simply tried to SELECT the grouped column, you'd only get the top most result. GROUP_CONCAT() is a lot like PHP's [man]implode/man in that it takes all of the rows in a group and combines them into one string.

cap97

I think I get it, thanks! 😃

sneakyimp

Brad, that is RAD. I never knew about group_concat.

Law_Ninja

This looks like what I'm looking for. Since this one is resolved and such, I've posted my problem here:

http://www.phpbuilder.com/board/showthread.php?p=10879481