Datetime : select by day (& not for each datetime) ?

Hello

I have a table with 2 fields : word | date

I have this code :

$sql = "select date, count(distinct date) mostpopular from table WHERE word='$word' group by date order by date DESC";
$req = mysql_query($sql) or die(mysql_error());
while ($db_data = mysql_fetch_assoc($req)) {
	$date = $db_data['date'];
	$mostpopular = $db_data['mostpopular'];
	echo "$date : $mostpopular<br/>\n";
}

Code (markup):

I want it show the number of hits for word $word for each day.

Actually it show something like this :

2007-09-27 03:25:17 : 1
2007-09-27 02:46:47 : 1
2007-09-27 02:44:10 : 1
2007-09-27 02:43:44 : 1
2007-09-27 02:29:41 : 1
2007-09-27 01:33:40 : 1

Code (markup):

So, I'd like that my SQL select for each day and not for each date-time. I also want to keep my date field with a datetime setting.

How could I do this please ?

 

I think you should be able to use the DATE() function to get only the date part:

$sql = "select DATE(date), count(distinct date) mostpopular from table WHERE word='$word' group by DATE(date) order by DATE(date) DESC";

Code (markup):

You'll find more useful date and time functions here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Edit:
What if a word gets two (or more) hits at the "same" time? It seems you are counting distinct datetimes, so you would only count one of the hits.
(I am not sure if the datetime also contains a micro- or milliseconds part, even though it is not displayed. In that case the two hits would need to be in the same micro- or millisecond, which is much more unlikely, but still possible.)

 

That work .

Thanks a lot !

ps : I don't understand your edit :-?