Executing array values in a MySQL PDO statement with WHERE and OR

Question

I've been trying to look for a solution where you can fetch database with one prepared statement and execute it with an array value

Typically I do this with my statement:

$search = $db->prepare("SELECT * FROM table WHERE name = ?");
$search->execute(array($name));

But what if i have an array like so:

Array ( 
  [0] => Array 
  ( 
    [name] => Burger Joint 
  ) 
  [1] => Array 
  ( 
    [name] => Burger Joint 
  ) 
  [2] => Array 
  ( 
    [name] => Burgers 
  ) 
  [3] => Array 
  ( 
   [name] => Meats 
  )
)

I'd like to somehow go through my database with either of the values in the array WHERE name=? in the statement. However, sometimes there's going to be multiple similar names, is there a way to condense the array before hand or what would be the best practice in a situation like this?

Thanks!

Did any of the answers here help you? If not, be so kind to leave a comment :) — Ja͢ck, Jun 18 '13 at 03:20
possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — TheConstructor, Feb 18 '14 at 20:52
Use `$flat = array_map(function($a) { return $a['name']; }, $array);` to get a flat array, afterwards proceed as in http://stackoverflow.com/a/920523/1266906 — TheConstructor, Feb 18 '14 at 20:58

score 0 · Answer 1 · answered Dec 03 '13 at 13:51

You can do this in a number of ways, but since you mentioned OR, let's use that:

First, your array of possible values. Let's take your array and mold it into an array of unique values:

$values_array = array_unique(
    array_map(
        function($element) { 
            return $element['name']; 
        },
        $original_array
    )
);
// $values_array now contains array('Burger Joint', 'Burgers', 'Meats')

Now, we build the prepared query by introducing as many placeholders as you have possible values:

$query = sprintf('SELECT * FROM table WHERE %s',
    implode(
        ' OR ',
        array_fill(
            'name = ?', 
            count($values_array)
        )
    )
);
// $query now contains 'SELECT * FROM table WHERE name = ? OR name = ? OR name = ?'

and execute it:

$search = $db->prepare($query);
$search->execute($values_array);

Alternatively, you could use IN instead, building your query like so:

$query = sprintf('SELECT * FROM table WHERE name in (%s)',
    implode(
        ', ',
        array_fill(
            '?', 
            count($values_array)
        )
    )
);
// $query now contains 'SELECT * FROM table WHERE name in (?, ?, ?)'

$search = $db->prepare($query);
$search->execute($values_array);

This will have the same effect, and it's slightly more clear what's going on by looking at the code.

@andrewliu You're right, why _are_ all the answers being downvoted? And they're still at it! But also, did you find an answer to your question? — Spiny Norman, Dec 06 '13 at 10:18

score -1 · Answer 2 · answered Feb 21 '13 at 07:08

-1

Try name IN instead of name = .

answered Feb 21 '13 at 07:08

chandimak

201
3
17

I was thinking more about it, and I think I was unclear. I need to use either/or of the values in the array, and not just use ALL values. Would IN work? – hellomello Feb 21 '13 at 07:11
Yes, it should work if you pass the value as an array. In one value's case array with one element. – chandimak Feb 21 '13 at 07:15
The `?` mark is used to avoid SQL injection. I haven't considered it here and so, this is not a complete answer. Check this [answer](http://stackoverflow.com/a/2373756/874000). You can prepare your array to avoid SQL injection using str_split, implode or using combination of such functions.(refer the comments of the answer as well) – chandimak Feb 21 '13 at 08:34

Your Common Sense · Answer 3 · 2013-02-21T09:00:02.570

-1

First, you need IN. field IN (1,2) is equal to field=1 OR field=2.
Next, you need some sort of helper function, to put all that mess of technical details of creating correct SQL statements away from application business code. To make it in ONE line, not 50.

$data = $db->getAll("SELECT * FROM table WHERE name IN (?a)",$names);

Finally, it seems you're getting your names from another query.
In this case you have to run only single query using JOIN. You may ask another question under [mysql] tag providing both your queries.

To get only names into array you have to use another helper function (though you have to create it yourself or get somewhere first):

$names = $db->getCol("here goes your query to get names");

edited Feb 21 '13 at 09:00

answered Feb 21 '13 at 07:31

Your Common Sense

156,878
40
214
345

I'm getting an array through another query correct, but its not from my database. I'm essentially doing this `$array[] = array("name"=>$names);` But will it matter if i have more than 1 value in array such like this: `$array[] = array("name"=>$names,"id"=>$id,"plural"=>$plural);` How do I only specify names? would it be `$names['name']` at the end of the getAll() function? – hellomello Feb 21 '13 at 07:48

Executing array values in a MySQL PDO statement with WHERE and OR

3 Answers3