Output top 10 entries from a mysql table

pablodelapena

Hello!

I have a table containing a list of ID's belonging to images. Say I have 10 images and everytime I click on an image it creates an entry into the table imagestats.

What I want to do is firstly count how many unique image clicks I have and then count how many I have of each and order it from the most to the least... giving a top 10 (if I set the limit to 10).

Can mysql do this in one command? Or will I have to use while loops etc and count it manually - using alot of memory.

Thanks!

devinemke

pablodelapena wrote:
What I want to do is firstly count how many unique image clicks I have...

SELECT COUNT(*) FROM imagestats

pablodelapena wrote:
...and then count how many I have of each and order it from the most to the least... giving a top 10

SELECT image_id, COUNT(*) AS count FROM imagestats GROUP BY image_id ORDER BY count DESC LIMIT 10

just guessing there about image_id as the field name.

pablodelapena

thanks, so the entire query would look soemthing like:

$query = "SELECT COUNT() FROM imagestats";
$result = mysql_query($query);
$query = "SELECT image_id, COUNT() AS count FROM imagestats GROUP BY image_id ORDER BY count DESC LIMIT 10";
$result = mysql_query($query);
$row = mysql_fetch_row($result);

and that would leave $row as an array containing the top 10 images clicked?

I dont really understand mysql and to eb honest i dont see how the 2 queries relate....

would the above work?

thanks

devinemke

<?php
mysql_connect('host', 'user', 'password') or exit(mysql_error());
mysql_select_db('dbname') or exit(mysql_error());

$result = mysql_query('SELECT COUNT(*) FROM imagestats') or exit(mysql_error());
echo 'total number of clicks: ' . mysql_result($result, 0) . '<br><br>';

$result = mysql_query('SELECT image_id, COUNT(*) AS count FROM imagestats GROUP BY image_id ORDER BY count DESC LIMIT 10') or exit(mysql_error());
echo 'top 10 images clicked:<br>';
while ($row = mysql_fetch_assoc($result))
{
	echo 'image_id: ' . $row['image_id'] . ' clicks: ' . $row['count'] . '<br>';
}
?>

pablodelapena

awesome, thanks very much!