MySQL Reference: ORDER BY

Using ORDER BY will sort the results in a specific order based on the values of the desired column: see the example for more details…

MySQL Example: (!)
SELECT * FROM table ORDER BY name DESC;

Description:

This will SELECT all the records from the db table and sort them via ‘name’ is a DESCending order.

ASC can also be used.

MySQL Example: (!)
Select Name,Category FROM authors ORDER BY Category,Name;

Description:

Will allow you to sort by categories listed in a seperate table
IF the category column in this primary table contains ID values
from your ID column in your second reference table.

So your first "authors" table looks like:

id name category
1 Henry Miller 2
3 June Day 1
3 Thomas Wolf 2

and your second reference table looks like:

id category
1 Modern
2 Classics

Now when the order of categories is changed in the second table
the order of categories will be reflected in the primary table.

Then just select the categories from the reference table and put
the list into a numbered array. Then in your script when you run
across a category number from the first recordset just reference
the value from the index in the second array to obtain the value.
In php in the above example it might look like:

foreach ($recordset as $key => $record) {
echo $record["id"] . ":" . $record["name"] . ":" . $ordered_cats[$record["category"]];
}

This may seem obvious to some but I was pulling my hair out
trying to figure out how to order a recordset based on a list
from a different table. Hope this helps someone.

Leave a Reply

Your email address will not be published. Required fields are marked *