mysql selecting multiple rows that match array of ids
arrays, mysql, php, sql
Solution
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})";
....
Problem
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 :)