current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite
1

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

share|improve this question
1  
You mean something like: stackoverflow.com/questions/3703180/… Same would apply to strings – Ronald Swets Dec 9 '13 at 14:20

3 Answers 3

up vote 15 down vote accepted

1. The multiply values in where clause, there is no need to use OR. use in, like in query blow.

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

2. 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 to implode it by ',' glues.

heres a whole 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})";
....
share|improve this answer
    
What happens if array is empty? usersStr no ids? will it return all records? or will it fail? – salivan Dec 9 '13 at 14:37
    
my advice is to check before query, the imploding and sendinq query, if(!empty($users)) { ... } – Vahe Shadunts Dec 9 '13 at 15:08
1  
because if the array is empty, why send an additional query? @salivan – Vahe Shadunts Dec 9 '13 at 15:09
    
true that. You answered all my questions, thanks! :) – salivan Dec 9 '13 at 15:23
up vote 0 down vote

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);
share|improve this answer
up vote 0 down vote

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
    }
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.