CODEWITHSUNDEEP
phpmysqlsqlbindparam
seamus
seamus

Reputation: 2901

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

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],);
}

Upvotes: 2

Views: 1556

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562631

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

Upvotes: 5

Dionei Miodutzki
Dionei Miodutzki

Reputation: 657

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

Upvotes: -1

Related Questions