How to sort a table in MYSQL, along with the query command

Question

I wanted to know if it is possible to sort a table in Mysql, and in this same line of code a search query.

 $ resultado_consulta="SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' LIMIT 5";

I wanted to order the year.

Answer 1

If the column calls ano:

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' LIMIT 5 ORDER BY ano

The ORDER BY is the clause you sort in the specified order. If you don’t have a suitable index the operation may take a while. If it takes time and the query occurs frequently it is best to create an index that helps this query. It is something more advanced, but fundamental in the scenario described.

If you don’t have the year and yes a date could get the date:

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' LIMIT 5 ORDER BY YEAR(data)

But this can be even more complicated for the index and I don’t know if the year is so important, the whole date can solve better because I would rate by year, so I could just do:

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' LIMIT 5 ORDER BY data

I put in the Github for future reference.

It is possible to use a ASC to say that it is ascending, but that is the default. The question does not say whether it is the desired.

The LIKE can hurt this enough, it is a terror used like this, but it can work well in some cases.

If that $pesquisar is a data that comes from outside the application, even indirectly, it is unsafe to use there and the server will suffer an invasion.

Documentation.

Answer 2

So the "N" ways to sort, I do not recommend making only a year field, because this can give you problems in the future, especially when you will have to display a report per period of records. You can separate by comma:order by id desc, ano desc etc... You need to have a date field of type: DATE, DATETIME, or TIMESTAMP. In case I called data_registro. If you want to order only per year, just do this to get you from the latest to the oldest (decreasing):

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' order by YEAR(data_registro) DESC LIMIT 5;

Or the reverse (ascending):

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' order by YEAR(data_registro) ASC LIMIT 5;

Now if you’re referring to the student class type: 1st year, 2nd year, 3rd year...

You can make an order like this:

SELECT * FROM alunos WHERE nome_aluno LIKE '%$pesquisar%' order by FIELD(turma, '1º ano', '2º ano', '3º ano') ASC LIMIT 5;