Grouping results by date

How?

[Patrick Taylor]

Way out of my depth here, but here goes...

I have a MySQL database into which are fed events by my site visitors when they visit a page. In the database is recorded NOW() into its own field with each event.

I want to get the results in such a way as to group them by date as the dates go along. In other words I want a list of dates that increments by itself with each day that goes by (generating the new date) with a total for the events that occurred for each day. I have this query:

$query = "SELECT DATE_FORMAT(event_date, '%M %D %Y'), COUNT(*) AS dailies FROM userdata GROUP BY event_date

but I really have no idea how to present the output using PHP, nor even am I sure if my query is correct. All I've managed to do so far is to create a long list of dates which keep on repeating themselves per event, instead of the dates showing only once with the total number of events. In this instance the event is actually the addition of NOW() into the database (as this marks the event anyway, and is enough for my purpose).

I hope I've explained this clearly enough, and would appreciate some assistance.

[coopster]

Well, your query statement looks fine, except that you may want to add an ALIAS to that newly formatted date, just to make the returned variable name a bit easier to work with in PHP:

$query = "SELECT DATE_FORMAT(event_date, '%M %D %Y') AS event_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY event_date

And don't name it the same as the actual column name! If you do, your GROUP BY will return the results in alphabetic order as specified in your DATE_FORMAT! Notice how I called it

event_date_formatted

. Try it once as

event_date

and you'll see what I mean.

Note that if you are looking for incremental dates, and nobody visited on that day, you are going to have a gap in the sequence. For example, if nobody visited your site on Tuesday, your result set will show the gap:

+----------------------+---------+ 
¦ event_date_formatted ¦ dailies ¦ 
+----------------------+---------+ 
¦ July 4th 2004        ¦      45 ¦ 
¦ June 5th 2004        ¦     123 ¦ 
¦ June 7th 2004        ¦       2 ¦ 
+----------------------+---------+

Now, to get the data out of the result set, you need to loop through it, something like this:

$query = "SELECT DATE_FORMAT(chat_date, '%M %D %Y') AS event_date_formatted, COUNT(*) as dailies FROM clicks GROUP BY chat_date;"; 
$rows = mysql_query($query); 
while ($row = mysql_fetch_assoc($rows)) { 
print $row['event_date_formatted'] . ': ' . $row['dailies'] . '<br />'; 
}

[Patrick Taylor]

+----------------------+---------+
¦ event_date_formatted ¦ dailies ¦
+----------------------+---------+
¦ July 4th 2004 ¦ 45 ¦
¦ June 5th 2004 ¦ 123 ¦
¦ June 7th 2004 ¦ 2 ¦
+----------------------+---------+

Thanks. This is exactly how I want my results, but I'm still getting a long list of dates, like:

July 4th 2004: 1
July 4th 2004: 1
July 4th 2004: 1
etc etc

Here's the code I've used (the one you suggested, I think):

$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY info_date DESC LIMIT $offset,$page_size";

// Run the query.
$result = @mysql_query ($query);

// If it ran OK, display the records.
if ($result) {
$rows = mysql_query($query);
while ($row = mysql_fetch_assoc($rows)) {
print $row['info_date_formatted'] . ': ' . $row['dailies'] . '<br />';
}

// Free up the resources.
mysql_free_result ($result);

} else { // If it did not run OK.
echo 'Whoops... the query didn\'t run.<br />' . mysql_error() . '<br />';
}

If my query is now correct, I'm obviously not understanding the PHP output part, because the results for each date should be aggregated and not output in the form of a long list of dates with only one event attached to it (if that makes sense!)

Is there an obvious mistake I've made?

<added>
I've noticed that a few of the lines (very few, out of thousands) are like:

July 4th 2004: 5

... out of a huge list where the result is:

July 4th 2004: 1
</added>

[uncle_bob]

You appear to be grouping the data by the raw date column (info_date) which will include time info I expect, which is why you get one row for each entry. Try grouping the data by the formatted date (info_date_formatted) instead, this should give you just one row per day, as you want.

[Patrick Taylor]

You're SO right! Thanks.

Patrick

[Patrick Taylor]

Thanks again, both. I've now got this to work really well. I hadn't realised how powerful "GROUP BY" is, and it's so simple when you know how (which I didn't until now). Pure magic!

[coopster]

Oh, when you stated it was date field, I assumed you meant it was of column type date! It is of type timestamp? Was that the issue?

[Patrick Taylor]

Yes, the "info_date" field contains NOW() values for each event. I could present the date formatted as eg "July 4th 2004" but I didn't grasp how to group the results or to display the events aggregated for each date on a rolling basis. It works a treat now, thanks (plus I learnt something).

