mysql selecting multiple rows that match array of ids

First of all:

SELECT `key`, `value` FROM settings
 WHERE `key` = :base_url
    OR `key` = :google_analytics
    OR `key` = :site_domain

Is this correct way of selecting multiple rows? Or is there a better way? Because it is not really OR, but all of them… So it just doesn’t sound right.

Question number 2:

This is my query that selects users with pagination:

SELECT id, email, verified, level, name_surname, age, sex, profession, education, location, privacy, suspend FROM users LIMIT :start, :results

Sometimes I want to pass array of users to return to this query, like array(1,2,3,4,5).

Obviously simply adding WHERE $array doesn’t work. So how would I do that?

Sometimes I don’t want to pass this array of ids, so how do I switch between state when there is id list and when there is not? Do I simply create php if else on prepare statement? That seems obvious, but it is better to ask about best practices 🙂

Answer

To obtain multiple values from the where clause, there is no need to use OR. Use in, like in the query below:

SELECT `key`, `value` FROM settings
 WHERE `key` in ( :base_url, :google_analytics, :site_domain);

This is the same as the first:

SELECT * FROM users where id in (1,2,3,4,5);

So if you have an array of user ids, you must implode it with the ',' glue characters.

Here’s a complete example with php:

<?php
$users = array(1,2,3,4,5);
$usersStr = implode(',', $users); // returns 1,2,3,4,5
$sql = "SELECT * FROM users where id in ({$userStr})";
....