Need Help with displaying table results - High Score List

[widget]

Using mysql and php

 

TABLE

id    item_id    pet_id  game 

 

id is auto increment

items id is the item number of the book thats been read

pet_id is the pet id number or the pet that read the book

game is the game number (always 1)

 

TABLE EXAMPLE

      id  item_id  pet_id  game 

      39  30892    37057    1

      40  30892    37263    1

      41  30975    37329    1

      42  30976    37329    1

      43  30989    37329    1

      44  30997    37329    1

      45  30999    37329    1

 

What I need to do is create a high scores list showing the pet_id and how many books its read.

 

Could someone help me out with the code to do this?

 

My current useless code is

 

<?

$x = 0;
$query = mysql_query("SELECT pet_id FROM read2");
$num_rows2 = mysql_num_rows($query);

while ($array = mysql_fetch_array($query))

{
echo "<p align=center>$num_rows2</b></font></p></td>";

$x++;
}
print "</table></center>";
?>

 

which just displays the total number of entries in the table.

[widget]

Ive tried the following code aswell but get an error

 

<? 

$first_query = mysql_query('SELECT DISTINCT `pet_id` FROM `read2`'); 

while($each = mysql_fetch_array($first_query)) { // while loop to go through all the usernames 
  $new_query = 'SELECT * FROM `read2` WHERE `pet_id` = \'' . $each['item_id'] . '\''; 
  $sql = mysql_query($new_query); 
  $username = $each['item_id']; 
  $number = mysql_num_rows(mysql_query($sql)); 

  echo $username . ' - ' . $number . '<br />'; 
} 

?> [code]

[/code]

[widget]

Ok Ive got it outputting a count of how many times a pet_id is in the table but cannot get it to do it in order of highest to lowest.

 

If I place  ASC LIMIT 0, 100 anywhere i get errors.

 

<? 

$first_query = mysql_query('SELECT DISTINCT `pet_id` FROM `read2`'); 

while($each = mysql_fetch_array($first_query)) { // while loop to go through all the usernames 
  $new_query = 'SELECT * FROM `read2` WHERE `pet_id` = \'' . $each['pet_id'] . '\''; 
  

$sql = mysql_query($new_query); 
  $username = $each['pet_id']; 
  $number = mysql_num_rows($sql);

  echo $username . ' - ' . $number . '<br />'; 
} 



?> 

 

RESULT

 

37359 - 31

37329 - 48

37484 - 2

37482 - 1

37263 - 5

37057 - 47

37501 - 3

37507 - 6