PHP MySQL - Top 50 Rows Between...

[DigiplayStudios]

Hi There,

I currently have a PHP/MySQL query script which displays the top 50 MySQL rows in the table, however, it displays the top 50 of all time. I would love a way for it to display the top 50 rows in the last 7 days. The table looks a bit like this,


-------------------------------------
|id | tag |date |
-------------------------------------
|1 | search 1 |05-27-2010
|2 | search 2 |05-27-2010
|3 | search 3 |05-28-2010
|4 | search 4 |05-29-2010
-------------------------------------

My current PHP/MySQL script looks like this (the script that grabs the top 50 of all time):


$result = mysql_query("SELECT tag, COUNT(*) FROM tags
GROUP BY tag
ORDER BY 2 DESC LIMIT 50");

[DigiplayStudios]

Any help guys? :)

[djr33]

You are storing the date in a "human readable" format. That's not good for data organization: use a timestamp (http://en.wikipedia.org/wiki/Unix_time). The easiest way is to get it using time() (http://php.net/manual/en/function.time.php). Then you can format that later (when displaying, NOT in the database) using date() (http://us.php.net/manual/en/function.date.php).

Once you do that, then all you need to do is add "WHERE time>$startofweektime AND time<$endofweektime"

Timestamps are hard to get used to, but they're much easier to work with for mathematical operations.


Alternatively you can keep the current format (I strongly recommend that you change it!), and search for "WHERE time = $day1 OR time = $day2 OR ...... time = $day7".


Aside from adding the WHERE component, your query remains the same.
The order is:
OPERATION [select] / info [tables etc] / WHERE / [order by] / LIMIT

So:
"SELECT tag, COUNT(*) FROM tags WHERE ........ GROUP BY tag ORDER BY 2 DESC LIMIT 50"