CODEWITHSUNDEEP
phpmysqlsql
VolkaRacho
VolkaRacho

Reputation: 301

Sort SQL output by the order of added WHERE conditions

How can I sort values from SQL output by an external condition or e.g. the order of added WHERE conditions?

First I generate multiple ID's with a "json_decode foreach loop"

$itemid = '';
$arr = json_decode($string,true);
foreach($arr['feed']['entry'] as $val)
{
$itemid .= " OR `item_id` = ".$val['id']['att']['ix:id'];
}

The output is e.g. (simplified)

`item_id` = 2
OR `item_id` = 3
OR `item_id` = 1

Here the order of the output is important for me. 2 is the ID of the first position, 3 of the second position and so on.

In the next step I use all IDs in a MySQL statement to pull information for each IDs 

$sql = "
SELECT *
FROM `itemtable`
WHERE $itemid
";

Which is more readable:

$sql = "
SELECT *
FROM `itemtable`
WHERE `item_id` = 2
OR `item_id` = 3
OR `item_id` = 1
";

The SQL output is now in the order of the IDs ASC 

+-------+-----------+
|   ID  |   INFO    |
+-------+-----------+
|   1   | something |
+-------+-----------+
|   2   | something |
+-------+-----------+
|   3   | something |
+-------+-----------+

But I need the Info in the order how the IDs where generated by the json_decode:

+-------+-----------+
|   ID  |   INFO    |
+-------+-----------+
|   2   | something |
+-------+-----------+
|   3   | something |
+-------+-----------+
|   1   | something |
+-------+-----------+

I am outputing the received data with another foreach loop in the same php script:

$output = '';
foreach ($xpdo->query($sql) as $row) {
        $output .=  $row['ID']
            .$row['INFO']
        ."</br>";
}
return $output;

How can I get the output into the order of the json_decode? Actually I dont mind weather the sorting happens in the SQL statement or in PHP.

EDIT : SOLUTION from Fluffeh

Modified the json_decode to + adding an IF condition for the first entry to remove the "union all" here.

$itemid = '';
$arr = json_decode($string,true);
foreach($arr['feed']['entry'] as $val)
{
$itemid .= "union all 
SELECT * FROM `itemtable` 
WHERE `item_id` = ".$val['id']['att']['ix:id'];
}

Upvotes: 0

Views: 129

Answers (1)

Fluffeh
Fluffeh

Reputation: 33522

You technically can't, but if the query isn't TOO bad, you could write some code to do this:

$sql = "
SELECT *
    FROM `itemtable`
WHERE $itemid1
union all
SELECT *
    FROM `itemtable`
WHERE $itemid2
";

If you put some PHP to join the queries together it will work quite easily.

When you run union queries, the data will be returned in the order of the queries themselves.

Upvotes: 1

Related Questions