mySQL help

[Kingy]

mysql>SET @rank:=0;

mysql>SELECT rank, user FROM (SELECT @rank:=@rank+1 AS rank, id, user, written FROM stats ORDER BY written DESC) AS foo WHERE user='Kingy';

 

+------+-------+

| rank  | user    |

+------+-------+

|    4  | Kingy  |

+------+-------+

 

that works perfectly for what i want to do, but as u can see im running that from the mysql command prompt, how would i get that exact output, using php?

[btherl]

The simplest way would be to fetch the results in order, and add the ranks in php.  I'm not sure how to do it in pure mysql.

[Kingy]

yeah im quite confused about how to do this in php..

 

i was about to try and write some code, but i actually have no idea :S anyone?

[awpti]

http://www.php.net/mysql/

 

Read up and enjoy. There are some pretty simple examples there.

[Kingy]

<?php

$query = "select * from stats order by written desc";
$query = mysql_query($query);
$rank = 0;
while($row = mysql_fetch_array($query)){
    $rank++;
    if ($row['user'] == "Kingy"){
      echo "You are ranked $rank;
       $rank = 0;   
       break;
   }
}
?>

well i have that and its works fine, what i want now is to have

 

echo "You are ranked $rank out of $total";

 

how would i go about getting the total, i'd say i'd use some sort of count?

 

[mmarif4u]

U can use count and also u have to use where if for some specific record you want to do counting.

Or:

$num=mysql_num_rows($query);

also can use.

Then use if statement and give condition like:

 

if($num > 10) { do something }else {
blah blah }

 

Am i right to get your idea.

[Kingy]

am i able to just echo $num to find out the total of rows it finds or not?

[btherl]

mysql_num_rows($query) will give you the number of rows.  BTW, I suggest you don't use $query for both the query itself and the result, as they are different things.  Better to do like this:

 

$query = "select * from stats order by written desc";
$result = mysql_query($query);
if (!$result) die("Error in $query: " . mysql_error());
print "There were " . mysql_num_rows($result) . " rows returnd\n";
$rank = 0;
while($row = mysql_fetch_array($result)){
    $rank++;
    if ($row['user'] == "Kingy"){
      echo "You are ranked $rank;
       $rank = 0;   
       break;
   }
}