problems with MYSQL query

[irishgirl]

Hi,

I want to find a record in the table with the highest score. I then want to display all (seven)attributes of that record.

At present, this is the query I am using:

$highestscore="SELECT max(totalScore) FROM customer";
$result=mysql_query($highestscore, $db);

if($info=mysql_fetch_array($result)){

	while ($info=mysql_fetch_array($result)){

                             --------DISPLAY RECORD(S) HERE-------
                             }"

---

Needless to say, it isn't working for me.

'totalScore' is an attribute, there are several other attributes, e.g. name, occupation etc. I want to display the record with the highest score.
I think it might be something to do with selecting only one attribute in 'SELECT statment', and maybe it's not being read into the array properly???????????. Then again I could be wrong!!!

Please can anyone help me? I'm a newbie at this.
Thanks

Help would be greatly appreciated

[ultraslacker]

You could use GROUP BY, ORDER BY, and LIMIT for this.

SELECT max(totalscore) as highscore, name, occupation FROM customer GROUP BY name, occupation ORDER BY highscore DESC LIMIT 1

[rich81]

i don't use mysql much but i have an idea of what might be wrong.

when you use $var=mysql_fetch_array($results) in an if() or while() statement, I THINK that as well as returning True, it'll move the cursor to the next record, which is why this works...

while($info=mysql_fetch_array($results)) {

...and then it moves to the next record for each loop.

but because you're using...

if($info=mysql_fetch_array($results)) {

...first, it's succeeding on that IF, because it finds a record.... BUT because you're returning only one record (MAX), it's moving the cursor on to a blank record after passing the IF. So when you go through the first loop in your WHILE statement, there are no records left.

try removing the IF altogether. if no records are returned, the while statement should simply not execute and just move on, which pretty much makes the if($info=mysql_fetch_array()) line redundant. try removing it and try again!

hope this helps. :-)