php mysqli WHERE IN (?,?,? ...)

Question

According to http://us2.php.net/manual/en/mysqli-stmt.bind-param.php, the different types are:

i   corresponding variable has type integer
d   corresponding variable has type double
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets

However, how can you handle this:

->prepare("SELECT blabla FROM foo WHERE id IN (?)")

Where ? would be a list of ids. There could be one or more items:

$ids = "3,4,78";
->bind_param('s',$ids);

Is that possible?

I'd like to use prepared statements because it will be executed in a loop.

See http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — beldaz, Dec 06 '10 at 05:08

score 3 · Answer 1 · answered Oct 07 '09 at 21:41

3

The proper syntax would be

->prepare("SELECT blabla FROM foo WHERE id IN (?, ?, ?)")

for 3 items in that array, for example. You would then have to bind each of those items individually using bind_param().

If you don't have a size guarantee on the array, you'll have to write a couple helper functions to generate your SQL with the proper number of "?"'s and bind statements.

answered Oct 07 '09 at 21:41

Marc W

19,083
4
59
71

2

Indeed I do not know the size in advance. If I re-write the prepared statement for each iteration of the loop (to change the number of ?s), then it defeats the purpose of prepared statements. – Nathan H Oct 07 '09 at 22:03
I'm not quite sure what you want to accomplish in the loop. Will the values bound to the statement be changing each time? Are the _number_ of values bound changing? Are you executing an identical statement multiple times (don't know why you'd do this)? – Marc W Oct 07 '09 at 22:08
the number of values IN(?,?,?) change each loop iteration sometimes it could be (1,2,3,4), or (1,2,3,4,5,6,7,8,9) ... – Nathan H Oct 07 '09 at 22:12
@nute. It may slightly defeat the speed advantage, but it still has the advantage of being safe from from malicious sql injection. You can always gain back the speed advantage of pre-preparing one statement per length-of-in clause. – Cheekysoft Oct 29 '09 at 17:33

score 3 · Answer 2 · answered Oct 08 '09 at 07:15

If you have a list of variables that differs in size every call that you wanto to bind to an IN-statement, the most simple way would be to generate the SQL string programatically and use a loop to bind the variables:

/**
 * @param  array  $values
 * @param  mysqli $db
 * @return mysqli_stmt
 */
function bindInValues(array $values, mysqli $db)
{
    $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)',
        implode(', ', array_fill(0, count($values), '?'))
    );
    $stmt = $db->prepare($sql);
    foreach ($values as $value) {
        $stmt->bind_param('s', $value);
    }
    return $stmt;
}

If you like call_user_func_array you can use dynamic method invocation and go along without a loop.

/**
 * @param  array  $values
 * @param  mysqli $db
 * @return mysqli_stmt
 */
function bindInValues(array $values, mysqli $db)
{
    $sql = sprintf('SELECT blabla FROM foo WHERE id IN (%s)',
        implode(', ', array_fill(0, count($values), '?'))
    );
    $stmt = $db->prepare($sql);
    array_unshift($values, implode('', array_fill(0, count($values), 's')));
    call_user_func_array(array($stmt, 'bind_param'), $values);
    return $stmt;
}

this of course means the use of Prepared Statements isn't really efficient anymore, since you're "preparing" at every iteration, instead of just once. Because I mean to send that SELECT statement many times in a row, with different number of params every time. — Nathan H, Oct 08 '09 at 21:14
You're right - that's the inevitable consequence... But there is no other way around. — Stefan Gehrig, Oct 08 '09 at 21:20
If you're sending a different query (related to the SQL structure not the values) every time, you can skip the preparation step and directly build the SQL including the `IN` values. — Stefan Gehrig, Oct 08 '09 at 21:22

score -1 · Answer 3 · answered Jan 10 '13 at 11:23

-1

What about doing this:

$sql = sprintf("SELECT blabla FROM foo WHERE id IN(%s) ", $ids);
$stmt = $mysqli->prepare($sql);
if (!$stmt) {
    $this->throwException();
}

if (!$stmt->execute()) {
    $this->throwException();
}

If it is not good, please tell me why so I can learn from my errors. Thanks!

answered Jan 10 '13 at 11:23

kitimenpolku

2,604
4
36
51

Can someone explain me why is a -1 ? – kitimenpolku Feb 13 '13 at 08:06
I think it might be the same as the comments in this answer, http://stackoverflow.com/a/1534520/1253832 – Brad Moore Apr 08 '13 at 01:00

score -4 · Answer 4 · answered Oct 07 '09 at 22:09

-4

I think I found the answer to my question:

->prepare("SELECT stuff FROM table_name WHERE id IN (?)");

$itemList = implode(',',$items);
$children->bind_param('s',$itemList);

Seems to be working fine when using a string with coma-separated values. I'm still checking if the results are really accurate ...

answered Oct 07 '09 at 22:09

Nathan H

48,033
60
165
247

3

Hmm, that shouldn't be working. The prepared statement should wind up being `SELECT stuff FROM table_name WHERE id IN ('1, 2, 3, 4, 5')` in that situation, which shouldn't work as you expect... – ceejayoz Oct 07 '09 at 23:41
However it does seem to work fine so far ... If you've got a better idea, please let me know! – Nathan H Oct 08 '09 at 20:02

php mysqli WHERE IN (?,?,? ...)

4 Answers4

Linked