[Patrick Taylor]

It works a treat now

Now that it's July 10th, I can see I spoke too soon. In my results, July 10th is listed below the other July dates: 7th, 8th, 9th etc, instead of at the top of the list where it should be - presumably because the actual date number 10th starts with a "1".

Here's my query:

$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY info_date_formatted DESC LIMIT $offset,$page_size";

<added>I've corrected the ordering - for July, at least - with %d instead of %D. I'm not sure what will happen come a new month.</added>

[coopster]

You really didn't answer the question yet. You see, you can enter

NOW()

into both a

DATE

column type as well as a

TIMESTAMP

column type. For example, take the following:

CREATE TABLE times (datestp DATE, timestp TIMESTAMP); 
INSERT INTO times VALUES(NOW(), NOW()); 
SELECT * FROM times; 
// returns: 
+------------+---------------------+ 
¦ datestp    ¦ timestp             ¦ 
+------------+---------------------+ 
¦ 2004-07-10 ¦ 2004-07-10 09:08:18 ¦ 
+------------+---------------------+

Now can you see where your problem may be starting? I'm guessing that uncle_bob is correct in the assumption that your

info_date

column is indeed of type

TIMESTAMP

. The

DESCRIBE

command will reveal this information to you,

DESCRIBE userdata;

This would explain why you may be getting multiple rows for the same date -- if the column is of type

TIMESTAMP

, the time is more than likely going to be different for each date entered!

Now, knowing this, we really don't want to sort the column by the formatted date, as you have already realized, because July 10 ends up printing before July 1st, July 2nd, etc. So, we really just want to sort by the date part of the

info_date

column, right?

$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY DATE [dev.mysql.com](info_date) DESC LIMIT $offset,$page_size";

[Patrick Taylor]

My database field is DATETIME and the data is formatted like 2004-07-10 09:08:18 (has time info as well as date info), so I suppose that means it's a TIMESTAMP column type. I can therefore see that I have to extract the info_date data formatted as a date-only before I can group it the way I need: GROUPING the results from the date-only output. So having understood that part, I've substituted your newly-suggested query for my previous one, and I now get this response on the page:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /nfs/home/arthur8/public_html/priv/dailies.php on line 113

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /nfs/home/arthur8/public_html/priv/dailies.php on line 119

This refers to (in my code):

// Display dailies
echo '<table cellspacing="0" cellpadding="0" width="100%">';
// Line 113:
while ($row = mysql_fetch_assoc($result)) {
echo '<tr><td width="200" valign="top"><p class="text1a">' . $row['info_date_formatted'] . ':</p></td><td width="70" valign="top"><p class="text1a">' . $row['dailies'] . '</p></td><td width=" " valign="top" bgcolor="#EBEBEB"><p class="bar"><img src="bar.jpg" height="11" width="' . (($row['dailies'] / $total) * 100) . '%" /></td></tr>';
}
echo '</table>';

// Free up the resources.
// Line 119:
mysql_free_result ($result);

