Mysql select order results by parameter


$sql = "CREATE TABLE $playlist_table (             `id` int(11) unsigned NOT NULL AUTO_INCREMENT,             `title` varchar(100) NOT NULL,             `options` text DEFAULT NULL,             PRIMARY KEY (`id`)         ) $charset_collate;";         dbDelta( $sql );   $playlist_id = array(3) { [0]=> string(2) "13" [1]=> string(2) "11" [2]=> string(2) "25" }  $wpdb->get_results($wpdb->prepare("SELECT id, title, options FROM {$playlist_table} WHERE id IN (%d,%d,%d)", $playlist_id), ARRAY_A); 

I get results returned:

playlist id 11, 13, 25

How can I have them returned in the order I specified? (13,11,25)

Add Comment
1 Answer(s)

As a starter: your query has no order by clause, so the ordering of the result is actually undefined. Whatever ordering you currently see is not guaranteed to be consistent over consecutive executions of the query.

So you do need an order by clause. In MySQL, you can conveniently use string function field() for this. This requires passing the list of values twice:

SELECT id, title, options  FROM {$playlist_table}  WHERE id IN (%d, %d, %d)  ORDER BY FIELD(id, %d, %d, %d) 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.