[SOLVED] Desperate For Help <<< Display Results from Highest to Lowest??

[widget]

Im creating a high score table.

 

Using php and mysql

 

Table name read2

 

2 rows - pet_id and item_id

 

<? 

$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

37509 - 1

37516 - 1

37481 - 1

37520 - 3

37885 - 1

37323 - 15

37668 - 55

 

 

I need it to display the data sorted from the highest number (left column) to lowest with a limit of say 100.

I've tried adding in ORDER BY `pet_id` ASC LIMIT 0, 100  but I just get errors.

 

 

[paul2463]

what errors??

 

and why two queries

 

try

<?php
$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` ORDER BY `pet_id` ASC";
$result= mysql_query($query); 
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id']; 
  $number = $row['amount'];
  echo $username . ' - ' . $number . '<br />'; 
} 
?>

[widget]

Why 2 queries?

 

No idea - I was just following a code example I found via google.

 

I tried your code and get this error

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/chicka/public_html/books_high_scores.php on line 23

 

line 23 is while($row = mysql_fetch_assoc($result))

[paul2463]

silly me I forgot my own methods when i typed this, there is obviously a problem with the query. try this one

<?php
$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` GROUP BY `pet_id` AND ORDER BY `pet_id` ASC";
$result= mysql_query($query) or die ("Error in query " . mysql_error()); //whats the error??
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id']; 
  $number = $row['amount'];
  echo $username . ' - ' . $number . '<br />'; 
} 
?>

 

EDIT + if you are reading this after trying the code I have edited the query to add the GROUP BY command which is what was probably causing the error if so try it again now it is there - Paul

[widget]

I  tried that and now get this error

 

Error in query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `pet_id` ASC' at line 1

[bubblegum.anarchy]

$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` GROUP BY `pet_id` AND ORDER BY `pet_id` ASC";

[widget]

Thanks but I just tried that too and its at least showing the data but still not in order of books read.

 

Current code

 

<?php
$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` GROUP BY `pet_id` ORDER BY `pet_id` ASC";
$result= mysql_query($query) or die ("Error in query " . mysql_error()); //whats the error??
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id']; 
  $number = $row['amount'];
  echo $username . ' - ' . $number . '<br />'; 
} 
?>

[widget]

Any takers on this problem?

[Illusion]

try this

<?php
$query = "SELECT DISTINCT `pet_id` FROM `read2` ORDER BY `pet_id` DESC";
$result= mysql_query($query) or die ("Error in query " . mysql_error()); //whats the error??
$number=mysql_num_rows($query);
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id'];  
  echo $username . ' - ' . $number . '<br />'; 
   $number =$number-1;
} 
?>
can I know the purpose of displaying number there. 

[widget]

I dont think thats quite what I wanted.

 

What that did was display each pet id with a number following, going from 1 to 100

 

Maybe if I explain the situation better...

 

I have a pet site I am making and users can purchase books to read to their pets.

When a book has been read the pets id number along with the items id number are placed into the table.

 

What I would like to display is a list of what pet has read the most books from highest to lowest with a limit of 100.

 

This below code is the only code I've been able to use without getting errors but unfortunately it doesnt display the pets from books read highest to lowest and displays the pet id from highest to lowest instead.

 

<?php
$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` GROUP BY `pet_id` ORDER BY `pet_id` ASC";
$result= mysql_query($query) or die ("Error in query " . mysql_error()); //whats the error??
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id']; 
  $number = $row['amount'];
  echo $username . ' - ' . $number . '<br />'; 
} 
?>

 

Out put

 

37057 - 48

37061 - 1

37139 - 4

37195 - 1

37261 - 1

37263 - 5

37275 - 2

37320 - 3

37321 - 3

37323 - 61

37329 - 48

37359 - 41

37384 - 1

37391 - 2

37396 - 1

37454 - 1

37460 - 2

37465 - 1

37481 - 1

37482 - 1

37484 - 2

37486 - 1

37501 - 3

37506 - 16

 

[Wildbug]

SELECT pet_id,COUNT(*) AS amount FROM read2 GROUP BY pet_id ORDER BY COUNT(*) DESC LIMIT 100

[Illusion]

may be this is what you want

$query = "SELECT DISTINCT `pet_id`, COUNT(`pet_id`) AS amount FROM `read2` GROUP BY `pet_id` ORDER BY `amount` DESC LIMIT 100";
$result= mysql_query($query) or die ("Error in query " . mysql_error()); //whats the error??
while($row = mysql_fetch_assoc($result))
{
  $username = $row['pet_id']; 
  $number = $row['amount'];
  echo $username . ' - ' . $number . '<br />'; 
} 
?>

 

[widget]

Woohoo

 

Thank you!!!

 

It worked