Mysql query that shows matches and then the rest of the rows

Question

Mysql query that shows matches and then the rest of the rows

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have a MySql database in which I keep activities with the following structure.

id (int) | nombre(varchar) | descripcion(text) | mes(tinyint)

What I'm trying to get is to show in php a list of them from the current month.

Until now I solved it by doing 2 separate consultations and then repeating the results consecutively in html ...

$mesactual = date("m")-1;

.php

$stm = $pdo -> prepare("SELECT * FROM actividades WHERE mes >= $mesactual ORDER BY mes");
$stm -> execute();
$resultado = $stm -> fetchAll();

$stm = $pdo -> prepare("SELECT * FROM actividades WHERE mes < $mesactual ORDER BY mes");
$stm -> execute();
$resultado2 = $stm -> fetchAll();

.html

foreach ($resultado as $item){
...
}
foreach ($resultado2 as $item){
...
}

Is there any way to unify in a single query?

mysql php mysqli pdo

asked by user2411598 05.06.2017 в 01:49

source

2 answers

like I think page in an entry in wordpress Bulk inserts in jpa without hibernate

score 0 · Answer 1

You can put a CASE in ORDER BY

SELECT *   FROM actividades 
ORDER BY 
CASE mes
    WHEN mes LIKE $mesactual THEN 1
END 
desc ;

OR Make a UNION ALL and exclude the first

SELECT * FROM actividades 
WHERE mes >= $mesactual 
UNON ALL 
SELECT * FROM actividades 
WHERE mes <> $mesactual

score 0 · Answer 2

Do a CASE in Query SQL

SELECT *,(CASE WHEN mes >= $mesactual THEN 1 ELSE 0 END) ordenamiento FROM actividades ORDER BY ordenamiento DESC, mes ASC

the case within the select will return 1 or 0 so I use it in the order giving priority to ordering from highest to lowest with the order of months