i'm in a situation where i might need to do such query :

select * from table where field1 IN (val1, val2, val3).

From the examples i've seen over the net, they're all showing
how to bind each '?' with a value only.

select * from table where field1 IN (?, ?, ?).

bind_param("iii", par1, par2, par3)

But what happens now is that I might not know the exact number of values that I'll need to put in field1, how should I do this?

Is it :

select * from table where field1 IN (?).

somewhere, a par1 is prepared :

$par1 has "101, 102, 103,104,105"

bind_param("i", $par1) ?

    I'm pretty sure that would not work, as it would end up as a single string parameter to IN(), e.g.:

    SELECT * FROM table WHERE field1 IN('101, 102, 103, 104, 105')

    Off-hand, I cannot think of an easy solution, other than preparing the query separately when you know the number of parameters.

      that's okay NogDog, i just think it's kinda weird as it seems to beat the purpose of having prepared statement (for this case) if I were to link the strings manually into an sql.

        For some reason, I have not come across a prepared statement interface that allows one to bind a list for use with the IN keyword. I suspect that it may have something to do with how the statement is compiled which could make it difficult, or inefficient, to allow such a binding. In any case, this is a case where you have to build the statement and escape the input with a PHP function instead of via the statement preparation.

          I suppose you could use REGEXP instead of IN(), perhaps?

          $stmt = mysqli_prepare('SELECT * FROM table WHERE field REGEXP ?');
$values = '101|102|103|104';  // could easily be an implode()'d array
$stmt->bind_param('s', $values);
            NogDog wrote:

            I suppose you could use REGEXP instead of IN(), perhaps?

            $stmt = mysqli_prepare('SELECT * FROM table WHERE field REGEXP ?');
$values = '101|102|103|104';  // could easily be an implode()'d array
$stmt->bind_param('s', $values);

            regular expressions? i'm never good at that 🙁

            do you mean to replace a certain string condition (REGEXP in this case)
            to become 'IN (101, 102,103,104)' ? but i fail to understand how it would fit in $stmt->bind_param('s', $values); ?

              do you mean to replace a certain string condition (REGEXP in this case)
              to become 'IN (101, 102,103,104)' ? but i fail to understand how it would fit in $stmt->bind_param('s', $values); ?

              No, the regex pattern '101|102|103|104' itself means '101 or 102 or 103 or 104'. However, it may be the case that this is less efficient than using IN.

                Yeah, I didn't claim it was a "good" way, just the only way I could think of achieving the desired effect via a bound parameter. Personally, I would just bite the bullet and either prepare the statement new each time it's needed, or else use a non-prepared query with non-bound (and escaped as necessary) parameters.

                  Write a Reply...