MySQL Joins and ORDER BY in PHP / MYSQL

Question

I have a database that looks like this with two tables

Items
id          |       Title
-----------------------------
1                   Bus
2                   Plane
3                   Jet
4                   Shoes
5                   Chair

Sorting
id               |         CatID         |       ItemID          |  SortOrder
------------------------------------------------------------------------------- 
1                            3                      3                   3                 
2                            3                      2                   1
3                            3                      4                   2
4                            3                      1                   0
5                            4                      5                   4

I can't figure out how to list the Titles of the ITEMS table based on the "SortOrder" Column of the SORTING table.

Here is what I tried so far:

   SELECT * 
     FROM Items
LEFT JOIN Sorting ON Items.id = Sorting.ItemID 
    WHERE Sorting.CatID = 3 
 ORDER BY Sorting.SortOrder

I'm not sure what I'm doing wrong

EDIT

It looks like the MySQL query is correct, the problem is happening because when I output the $row['id'] of the Items Table it is incorrect. I have an Ajax PHP update that is updating the database based on the id of an li tag.

Any ideas why the $row['id'] is outputting incorrectly? I think it has something to do with the Items.id = Sorting.ItemID

Answer 1

Try

SELECT * 
     FROM Items
LEFT JOIN Sorting ON Items.id = Sorting.ItemID 
    WHERE Sorting.CatID = 3 
 ORDER BY Sorting.SortOrder ASC

add DESC or ASC in ORDER BY clause.

if you use ASC then sorted result will be 0 1 2 3 4 for SortOrder.

sample php code to get title

<?php
    $query  = mysqli_query(above_query)or die(mysqli_error());
    while($result = mysqli_fetch_assoc($query))
    {
           echo $result['title']. '<br/>';
    }

Answer 2

This works as expected - SQLFiddle DEMO:

SELECT i.*, s.SortOrder
FROM items i, sorting s
WHERE i.id = s.ItemID 
  AND s.CatID = 3 
ORDER BY s.SortOrder