0

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.

sianipard
  • 164
  • 2
  • 17

1 Answers1

3

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.

Ofir Baruch
  • 10,323
  • 2
  • 26
  • 39
  • That's invalid syntax in the `IN()`, nee the single quotes. – Jared Farrish Nov 05 '17 at 13:55
  • You need `'` outside the `".join."` bit – pokeybit Nov 05 '17 at 13:56
  • Also, this bypasses entirely preparing statements and condition value escaping, which can lead to invalid syntax for things other than integers and potentially security issues if values are not pre-escaped and come from untrusted sources. – Jared Farrish Nov 05 '17 at 13:58
  • @JaredFarrish he stated his array was `$ids = [1, 34, 36]` we have no other reason to suspect that array has not been validated – pokeybit Nov 05 '17 at 13:59
  • You're right, while it seems that the array is defined by the OP. I'll add the original answer as well (with repeated placeholders) – Ofir Baruch Nov 05 '17 at 13:59
  • 1
    @OfirBaruch it should be `IN('".join("','",$ids)."')` – pokeybit Nov 05 '17 at 14:02
  • This is a more appropriate way to do `IN()` clauses in prepared statements (see the second code block): https://stackoverflow.com/a/330280 – Jared Farrish Nov 05 '17 at 14:02
  • @JaredFarrish it's all the same as here.. they use `implode` which is 3 letters longer than `join` and does exactly the same thing – pokeybit Nov 05 '17 at 14:03
  • @pokeybit I don't see `bindParam()` used anywhere, and to the quotes issue, using the "OP only shows integers in an array", the quotes around the numbers don't matter. – Jared Farrish Nov 05 '17 at 14:05
  • @JaredFarrish Haha your initial post here was `That's invalid syntax in the IN(), nee the single quotes` – pokeybit Nov 05 '17 at 14:06
  • @pokeybit Yes, and removing the single quotes from being expressed is as appropriate as leaving them and wrapping the whole value list. This is what the answer I showed above does, it's not the same: https://3v4l.org/gCTK7 – Jared Farrish Nov 05 '17 at 14:08
  • This answer by @OfirBaruch is absolutely fine. Move along please – pokeybit Nov 05 '17 at 14:09
  • 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. If you have any further suggestion I would be glad to learn and to edit the answer. Thanks. – Ofir Baruch Nov 05 '17 at 14:09
  • There is also another benefit to using prepared queries and that's performance. If a query will be executed multiple times, using prepared statements where the query is parsed ahead of execution will be more efficient, especially if traversing a network with large `IN()` clauses. – Jared Farrish Nov 05 '17 at 14:18
  • Added a note regarding your comment which would be useful for the OP and others who would ran across this question in the future. – Ofir Baruch Nov 05 '17 at 14:26