Bindparam with mysql IN operator

PHP
1

here’s my code:

$itemandsize_ids = '1,2,3'; try { $sql = 'SELECT id, sub_category, itemandsize FROM itemandsize WHERE id IN (:itemandsize_ids)'; $stmt2 = $db2->prepare($sql); } catch (PDOException $e) { echo $e->getMessage() . '
'; }

try {
$stmt2->execute(array(
‘:itemandsize_ids’ => $itemandsize_ids));
}
catch (PDOException $e) {
echo $e->getMessage() . ‘
’;
}

How do I need to rethink this?

thanks always,

niche

2

Like this:-

1 Like
3

So, no bindparam for the IN operator.

Correct?

4

The last example in that section shows a way of doing it with named placeholders, so I guess you could follow something along those lines if you wanted to use bindparam.
But the main point being made is that each value for the IN is an individual parameter itself, you can’t just bundle them all together in a single param. How you best achieve that is up to you.

5

I get this part from your link:

$ids = [1,2,3];
$in = "";
foreach ($ids as $i => $item)
{
    $key = ":id".$i;
    $in .= "$key,";
    $in_params[$key] = $item; // collecting values into key-value array
}

$in = rtrim($in,","); // :id0,:id1,:id2

How do I get the above into something like this:

$sql = "SELECT * FROM table WHERE id IN (??????????);
$stm = $db->prepare($sql);
$stm->execute(?????????));  // just would like to focus on bindparam for the IN operator
6

There is a thin PDO wrapper called the Doctrine Database Access Layer which has implemented this for you. Worth taking a look: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

7

Something like:-

$sql = "SELECT * FROM table WHERE id IN ($in);
$stm = $db->prepare($sql);
$stm->execute($in_params));

Though it’s probably simpler without naming the params.

…or take a look at the wrapper.

8

got this to work with your help:

$sql = 'SELECT * FROM table WHERE id IN (' . $in . ')'; $stm = $db->prepare($sql); $stm->execute($in_params));

I have a few questions before getting to the wrapper

why did i need to concat the $in in my query?

why not execute $in_params as an array?

9

Would be interesting to see how you got it to work with that extra parentheses at the end there.

I thought the parametized execution has to be in an associative array for it to bind correctly? Or am I wrong? Unless that’s just the part you’re leaving out, but you’re using associative arrays.

10

That’s a typo. Please excuse me.

$in_params is an assoc array:
array(3) { [“:id0”]=> string(2) “18” [“:id1”]=> string(2) “19” [“:id2”]=> string(2) “42” }

$stm->execute($in_params); //works

$stm->execute(array($in_params)); // throws this error:

Notice: Array to string conversion in …on line 99
SQLSTATE[HY093]: Invalid parameter number

11

if I understand the context, not necessarily. I prefer named placeholders myself so I could be wrong, but AFAIK if not using named placeholders the order has to be exact.

For example, I may now know that “?,?,?” is for “id, name, date” but will I later? If I have “:id, :name, :date” I won’t need to rely on my memory and risk making a mistake by feeding it “id, date, name”

1 Like
12

The second one fails because you are wrapping the associative array’s variable inside another array. Arrays require a string to be passed as an argument. However, since you are wrapping an array within a array, you will get the array to string conversion error since arrays can’t be turned into strings that way. I know you aren’t trying to do that so don’t wrap an array within an array if the variable already contains an array.

Ah. That is quite interesting. I prefer parametized placeholders because you know what you are binding. It is also good practice to understand what you are binding.

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.