How to order 1,2,3 not 1, 10, 11, 12 in mySQL

Question

The following code outputs in order of 1, 10, 11, 12 of id.

I want to make it 1,2,3,4...

Could anyone tell me what I should do please.

$Q = $this->db->query('SELECT P.*, C.Name AS CatName FROM products AS P LEFT JOIN categories C ON C.id = P.category_id');

Thanks in advance.

Answer 1

Well, you're not setting any ORDER BY clause.

Answer 2

First, add an order by clause at the end:

ORDER BY category_id

If category_id is a string, then you must treat it like an integer. There are a few ways to do this. I usually add a zero. You can also cast it.

ORDER BY category_id + 0

Answer 3

Make sure that the column that holds 1,2,3,4 is INT type, if it is TEXT, you will not get numerical order, but what you describe 1, 10, 11, 2, 22, 23, 31, etc;

And like others mentioned, use ORDER BY

Answer 4

You can do an explicit cast by doing:

ORDER BY CAST(category_id AS UNSIGNED INTEGER)

But you should reconsider you database layout as a field containing only numeric values should also be of an numeric type..

Best wishes, Fabian