How to order strings format from database in PHP?

Question

I have numbers like this: 666/2014, 01/2014, 1/2014, 02/2014, 125/2014, 06/2014 ...etc as STRING named 'n_inscription' in database. I want to retrieve those strings In ORDER from database

I used this:

$sql_students = $bd->query("SELECT * FROM `es_student_infos`
                            WHERE school_year='$school_year'
                            ORDER BY right(n_inscription, 4) * 1,
                            substring_index(n_inscription, '/', 1) * 1");

I get result like this: 01/2014, 02/2014, 06/2014, 1/2014, 125/2014, 666/2014

and the result I'm looking for is like this: 01/2014, 1/2014, 02/2014, 06/2014, 125/2014, 666/2014

any suggestion please?

Answer 1

The correct way is change the type of n_inscription to date/datetime/timestamp and then use order by as default.

There's more advantages to use date fields like comparison and date calculations, so I suggest you do like this.

EDIT: Change the order by collumn to school_year and not n_inscription:

 SELECT * FROM `es_student_infos`
                            WHERE school_year='$school_year'
                            ORDER school_year

Answer 2

The best approach is probably to normalize the input so normal sorting does what you want it to do. For example, store the student number and year in two separate INTEGER columns and then ORDER BY studentNumber ASC, inscriptionYear ASC.

If that's absolutely not possible:

SELECT
  *
FROM
  es_student_infos
ORDER BY
  CAST(RIGHT(n_inscription, 4) AS UNSIGNED) ASC,
  CAST(LEFT(n_inscription, LOCATE('/', n_inscription) - 1) AS UNSIGNED) ASC

Link to fiddle demonstrating the solution: http://sqlfiddle.com/#!2/a5538/1/0