Jump to content


Photo

Ranking SQL results?


  • Please log in to reply
4 replies to this topic

#1 truCido

truCido
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 02 August 2006 - 07:39 PM

Basically I know how to create the query in SQL to output the result I want however I'm having problems outputting this onto a PHP page? Basically all I want to do is to put a rank along side each result.

SELECT a1.planet, a1.score, COUNT( a2.score ) as rank
FROM pa_planet_listing a1, pa_planet_listing a2
WHERE a1.score <= a2.score
OR (
a1.score = a2.score
AND a1.planet = a2.planet
)
GROUP BY a1.planet, a1.score
ORDER BY a1.score DESC , a1.planet DESC ;


#2 ronverdonk

ronverdonk
  • Members
  • PipPipPip
  • Advanced Member
  • 277 posts
  • LocationNetherlands

Posted 02 August 2006 - 07:46 PM

What columns exectly do you want to display? In a HTML table?

Ronald  8)
RTFM is an almost extinct art form, it should be subsidized.

#3 truCido

truCido
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 02 August 2006 - 07:48 PM

I want to display Planet in one column, score in another and then rank in another as well, its not the html that I can't do its the presenting the results from the query

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 02 August 2006 - 07:50 PM

$row['rank'] will be the rank column, or $row[2] if you use mysql_fetch_row
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 ronverdonk

ronverdonk
  • Members
  • PipPipPip
  • Advanced Member
  • 277 posts
  • LocationNetherlands

Posted 02 August 2006 - 07:57 PM

If you fetch your rows with $result = mysql_fetch_row.... then
you use $result[0] for planet, $result[1] for score and $result[2] for rank.

Or you might try to name these fields with the AS in MySql, and you could address them as
$result['planet'], $result['score'] and $result['rank'].

Ronald  8)
RTFM is an almost extinct art form, it should be subsidized.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users