mysql select order by starting with today’s date

Asked

Viewed 412 times

0

I have this appointment that seeks birthdays of the month:

$mesHoje = date("m");
$sqlAni = $pdo->prepare('SELECT * FROM aniversario WHERE MONTH(dia) = :mesHoje ORDER BY dia ASC');
$sqlAni->execute(array("mesHoje" => $mesHoje));

How do I sort by today? Example (today is the 23rd), then the dates below:

07/10/1928
12/10/1987
15/10/2012
24/10/1942
30/10/1999

They would be in that order (note that the first date is the 24th, as it is the earliest date of today, which is the 23rd):

24/10/1942
30/10/1999
07/10/1928
12/10/1987
15/10/2012

I can’t take into account the year, just the day.

mysql date order-by order

  • It is not clear what you mean by "order for today". If you want to sort by day, you can sort by the day of the month in ascending or descending order.

  • I want to sort by day, but I NEED the first day to be the closest to the current day (TODAY). I made it clearer in the example.

  • I tested the answer to the question https://stackoverflow.com/questions/6186962/sql-query-to-show-nearest-date and it worked. Take a look.

1 answer

1


I fall the way I know for this is using FIND_IN_SET

The test structure I created was this:

CREATE TABLE `tbl_Local` (
  `ID` int(11) NOT NULL,
  `Local` varchar(45) DEFAULT NULL,
  `COR` varchar(45) DEFAULT NULL,
  `DATA` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='    ';

INSERT INTO tbl_Local
VALUES (1,  "A",    "RED",  "2018-10-10"),
 (2,    "A",    "RED",  "2018-10-12"),
 (3,    "B",    "RED",  "2018-10-05"),
 (4,    "B",    "BLACK",    "2018-10-23"),
 (5,    "B",    "WHITE",    "2018-10-28"),
 (6,    "C",    "RED",  "2018-10-30"),
 (7,    "D",    "BLUE", "2018-10-01"),
 (8,    "E",    "BLUE", "2018-10-05"),
 (9,    "E",    "ORANGE",   "2018-10-06"),
 (10,   "E",    "YELLOW",   "2018-10-13"),
 (11,   "E",    "RED",  "2018-10-22"),
 (12,   "F",    "RED",  "2018-10-21"),
 (13,   "G",    "ORANGE",   "2018-10-18"),
 (14,   "G",    "BLUE", "2018-10-04"),
 (15,   "H",    "ORANGE",   "2018-10-02")

And the SQL syntax:

Select * from tbl_Local ORDER BY FIND_IN_SET(DAY(DATA), '23,24,25,26,27,28,29,30,31,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22')

The function FIND_IN_SET or (FIND if you prefer) this being used to create a list from a criterion I placed ( in the case the static list starting on the 23rd) The only question is this. There is no ( I at least do not know) any way this list come dynamic for the current day and from there go moving to the next days.

What I advise is that you pass this list as a string already mounted in your application and build its sql syntax dynamically

Something like that more or less:

$mesHoje = date("m");
$diaHoje = date("d");
$recebeArrayDia = "";
for ($iDia = $diaHoje; $iDia <= 31; $iDia++) {
    $recebeArrayDia .= $iDia.",";
}
for ($iDia = 1; $iDia < $diaHoje; $iDia++) {
    $recebeArrayDia .= $iDia.",";
}
$recebeArrayDia = substr_replace($recebeArrayDia, "", -1);
$sqlAni = $pdo->prepare('SELECT * FROM aniversario WHERE MONTH(dia) = :mesHoje ORDER BY FIND_IN_SET(DAY(dia), :recebeArrayDia)');
$sqlAni->execute(array("mesHoje" => $mesHoje, "recebeArrayDia" => $recebeArrayDia));

Follow link to the

Sqlfiddle

  • 1

    I hadn’t thought of it that way, it worked, thanks!

Browser other questions tagged mysql date order-by order

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