(I'm converting the results into a barchart)

I now appear to have a PHP-type error, which is, seemingly, purely as a result of the amended query, so I need to understand this before I go any further.

Thankyou for your patience - as I said at the start, I'm rather out of my depth here, though it's helpful and amongst other things it's made me read up on timestamps and PHP getdate, date, and mktime.

[coopster]

DATETIME

and

TIMESTAMP

are two different column types [dev.mysql.com] but are similar in that the

DATE

function works for either type.

It seems there is something wrong with the query though, that is why the error is occuring on line 113. I tested it successfully, so I'm wondering if there isn't something wrong with one of your variables? Try printing your query out to make sure the syntax is correct...

$query = "SELECT ..... "; 
exit($query);

[charlier]

Try your query that worked except for the sort order in msg 9 and just add ORDER BY UNIX_TIMESTAMP(info_date) DESC

[Patrick Taylor]

For the sake of completeness, here: I have three queries. Towards the top of my page I have:

// Count how many rows there are.
$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY DATE(info_date)";
$result = @mysql_query($query);
if ($row = mysql_fetch_assoc($result)) $row_count = $row[0]; // Line 55
else $row_count = 0;

This is part of a paging system and is supposed to mirror the actual rows of the main query. Then lower down I have:

// Get total hits
$total = mysql_result(mysql_query("SELECT COUNT(info_date) AS total FROM userdata"),0);

This is to count the total number of events because I use this in the calculation of the length of each bar image for the GROUPED data. The main query is:

// Get events grouped by date
$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY DATE(info_date) DESC LIMIT $offset,$page_size";

// exit($query);

// Run the query.
$result = mysql_query($query);

// Display dailies
echo '<table cellspacing="0" cellpadding="0" width="100%">';
while ($row = mysql_fetch_assoc($result)) { // Line 115
echo '<tr><td width="200" valign="top"><p class="text1a">' . $row['info_date_formatted'] . ':</p></td><td width="70" valign="top"><p class="text1a">' . $row['dailies'] . '</p></td><td width=" " valign="top" bgcolor="#EBEBEB"><p class="bar"><img src="bar.jpg" height="11" width="' . (($row['dailies'] / $total) * 100) . '%" /></td></tr>';
}
echo '</table>';

// Free up the resources.
mysql_free_result ($result); // Line 121

... and when I put in "exit($query);" I get the Line 55 Warning plus:

SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY DATE(info_date) DESC LIMIT 0,200

The errors now read as:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /nfs/home/arthur8/public_html/priv/dailies3.php on line 55

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /nfs/home/arthur8/public_html/priv/dailies3.php on line 115

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /nfs/home/arthur8/public_html/priv/dailies3.php on line 121

I haven't yet tested the query except for the sort order in msg 9 and just added "ORDER BY UNIX_TIMESTAMP(info_date) DESC" but I will.

Sorry 'bout this!

[Patrick Taylor]

Yes, this seems to be okay:

SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, COUNT(*) AS dailies FROM userdata GROUP BY info_date_formatted ORDER BY UNIX_TIMESTAMP(info_date) DESC LIMIT $offset,$page_size

Thanks for that, though I don't understand why that would produce the correct order, because I'm putting the GROUPS of info_date_formatted into ORDER and not the actual individual info_date data, which by the time it's grouped surely doesn't have a TIMESTAMP attached to it any more.

[coopster]

The confusion is my fault. I should have offered standard SQL from the very beginning. The best explanation I can give you is this...

MySQL extends standard SQL by allowing you to use columns or calculations in a

SELECT

list that don't appear in a

GROUP BY

clause. However, we are warned not to use this feature unless the columns you omit from the

GROUP BY

clause are not unique in the group because you will get unpredictable results, as we are no doubt seeing here with the duplicated date information. Why did it work in my tests? Well, I'm sure that my test data set is quite different from yours.

While I'm at it, we may as well identify the two other violations of standard SQL here. First, although MySQL >= version 3.23.34 allows us to sort output rows by specifying

ASC

and

DESC

after columns named in the

GROUP BY

clause, it is a MySQL extension to the standard.

Second, you can't use expressions in a

GROUP BY

or

ORDER BY

clause. MySQL versions >= 3.23 allow it, however it is not good practice. There is a workaround in that you can use an alias for the expression.

So, how should we write the query statement?

$query = "SELECT DATE_FORMAT(info_date, '%M %D %Y, %W') AS info_date_formatted, DATE(info_date) AS sortdate, COUNT(*) AS dailies FROM userdata GROUP BY info_date_formatted, sortdate ORDER BY sortdate DESC LIMIT $offset,$page_size";

[Patrick Taylor]

Thankyou for your analysis, and well... I'm going to have to study it carefully and try to understand all you've explained in this thread. I have the page working now, but I appreciate there may be some violations of good practice, which ideally I will need to resolve.

The first thing I will look at is why I'm still getting 3 "supplied argument is not a valid MySQL result resource" errors! I will probably build the page afresh, actually.

Many thanks again for all your assistance.

Patrick

[coopster]

I shouldn't have used the term "violation" as it's a bit harsh. "Extension" is a better word. MySQL, as any other decent database, tries to adhere to SQL standards. When these companies realize they are onto something that extends the current standards, they may add it to their feature set. This is a good thing. Without it we would never improve. Many of the MySQL extensions are there for performance reasons. This is not uncommon. You will see this in most databases. It is quite all right to use these extensions, you just have to realize that they are indeed extensions to the standard and you won't be able to port your code to any other database without adjusting the non-standard statements.

If you are confused or want further explanation on any area, let us know. BTW, which version of MySQL are you running?

[Patrick Taylor]

It's 4.0.18-standard.

I don't believe in coming to forums like this simply for a quick fix. For me, this thread has been a very helpful learning exercise, and as I said, I will try to aborb the information.

Thanks again for your patience and knowledge.

Patrick

[coopster]

I don't believe in coming to forums like this simply for a quick fix.

I respect you for that, and I certainly hope you weren't thinking that I was implying as such! I realized your sincerity to understand and that is why I took the time to explain/share what I do know on the subject. You're quite welcome, my friend. Have a great day.