Put desired row id on top

Question

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.

So the solution can't be done with sql? Using PHP so, but how? — Hypister, Sep 15 '16 at 14:37
@aynber Thanks, I was searching for some question according to it but found nothing. — Hypister, Sep 15 '16 at 14:39
`SELECT * FROM products WHERE id_category = :id_category order by id=5 desc` ?? — Professor Abronsius, Sep 15 '16 at 14:39

score 1 · Answer 1 · answered Sep 15 '16 at 14:40

1

Use a UNION of 2 queries to achieve this. Let me rewrite just the query here:

SELECT * FROM products WHERE id_category = :id_category WHERE id=5
UNION
SELECT * FROM products WHERE id_category = :id_category WHERE id!=5

answered Sep 15 '16 at 14:40

Mohamed Yasin

440
4
10

score 1 · Answer 2 · answered Sep 15 '16 at 14:44

I would populate a new array and use array_unshift();

$rowHolder = array();
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
    if ($row['id_category'] !== 5) {
        $rowHolder[] = $row;
    } else {
        array_unshift($rowHolder, $row);
    }
}

$rowHolder now holds all the rows, with the row having id = 5 at the zero index.

Professor Abronsius · Accepted Answer · 2016-09-15T14:50:39.313

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  |
+----+-----------+----------+----------+

score 1 · Answer 4 · answered Sep 15 '16 at 14:49

It's trivial in MySQL:

SELECT ...
...
ORDER BY (id_category = 5) DESC, id_category

This works by taking the result of (id_category = 5), which is a boolean true/false, and casting it to an integer 0/1 and sorting them in DESC order. That places all of the 5 records at the TOP of the result set, then the remaining non-5 records are sorted as usual in the second place id_category clause.

score 0 · Answer 5 · answered Sep 15 '16 at 14:42

0

Try this:

$sql = "SELECT *,(id_category=5) as top FROM products WHERE id_category = :id_category ORDER BY top DESC";
$query = $conn->prepare($sql);
$query->execute(array
(
    'id_category' => $id_category
));

answered Sep 15 '16 at 14:42

Dmytrechko

598
3
11

Put desired row id on top

5 Answers5