MySQL: filtering results from a list of id's

A short while ago I posted about testing if a variable exists in a list and I spoke about the MySQL “IN” keyword.

I realised this week that not everyone knows about the MySQL “IN” keyword, so I’ve decided to explain this very handy SQL keyword.

Lets say you have a user table with the following fields; user_id, username, password. You have a list of user ids (eg 1, 3, 5) and you want to draw the usernames and passwords for these users.

You could use the following SQL code:

SELECT `username`, `password` FROM `user`
WHERE `user_id` = 1 AND WHERE `user_id` = 3 AND WHERE `user_id` = 5;

Now while this is correct and will return the required results, it is quite a bit to code (especially if you have a larger list of user ids). Here comes the “IN” keyword to save us.

SELECT `username`, `password` FROM `user` WHERE `user_id` IN (1, 3, 5);

See how easy that is. Now if you have a much larger list (or the list grows) you simply add the user ids to the comma delimited list. Less time spent, and the same result. I’m all about the shortcuts….

Life of a developer

It has been a while since I have posted here, mainly due to the time consuming period that is the festive season and also because I am discovering that I am getting fairly annoyed by Drupal as a blog tool and am trying out the new WordPress as an alternative.

For now I have a small post on something that many people aren’t aware of but is VERY handy, the ternary operator.

Basically it is a shortcut to do a simple if-then-else statement in one line when you are performing fairly simple checks for a single value. I tend to use it a lot when checking for GET or POST variables.

So:

$getContents = (isset($_GET) ? $_GET : array());

would check if the $_GET variable array is set, if it is return its contents to $getContents and if not return and empty array to $getContents.

Much easier that coding:

$getContents = array();
if (isset($_GET)){
$getContents = $_GET;
}

P.S. Just on a side note, if you are new to PHP and want to use this code to check for and receive your $_GET contents, dont forget to sanatize your data as well (hmm I think I see a new post topic)