Hypister Hypister - 2 months ago 7
MySQL Question

Put desired row id on top

I have the following PHP code with the query to retrieve the products from database:

$sql = "SELECT * FROM products WHERE id_category = :id_category";
$query = $conn->prepare($sql);
$query->execute(array
(
'id_category' => $id_category
));

while($row = $query->fetch(PDO::FETCH_ASSOC))
{
extract($row);
echo $id_product; //1, 2, 3, 4, 5, 6, 7 etc
}


What I need is to put the row of the product with id 5 on top, and I simply don't know how to do this. My attempts with
ORDER BY
all have failed.

Answer

You can, with the ORDER BY clause be specific with the order returned so perhaps for your needs you can try:

select * from `products` where `id_category` = :id_category order by `id`=5 desc;

As an example of how this changes the ordering consider the following

mysql> select * from accounts;
+----+-----------+----------+----------+
| id | sponsorID | open     | close    |
+----+-----------+----------+----------+
|  1 |        23 | 6:00 AM  | 10:30 PM |
|  2 |        23 | 6:00 AM  | 8:30 PM  |
|  3 |        21 | 11:00 AM | 5:30 PM  |
|  4 |        23 | 7:00 AM  | 2:30 PM  |
|  5 |        24 | 9:00 AM  | 11:30 PM |
|  6 |        25 | 6:00 AM  | 9:30 PM  |
|  7 |        99 | 6:00 AM  | 9:30 PM  |
+----+-----------+----------+----------+


mysql> select * from accounts order by id=5 desc;
+----+-----------+----------+----------+
| id | sponsorID | open     | close    |
+----+-----------+----------+----------+
|  5 |        24 | 9:00 AM  | 11:30 PM |
|  1 |        23 | 6:00 AM  | 10:30 PM |
|  2 |        23 | 6:00 AM  | 8:30 PM  |
|  3 |        21 | 11:00 AM | 5:30 PM  |
|  4 |        23 | 7:00 AM  | 2:30 PM  |
|  6 |        25 | 6:00 AM  | 9:30 PM  |
|  7 |        99 | 6:00 AM  | 9:30 PM  |
+----+-----------+----------+----------+

You can specify more than one explicit term in the order by or mix the ordering of subsequent columns

mysql> select * from accounts order by id=5 desc, id desc;
+----+-----------+----------+----------+
| id | sponsorID | open     | close    |
+----+-----------+----------+----------+
|  5 |        24 | 9:00 AM  | 11:30 PM |
|  7 |        99 | 6:00 AM  | 9:30 PM  |
|  6 |        25 | 6:00 AM  | 9:30 PM  |
|  4 |        23 | 7:00 AM  | 2:30 PM  |
|  3 |        21 | 11:00 AM | 5:30 PM  |
|  2 |        23 | 6:00 AM  | 8:30 PM  |
|  1 |        23 | 6:00 AM  | 10:30 PM |
+----+-----------+----------+----------+

or

mysql> select * from accounts order by id=5 desc, id=1 desc, id desc;
+----+-----------+----------+----------+
| id | sponsorID | open     | close    |
+----+-----------+----------+----------+
|  5 |        24 | 9:00 AM  | 11:30 PM |
|  1 |        23 | 6:00 AM  | 10:30 PM |
|  7 |        99 | 6:00 AM  | 9:30 PM  |
|  6 |        25 | 6:00 AM  | 9:30 PM  |
|  4 |        23 | 7:00 AM  | 2:30 PM  |
|  3 |        21 | 11:00 AM | 5:30 PM  |
|  2 |        23 | 6:00 AM  | 8:30 PM  |
+----+-----------+----------+----------+