11

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

Improve this question
1

3 Answers 3

Reset to default
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})";
....
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

What happens if array is empty? usersStr no ids? will it return all records? or will it fail?
my advice is to check before query, the imploding and sendinq query, if(!empty($users)) { ... }
because if the array is empty, why send an additional query? @salivan
0

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);
Improve this answer

Comments

0

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
    }
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.