Does my SQL Query not work properly?

Asked

Viewed 81 times

-1

I have a system that uses dates for registration and I separate the date by a explode formed by arrays and record them in a database, but when I do the query that searches them by order, the order embarrasses.

Following are examples of the code ->

      <?php 
                    $listar = new USER();
     $stmt = $listar->runQuery("SELECT * FROM horario ORDER BY dia,mes,ano DESC LIMIT 5");
    $stmt->execute();
    $Row=$stmt->fetchAll(PDO::FETCH_ASSOC);

Recent dates : 08/11/2018

Pesquisa de Monitorias por data recente

Registros Banco de Dados MYSQL

As you can see is not picking up the records as it should, please need help is my TCC!!!

php mysql query pdo

  • I think it is because you are giving a DESC ordination only in the year. Try to change to: ORDER BY DESC day, DESC month, DESC year. But if you made a date field with the full date would be easier for you to deal with later.

  • Also remember that you are sorting by date, where you have equal dates, but no time, so mysql knows which is the most recent or oldest data. then it continues the increasing ordering by the other fields. and maybe that’s why you’re having this shuffling.

  • I recently made an alternative table to record all dates, times to use the "Time Ago", but only this, in case I saved in the bank being as day, month and year because it is not only the site that feeds the bank, has more 2 fronts ( Android, Desktop ) who will also use the bank and it became easier to separate the date for them( I’m not making them ), but your SELECT example below solved my problem!! Thank you in advance.

1 answer

1


You are making an ordination increasing by day and month, and decreasing by only in the year.

Your data doesn’t have a time order, so mysql knows which is the latest or oldest date, so I think you can solve this problem with the ID by taking the order of the latest id.

I think a select like that would solve your case:

 SELECT * FROM horario ORDER BY ano DESC, mes DESC, dia DESC, idh DESC  LIMIT 5

Browser other questions tagged php mysql query pdo

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