Sort results with month and year

Asked

Viewed 139 times

2

I have a table with the fields mes E ano. How can I sort the results according to the month.

I’m already ordering according to the year

=> SELECT * FROM tabela WHERE id_cliente = '$id_cliente' ORDER BY ano DESC Now how can I order the months too ? Ex ( January, Feb, sea... )

PS: mes (varchar 255), ano (varchar 255) that would be a bad practice?

php mysql sql mysqli

  • How are you storing the month? Number or written?

  • Written, that would be a bad practice?

  • Yes, for performance purposes, put a field of the kind tinyint and number 1 to 12, it would be lighter and simpler to do the ordering you want

  • 1

    Bad practice for using the name? It will depend on the rule of your application. But what to use varchar 255 for this is absurd, is.

  • @Marcelodeandrade can tell me about these values? how to use them correctly?.

  • In general I would not consider ideal. The VARCHAR 255 using utf8mb4 will cost 1020 bytes at most. In older versions of Mysql, I don’t know currently, it would even be necessary to enable the innodb_large_prefix so that you could set an index in that situation. While using the TINYINT uses a single byte, so it can store 256 values, sufficient to store 1 until 12. The year the same thing, using SMALLINT would use 2 bytes, and could set "2017" quietly.

  • You don’t think using a date field would be more convenient?

Show 2 more comments

1 answer

7


That way with the month of the kind sweep, have to use the FIELD in the ORDER BY

SELECT * FROM tabela
WHERE id_cliente = '$id_cliente'
ORDER BY FIELD(mes, 'Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho', 'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro'), ano DESC

  • Thank you Tafarel, it worked correctly!

Browser other questions tagged php mysql sql mysqli

You are not signed in. Login or sign up in order to post.