Most Searches in a Day

[eluser]EEssam[/eluser]
Hello,

Here is my MySQL table structure:

Code:

CREATE TABLE `search` (
`searchid` int(11) NOT NULL default '0',
`terms` text character set latin1 NOT NULL,
`timestamp` int(10) default NULL,
PRIMARY KEY (`searchid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The "timestamp" field is populated using PHP time() function when any search is performed.

Can you please tell me how can I show the number of "Most Searches in a Day"?

Thanks.

[eluser]richthegeek[/eluser]
I think this might be too complicated to do it with pure SQL, but I'm sure some SQL guru will disagree with me here...

Read the db into a PHP array.

Create a second array called "searchDays" or w/e

For each row, could divide the timestamp by 86400 and round it to give you a "number of days since unix epoch" instead of number of seconds.

Increment searchDays[ timestamp ] by 1, and you will end up with an array where the key is eqaul to the number of days, and the value equivalent to the number of searches on that day.

ksort() the array to make the most active day appear the top and then use current() to grab the first array element, and then use key() to get the timestamp.

Finally, multiply the timestamp by 86400 to get back to a UNIX timestamp and use date() to format as you wish.

[eluser]EEssam[/eluser]
A good guy just told me how to do it in SQL:

Code:

SELECT DATE(FROM_UNIXTIME(`timestamp`)) AS the_day, COUNT(*) AS the_count FROM search GROUP BY the_day ORDER BY the_count DESC LIMIT 1

[eluser]richthegeek[/eluser]
Sample code

Code:

<?php

$query = mysql_query( "SELECT timestamp FROM search" );
$searchDays = array();
while( $row = mysql_fetch_assoc( $query ) )
{
    $day = round( $row[ 'timestamp' ] / 86400 );
    $searchDays[ $day ]++;
}
asort( $searchDays );
$day = key( current( $searchDays ) ) * 86400;
$date = date( "d m y", $day );

print $date." with ".current( $searchDays )." searches";

?>

[eluser]EEssam[/eluser]
Thanks Rich but that simple, single line is working like a charm, which much more elegant for sure.

[eluser]richthegeek[/eluser]
yep no doubt that the SQL is more efficient!

[eluser]Developer13[/eluser]
[quote author="richthegeek" date="1222308113"]I think this might be too complicated to do it with pure SQL, but I'm sure some SQL guru will disagree with me here...[/quote]

Kind of OT, so sue me... :-)

Okay... at first all I read on your reply was the "I think this might be too complicated to do it with pure SQL..." and didn't see the "but I'm sure some SQL guru will disagree with me here" part. Not that I'm a SQL guru by any means, but so many applications truly under-utilize the potential of the internal database processing power. So many things can be achieved in much simpler ways by writing creative queries, using stored procedures, etc etc instead of writing a mass of code that will most likely take longer to execute than MySQL (or others) could do it natively.

With each new application I write, I try to find a couple of new things I can use the database server for to offload my code. So much of it can simply be done with the way queries are structured - SQL is a very powerful language.