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

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 2

As previously mentioned MySQL doesn't support alphanumeric sorting. One common trick to solve this is to first order by length:

ORDER BY LENGTH(column_name), column_name 

As long as the non-numeric part of the value is the same length, this will sort 1 before 10, 10 before 100, etc.