-2

I have a MySQL database with a table called subm_items

The table contains the following columns (item_id, score)

E.g.

+---------+-------+
| item_id | score |
+---------+-------+
|    1    |   20  |
|    2    |   30  |
|    3    |    5  |
|    4    |   10  |
|    5    |   80  |
+---------+-------+

I would like to be able to preform a query (AND DISPLAY THE RESULTS IN PHP) that determines the "rank" or "position" of an entry based on the score of an item compared to the score of all the other items.

For example if I pass item_id '5' (with a score of 80) it should return a rank of 1.

If I pass item_id '3' (with a score of 5) it should return a rank of 5.

IMPORTANT NOTE: If someone would be willing to provide me with not only the MySQL query but also how to display the results of the Query in PHP (using a variable for example) that would be great! I have come across this question in other posts but no one has provided how to actually use and display the results from the query in PHP.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1154955
  • 7
  • 1
  • 3
  • isn't that a select score from subm_item where item_id = 4 or whatever you want it to be? can you pass it in? – Kevin Jan 17 '12 at 22:20
  • http://php.net/mysql_query --- and welcome to the SO (stackoverflow), the place where people help to solve issues, but not do your work for free – zerkms Jan 17 '12 at 22:21
  • I don't want to display the score, I know the score, I want to display the rank, and the rank can only be determined by comparing the score of a single item to the other items! Thanks though. – user1154955 Jan 17 '12 at 22:22
  • Which posts have you looked at regarding obtaining the result? – CBusBus Jan 17 '12 at 22:23
  • Wow I didn't think my question was over the top difficult! I don't expect free work, I tried to use the site to figure it out but I haven't been able to yet! I am sure someone won't consider this to be free work and will be willing to help! Thanks for nothing zerkms! – user1154955 Jan 17 '12 at 22:23
  • 2
    "provide me with not only the MYSQL Querry but also how to display the results of the Query in PHP"... please at least attempt this yourself before posting and there will be many more people willing to help you out. – Matt K Jan 17 '12 at 22:23
  • I have looked at a few, here are some http://stackoverflow.com/questions/8767323/how-best-to-get-someones-rank-from-a-scores-table-with-php-and-mysql-without http://stackoverflow.com/questions/1293817/mysql-get-users-rank – user1154955 Jan 17 '12 at 22:24
  • Matt, I am new with MYSQL and PHP, I know how to post data from a DB ROW but I don't know how to do this from a query that doesn't simply pull data directly from the DB! – user1154955 Jan 17 '12 at 22:25
  • @user1154955: where does it pull the data if not from the DB? Sql queries are processed by mysql server. You know how to output the query results - perfect. Remove all the php-stuff from the question and ask only about mysql query – zerkms Jan 17 '12 at 22:27
  • yes, but you failed to explain that and to show any attempt at solving the problem yourself. that's why you're being downvoted. – Matt K Jan 17 '12 at 22:28
  • Is `score` unique? If not, how do you want to handle ties? If two and only two items are tied for first place, is the next highest scoring item ranked second (à la DENSE_RANK) or third (à la RANK)? – pilcrow Jan 17 '12 at 22:29
  • @zerkms - Well yes it is pulling data from the DB but it is not displaying data directly from the DB, that is what I meant. It is using data from the DB but displaying something else, in this case the rank which is not stored in the DB> – user1154955 Jan 17 '12 at 22:32
  • @MATTK - Sorry, I have been trying for hours but I can't figure out how to take the results from a query and display them, I only know how to display results from a ROW in a DB. – user1154955 Jan 17 '12 at 22:33
  • @user1154955: oh come on `SELECT 1 as A, 2 as B`. Are you able to retrieve the results from this query in php? Note: the data is hardcoded and is not retrieved from any of storage engine – zerkms Jan 17 '12 at 22:34
  • @pilcrow - Good question! Score may not be unique so I suppose if there is a tie (say 2 people is first place) the 3rd person should be ranked 3rd. – user1154955 Jan 17 '12 at 22:34
  • @zerkms - I honesty don't know! $someVar = A; ? $someVar_2 = B; ? – user1154955 Jan 17 '12 at 22:36
  • @user1154955: there is **absolutely** no difference. You extract the data in absolutely the same way like you do that with row: mysql_query + mysql_fetch_array – zerkms Jan 17 '12 at 22:37
  • @zerkms: So within the mysql_fetch_array how do I access the value from '@rank'? That is what I can't figure out! – user1154955 Jan 17 '12 at 22:44
  • @user1154955: like you do that for field. Imagine that `rank` is a real field. How would you extract the value? – zerkms Jan 17 '12 at 22:45
  • @zerkms: $someVar = $row['rank']; ? – user1154955 Jan 17 '12 at 22:46
  • @user1154955: exactly! When you retrieve result set back from mysql - it doesn't matter how and where the data came from - you just get keys and values. – zerkms Jan 17 '12 at 22:48

1 Answers1

5

Well, if you just want to display the rank table, you can do this:

$sql = mysql_query( "SELECT * FROM `subm_items` ORDER BY `score` DESC");
$i = 0;
while($row = mysql_fetch_assoc($sql)) {
    $i++;
    echo "Rank ".$i.": ID#".$row['item_id']." - Score: ".$row['score']."<br />\n";
}

If you want to compute the ranks in MySQL, you want something like this:

SET @rank=0;
SELECT (@rank:=@rank+1) as `rank`, `item_id`, `score` FROM `subm_items` ORDER BY `score` DESC

But IMO it's easier to just have a counter in PHP.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • Hi Kolink Thanks for the help! So how would I display the actual rank in PHP? Is 'rank' used? What I have so Far is this: $sqlrank=mysql_query("SET @rank=0; SELECT (@rank:=@rank+1) as `rank`, `item_id`, `pct` FROM `subm_items` ORDER BY `pct` DESC"); while($row=mysql_fetch_array($sqlrank)) { $someVAR = $rank; } echo $someVAR; – user1154955 Jan 17 '12 at 22:40
  • @user1154955: there are 2 queries, so you need to perform `mysql_query()` twice – zerkms Jan 17 '12 at 22:44
  • Can't I just use the 2nd one? I don't want to display the rank table, just the rank for a single item. – user1154955 Jan 17 '12 at 22:49