PHP - return mysql rows in same order as called

Question

I am trying to use a SELECT query to select 3 rows, but I want them in the same order I call for them. This is what I attempted to do:

$array = array("50", "23", "67");
$list = implode(",", $array);

foreach($db->query("SELECT * FROM champs WHERE id IN ($list) ORDER BY 
    DECODE(id, $array[0], 1, $array[1], 2, $array[2], 3)") as $row) {
    //Do stuff
}

however, I get:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

is there a way to ensure it is always returned in the order I call it?

Gordon Linoff · Accepted Answer · 2014-04-26 03:40:19Z

1

decode() is an Oracle function, not a MySQL function. You should use case, which works in both databases:

SELECT *
FROM champs
WHERE id IN ($list)
ORDER BY (CASE WHEN id = $array[0] THEN 1
               WHEN id = $array[1] THEN 2
               WHEN id = $array[2] THEN 3
          END);

answered Apr 26, 2014 at 3:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP - return mysql rows in same order as called

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related