ORDER BY not working as expected in PHP/MySQL

Question

My code:

$afer_result = mysql_query("SELECT * FROM aq_afers WHERE aferId = '".$afer_id."'");

while ($afer = mysql_fetch_array($afer_result)) {
$item_result = mysql_query("SELECT * FROM aq_".$afer['ItemCategory']."s WHERE ItemId = '".$afer['ItemId']."' ORDER BY ItemLevel");
$item = mysql_fetch_assoc($item_result);

echo $item['ItemLevel'];
echo "\n";
}

I expect the output to be a list of numbers to be sorted from lowest to highest, but this is what my output looks like:

Any idea why ORDER BY is not working as I expect it to?

It does that because you select 1 row at a time. There is no relation to the other rows. You could store it to an array and have PHP sort it...or you probably could do this with 1 query and use a `join`. — user3783243, Dec 30 '18 at 20:46
You also should not use `mysql_` functions anymore. This is likely to stop working in the future, and is insecure. — user3783243, Dec 30 '18 at 20:47
Must read [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — Raymond Nijland, Dec 30 '18 at 21:04
@user3783243 In the future was three years ago in 2015 when PHP 7.0 deleted those functions. — tadman, Dec 30 '18 at 21:25
**WARNING**: Do not use the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface which was removed in PHP 7. A replacement like [PDO is not hard to learn](https://phpdelusions.net/pdo) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Here parameters are **NOT** [properly escaped](http://bobby-tables.com/php) and this has severe [SQL injection bugs](http://bobby-tables.com/) in this code. Escape **any** and all user data, especially from `$_POST` or `$_GET`. — tadman, Dec 30 '18 at 21:25
@tadman But not for the OP. Since they aren't getting undefined functions it is working for them. In the future, when they, or their host, upgrades the PHP version it will stop. — user3783243, Dec 30 '18 at 21:27
@user3783243 It's true, a lot of hosting providers are living in the distant past. — tadman, Dec 30 '18 at 21:29

GMB · Answer 1 · 2018-12-30T23:40:28.120

The problem with your code is that you run several queries within a loop. The results of each query are ordered, but not the global results.

A solution is to use the loop to build a UNION sql query. The you can run the query outside the loop ; the ORDER BY clause of an UNION query applies globally to its results. Of course this assumes that all queries return the same columns (else you’ll need to adapt the code).

Code ;

$afer_result = mysql_query(
    "SELECT * 
    FROM aq_afers 
    WHERE aferId = '".$afer_id."'"
);

$sql_parts = array();
while ($afer = mysql_fetch_array($afer_result)) {
    array_push(
        $sql_parts,
        "SELECT * 
          FROM aq_".$afer['ItemCategory']."s
          WHERE ItemId = '".$afer['ItemId']."'"
    );
}

$sql = join(' UNION ALL ', $sql_parts);
$item_result = mysql_query($sql . 'ORDER BY ItemLevel');
while ($item = mysql_fetch_array($item_result)) {
    echo $item['ItemLevel'];
    echo "\n";
}

Thank you for your help, when I run the SQL query I get this MySQL error: "The used SELECT statements have a different number of columns". I think I need to add "dummy columns" into the query to solve this — , Dec 30 '18 at 23:30
@JDoe : yes all UNIONed query should return the same columns... Adding dummy columns is indeed a solution. — GMB, Dec 30 '18 at 23:41

Sigee · Answer 2 · 2018-12-31T11:56:05.073

Your code select the categories and goes throug each of them. It selects the ItemLevel for each category sorted, but not all of them at the same time. For the first category 10, 37, 62 For the second category: 15, 35, 55, 75, 95, 105 For the third category: 70 Etc.

So you should merge your 2 sql queries into one and order that result.
If it is not possible you should store the itemlevels into an array and sort it before printing them out.

$afer_result = mysql_query("SELECT * FROM aq_afers WHERE aferId = '".$afer_id."'");
$itemLevels = [];
while ($afer = mysql_fetch_array($afer_result)) {
    $item_result = mysql_query("SELECT * FROM aq_".$afer['ItemCategory']."s WHERE ItemId = '".$afer['ItemId']."' ORDER BY ItemLevel");
    $item = mysql_fetch_assoc($item_result);
    $itemLevels[] = $item['ItemLevel'];
}
asort($itemLevels);
foreach ($itemLevels as $itemLevel) {
    echo $itemLevel;
    echo "\n";
}

It's not possible to merge the queries as the second query selects from a different, dynamically named table each time. — Nick, Dec 30 '18 at 21:04
Personally I would put *all* the item data into an array (not just the `ItemLevel` values) and use `usort` to sort on the `ItemLevel` values. — Nick, Dec 30 '18 at 21:06

ORDER BY not working as expected in PHP/MySQL

2 Answers2