mysql select order by starting today's date

Question

mysql select order by starting today's date

Navigation

#1 by (1 votes)

1

I have this query that looks for 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 order for today? Example (today is 23), then the dates below:

They would be in this order (note that the first date is 24, because it is the closest date today, which is 23):

I can not take into consideration the year, just the day.

mysql date order-by order

asked by anonymous 23.10.2018 / 16:29

1 answer

How to redirect the user to a specified page after login How to format all labels of a form with a style associated with an id?

score 1 · Accepted Answer

I fall into the way I know for this is using FIND_IN_SET

The test framework 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

23.10.2018 / 17:48