Need to order and grab SQL rows by 2 criteria

Question

I am using the following PHP and MySQL code to pull high score records out of a database, 5 above and 5 below the users score.

<?php

$dbcon = mysql_connect("localhost", "XXXX", "XXXX") or      die(mysql_error());
mysql_select_db("tulesblo_koreangame", $dbcon) or die(mysql_error());
mysql_query("SET NAMES utf8");

$name = $_POST["name"];
$email = $_POST["email"];
$score = $_POST["score"];
$table = "";
$submit = "";
$input = "";
$newposition = $_POST['position'];

$position = mysql_query("(SELECT position 
    FROM highscore 
   WHERE score < '$score'
ORDER BY score DESC
   LIMIT 1)");

if(!$name){

$gethigherrows = "(SELECT * 
    FROM highscore 
   WHERE score >= '$score'
ORDER BY score ASC
   LIMIT 5)";

$getlowerrows = "(SELECT * 
    FROM highscore 
   WHERE score < '$score'
ORDER BY score DESC
   LIMIT 5)";
   
$higherrows= mysql_query($gethigherrows);
$lowerrows= mysql_query($getlowerrows);

if(mysql_error())echo mysql_error();
 
while($row=mysql_fetch_array($higherrows)) 
{
    $uppertable .= "<tr><td>$row[position]</td><td>$row[name]</td>    <td>$row[score]</td></tr>";
}

$x = 0; 
if (mysql_num_rows($lowerrows) > 0)
{   mysql_query("UPDATE highscore SET position = position + 1 WHERE score <  '$score'")or die("update failed");
    while($row=mysql_fetch_array($lowerrows)) 
    {   
        if ($x == 0)
            {$position = $row['position'];};
        $x++;
        $newpos = $row[position]+1;
        $lowertable.= "<tr><td>$newpos</td><td>$row[name]</td>    <td>$row[score]</td></tr>";
    }
    $input = "<tr><td id='position'>$position</td><td><input  id='nameinput'type='text' /></td><td>$score</td></tr>";
    $submit = "<br />Enter email if you want to receive a prize!<br /><input  id='emailinput'type='text' /><br /><input id='submithighscore'type='submit'  value='Submit'>";
}

$table .= "<table id='scoretable'><tr><th>Position</th><th>Name</th><th>Score</th></tr>";
$table .= $uppertable;
$table .= $input;
$table .= $lowertable;
$table .= "</table>";
$table .= $submit;
$table .= "<br /><span class='msg'></span>";
echo $table;

}else{  echo($newposition);
mysql_query("INSERT INTO highscore VALUES (NULL, '$score', '$name', '$email',     '$newposition')");
}

?>

screen shot

The only problem, as you can see, is that if there are several instances of the same score, as there are and inevitably will be, the positions get jumbled up. How can I select by score first but ensure that it grabs the positions in a sensible order?

EDIT: OK, using the following

$gethigherrows = "(SELECT * 
    FROM highscore 
   WHERE score >= '$score'
ORDER BY
position ASC
   LIMIT 5)";

$getlowerrows = "(SELECT * 
    FROM highscore 
   WHERE score < '$score'
ORDER BY score DESC, 
position DESC
   LIMIT 5)";

I now get:

enter image description here

Which is better but the above scores really need to be 9,8,7,6,5

Honestly SQL fries my brain :P

For your next question, post only relevant code and not the whole file. — svens, Oct 04 '11 at 18:28
Surely if someone has the same score they are all the same position? — AndrewC, Oct 04 '11 at 18:28
You have an SQL-injection hole, see: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Johan, Oct 04 '11 at 18:29

score 3 · Accepted Answer · answered Oct 04 '11 at 18:27

3

You can specify more than one column in a ORDER BY clause.

ORDER BY score ASC, position DESC

answered Oct 04 '11 at 18:27

svens

11,438
6
36
55

OK, I think I've at least visualized it correctly: What I need is to grab the 5 records with a higher score than the user BUT with the lowest (highest number) position, so then the results will be properly sequential. You get what I mean? – Tules Oct 04 '11 at 19:17
something like:---------- SELECT * FROM highscore WHERE score > '$score' AND position (is highest numbers in group) – Tules Oct 04 '11 at 19:20
Not really. Something like `.. WHERE score > {$score} ORDER BY position DESC`? – svens Oct 04 '11 at 19:38

score 2 · Answer 2 · answered Oct 04 '11 at 18:33

First I would fix those SQL-injection holes:

$score = mysql_real_escape_string($_POST['score']);
$name = mysql_real_escape_string($_POST['name']);
$email = mysql_real_escape_string($_POST['email']);

$sql = "(SELECT * 
        FROM highscore 
        WHERE score >= '$score'
        ORDER BY score ASC, position DESC
        LIMIT 5)";

If you don't I can substitude ' or (1=1) LIMIT 1 UNION SELECT password, email, username FROM users -- for score. To get a list of all passwords and email addresses of your users.

Need to order and grab SQL rows by 2 criteria

2 Answers2