mysql selecting multiple rows that match array of ids

Question

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 :)

You mean something like: http://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql Same would apply to strings — Ronald Swets, Dec 09 '13 at 14:20

score 43 · Accepted Answer · edited Sep 19 '19 at 13:57

43

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})";
....

edited Sep 19 '19 at 13:57

Kevin

16,549
8
60
74

answered Dec 09 '13 at 14:25

Vahe Shadunts

1,956
14
18

What happens if array is empty? usersStr no ids? will it return all records? or will it fail? – Dec 09 '13 at 14:37
my advice is to check before query, the imploding and sendinq query, `if(!empty($users)) { ... }` – Vahe Shadunts Dec 09 '13 at 15:08
1

because if the array is empty, why send an additional query? @salivan – Vahe Shadunts Dec 09 '13 at 15:09
true that. You answered all my questions, thanks! :) – Dec 09 '13 at 15:23
Thats perfect. Thank you. – Baran Jan 05 '18 at 08:40

score 0 · Answer 2 · edited May 23 '17 at 12:26

Question 1

I'm not sure that I understand what you mean by all of them, but if you use OR it means "any of these conditions can be true, even if multiple of them are true." This is different from the common English usage of "or," which would be exclusive.

If you want all conditions to be true simultaneously, use AND.

It is also simpler and more efficient to use IN rather than OR when comparing to multiple conditions at the same time as in your query.

Question 2

A prepared statement, `WHERE .. IN(..)` query and sorting — with MySQL

Essentially you should always have an array even if you pass in a scalar value:

$values = (array)$argument;
$conditions = implode(",", array_fill(0, count($values), "?");
// assuming you're using PDO
$stmt->execute($conditions);

score 0 · Answer 3 · answered Dec 09 '13 at 14:29

For question 1, I think @Ronald have already given you a link to refer to;

For question 2, I think you can do it in this way:

public function myquery($condition){
    if is_string($condition){
         //do something to compose the query for string
    }

    if is_array($condition){
         //do something to compose the query for array
    }
}

mysql selecting multiple rows that match array of ids

3 Answers3

Question 1

Question 2