Can you pass multiple params using OR to an SQL/PHP single bind statement?

Question

I have a search bar that passes data to the server. I am taking the sentence sent and breaking it into individual words.

I am then comparing a column against each word in the sentence.

$term = filter_var($input['term'], FILTER_SANITIZE_STRING);
$terms = explode(" ", $term);
$size = sizeof($terms);

$posts = DB::select('SELECT * FROM cars
WHERE color = ?',
$terms[0] || $terms[1] || $terms[2] || $terms[3] || $terms[4] );

What is the proper way to bind with multiple parameters on one bind?

This way would get messy, as I would want to search additional columns.

for ($i=0; $i < $size ; $i++) {
    $posts = DB::select('SELECT * FROM cars
        WHERE color = ? AND
        WHERE model =?',
    $terms[$i], $terms[$i],);
}

Yes, but you only need ONE `WHERE` i.e. `WHERE color = ? AND model =?` — RiggsFolly, Mar 22 '18 at 16:52
A sneaky peak at the SQL manual or a few tutorials would seem like what you should do over the next few hours — RiggsFolly, Mar 22 '18 at 16:54

Bill Karwin · Accepted Answer · 2018-03-22T17:18:20.640

What is the proper way to bind with multiple parameters on one bind.

Think of this rule: You can use a parameter in an SQL query in place of one single scalar value.

That is, where you would normally use in your SQL statement one numeric constant, one quoted string constant, or one quoted date constant, you can replace that one query element with one parameter.

Parameters can not be used in place of:

Lists of multiple values
SQL expressions
SQL keywords
Identifiers like table names, column names, or database names

If you want to compare your color column to multiple values, you need multiple parameter placeholders.

$posts = DB::select('SELECT * FROM cars
WHERE color IN (?, ?, ?, ?)');

It doesn't work to pass a string containing a comma-separated list of values to a single placeholder. You end up with a query that works as if you had written it this way:

SELECT * FROM cars WHERE color IN ('12,34,56,78');

This query will run without error, but it won't give you want you want. In a numeric context, the string '12,34,56,78' has a numeric value of 12. It ignores all the rest of the characters in the string after the first non-numeric character ,. So it will succeed in searching for color 12, but it will fail to find the other colors.

PDO makes it easy to deal with lists of values, because when it is time to supply the values for a parameterized query, you can simply pass an array to the PDOStatement::execute() function.

If you don't know how many color values you need to search for, you can use PHP builtin functions to make a list of question mark placeholders that is the same length as your array of color values:

$list_of_question_marks = implode(',', array_fill(1, count($color_values), '?'));
$sql = "SELECT * FROM cars WHERE color IN ($list_of_question_marks)" 
$stmt = $pdo->prepare($sql);
$stmt->execute($color_values);

@seamus And for building and binding dynamically using strictly `mysqli` (not pdo), you would use `call_user_func_array`. PDO does make it easier with `execute` ... but its not required if you still wish to stick with mysqli (server reasons, or otherwise). — IncredibleHat, Mar 22 '18 at 17:24
@IncredibleHat There was a time when people used mysqli because PDO was not yet a part of PHP distributions by default. **That was 2005.** It's time to treat PDO as a normal part of the PHP environment. — Bill Karwin, Mar 22 '18 at 17:48
Well, given its 2018... and I still run across new servers that have PDO disabled... I thought it pertinent to mention there are still ways to do things in mysqli ;) It wasn't a bash on your answer at all. — IncredibleHat, Mar 22 '18 at 17:50
It's true, depending on your Linux distro, PDO is a separate yum/apt package. But so is mysqli. And many other common PHP extensions. — Bill Karwin, Mar 22 '18 at 17:53

score -1 · Answer 2 · answered Mar 22 '18 at 16:56

-1

You should use In to search between various items, and if it's a search, a OR operator would work better:

    $posts = DB::select('SELECT * FROM cars
    WHERE color in (?) or
    model in (?)',
    implode(',', $terms), implode(',', $terms));

answered Mar 22 '18 at 16:56

Dionei Miodutzki

657
7
16

I'm unsure how this would work (see Bill's answer). For example passing the $terms of "look" "for" "this", will turn into a string of "look,for,this" (the implode). Then that is going to be prepared and bound as a *single string* into the single `?`. It will not be a comma separated list the `IN` needs as: `IN ('look','for','this')`. It will instead be `IN ('look,for,this')`. Nope. – IncredibleHat Mar 22 '18 at 17:38
that's right, there's a better (and right) answer for the question. – Dionei Miodutzki Mar 22 '18 at 17:40
2

yup.. i would delete it but i'm unable as it's the choosen answer – Dionei Miodutzki Mar 22 '18 at 17:41

Can you pass multiple params using OR to an SQL/PHP single bind statement?

2 Answers2