Selecting data from an array [duplicate]

Question

If I have this array:

$ids = [1, 34, 36];

How do I get data from a table from each of ID using this query:

$query = $DB->Prepare('SELECT name, address FROM tContacts WHERE id = ?');

I tried using:

foreach ($ids AS $val) {
    $query = $DB->Prepare('SELECT name, address FROM tContacts WHERE id = ?');
    $rs = $DB->Execute($query, array($val));
    $i = 0;

    foreach($rs as $rows)
    {
        $rs[$i++]=array(
            'name'    => $rows['name'],
            'address' => $rows['address']
        );
    }
}

And when I tried to:

print_r($rs);

It only display the last row, not 3 rows as I intended.

Answer 1

You should use the IN function in the SQL condition. Combine it with a php function to repeat the placeholders N times where N is the amount of the requested IDs in the array.

$ids = array(1,434,23);

$query = "SELECT name, address FROM tContacts WHERE id IN('".join("','",$ids)."')";
$stmt = $DB->Prepare($query);

Originally i've used str_repeat, but @pokeybit came up with an idea to use join which is much more comfortable.

In case the $ids array is being built based on a user's input or so, it would be better to use the query prepare mechanism and use placeholders.

$count = count($ids);
$query = "SELECT name, address FROM tContacts WHERE id IN(".str_repeat('?,',$count-1)."?)"; //would output: IN(?,?,?)

Then, use a loop to bind the relevant IDs from the array.

Note 1: Both of the ideas work, one is in case the developer sets the array, the other one use the advantages of preparing queries and binding which is a better solution in case the array's in dynamic/input-based. Therefore, I've wrote both of the solutions.

Note 2: See @JaredFarrish last comment regarding the advantages of prepared queries.