MySQL variant for PHP’s in_array

So you have an array, let’s call it $array.

$array = array('one', 'two', 'three');

And you want to do a MySQL query that will check if one of the values in the array are present or similar or whatever. In PHP it would look like this:

if (in_array('one', $array))

and it would return true. Now to do this in MySQL we can use (along other things but I use this one) the IN statement.

mysql_query('select something from table where one IN ("' . implode('","', $array) . '")');

Why is this useful? Let’s say you’ve got an array of file extensions (pictures) and you have a database with some files in it, pictures, music, videos. You want to display only the pictures, because you want to display the last 15 uploaded pictures. Let’s say you have the filename and extension (filename = hack.jpg) stored in your database. Let’s see an example of what you could do…

mysql_query('select id, name, filename from uploads where substr(filename, -3) IN ("' . implode('","', $pics) . '") order by id desc limit 12');

And that’s it. Easy peasy!

Have swing!

If you liked this post think about subscribing to my RSS feed and prevent missing anything interesting. It's free, fast and doesn't hurt. Promise. Click here.
Related posts: