CODEWITHSUNDEEP
phpmysql
JasonDavis
JasonDavis

Reputation: 48933

Get MySQL results based on order of ID's in a variable

I have a PHP variable that holds a JSON string like this below, each number is the id from the module_categoryMySQL table

$json_order = '["8","2","7","3","1","6","4","5"]';

So using the $json_order I need to iterate the table below, but return the results in the Order that $json_order is in.

SELECT id, name
FROM  `module_category`

Can anyone show me a good way of doing this?

Upvotes: 2

Views: 206

Answers (6)

dfsq
dfsq

Reputation: 193261

You should use FIELD as follows:

$order = trim('["8","2","7","3","1","6","4","5"]', '[]');

$query = "SELECT id, name FROM  `module_category`
          ORDER BY IF (FIELD(id, $order), 0, id), FIELD(id, $order)";

Note, that it's important to use IF in this case otherwise FIELD will return 0 for all the results which are not in the list making them appear in the top. We just invert this behaviour: now for each row whose id is within the list we return 0 - this makes these rows topmost, then they are sorted by id.

Upvotes: 2

Nelson Benítez León
Nelson Benítez León

Reputation: 50563

You can use FIELD() function, which is used for custom ordering, just what you need, you can use it like this:

//Convert $json_order into '8','2','7','3','1','6','4','5' to insert it in sql
$order = str_replace(array('"','[',']'), array("'",'',''), $json_order);
$sql = 'SELECT id, name
FROM  `module_category` ORDER BY FIELD(id, '.$order.' )';

UPDATE: Sorry there was an error in my above code, it's now corrected :-)

Upvotes: 4

Suyash
Suyash

Reputation: 625

$json_order = '["8","2","7","3","1","6","4","5"]';
$a=json_decode($json_order,"true");
$sql="SELECT id, name
FROM  `module_category`
ORDER BY FIELD (id,".implode(",",$a).")";

So basically you take the JSON, convert it into an array and then implode it to be comma separated. Then using the Field operation of SQl you can fetch everything in order.

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Take the list of IDs and sculpt the following

SELECT id,name FROM `module_category` ORDER BY
FIELD(id,8,2,7,3,1,6,4,5);

The FIELD function returns the index position of each value. You use that to order by.

Upvotes: 2

Rohan Kumar
Rohan Kumar

Reputation: 40639

Try this:

$sql=SELECT id, name FROM  `module_category`
 ORDER BY FIELD(id, 8,2,7,3,1,6,4,5), json_order;

Read http://www.electrictoolbox.com/mysql-order-specific-field-values/ and http://lists.mysql.com/mysql/209784

Upvotes: 1

vonUbisch
vonUbisch

Reputation: 1469

Here's my solution;

        $json_order = '["8","2","7","3","1","6","4","5"]';
        $decoded = json_decode($json_order);
        echo 'SELECT id, name FROM `table` WHERE `id` IN ('.implode(',', $decoded).') ORDER BY FIELD(id,'.implode(',', $decoded).');';

Upvotes: 1

Related Questions