Posted by Hilarion on 10/10/05 16:18

> I am trying to find the position of a record using COUNT, but I think I
> must be doing something wrong:
>
> $query = "Select COUNT(*) as HPos from Highscores where Score >
> $nScore";
> $resultid = mysql_query ($query, $chan);
> if (!$resultid)
> die('Error getting position:' . mysql_error());
>
> $row = mysql_fetch_array($result);
> $nPosition = $row['HPos'];
> echo "POS=";
> echo $nPosition;
>
>
> I always get a POS of 0 despite there being plenty of rows in the
> table. I still get the same even if I take out the "Score > $nScore".
>
> If I run the same query via PHPAdmin it works fine, so I'm obviously
> getting some kind of syntax wrong in my PHP script.



Do some debugging:

<?php
error_reporting( E_ALL );
//
// here should be the code to connect to MySQL server and select a DB
//
echo 'nScore: ' . $nScore . "<br />\n";
$query = 'Select COUNT(*) as HPos from Highscores where Score > ' . $nScore;
echo 'query: ' . $query . "<br />\n";
$resultid = mysql_query ($query, $chan);
if (!$resultid)
die('Error getting position:' . mysql_error());

$row = mysql_fetch_array($result);
if (!$row)
die("No result!<br />\n");
echo "<pre>\n";
print_r( $row );
echo "</pre\n";
$nPosition = $row['HPos'];
echo 'nPosition: ' . $nPosition . "<br />\n";
?>

Check if your way of connecting to MySQL and selecting a DB are correct too.

I also do not know where from does $nScore value come from, so maybe you
should check if it contains valid data before using it to prevent SQL
injection (sometimes <?php $nScore = intval( $nScore ); ?> is enough).
To get a valid position you should also use ">=" operator instead of ">".
The first one will give result of "1" for the best score (if it is in
the Highscores table), the second one gives "0".

Hilarion

[Back to original message]