Mysqli prepared statement : several WHERE clauses and WHERE IN (Array)

Question

I need to run the query below. It asks me a lot of trouble. In fact, I have several "WHERE" conditions, one that requires the decomposition of an Array.

This issue helped me but it doesn't have several conditions "WHERE" .

$array = (1,2,3,4,5,6,7,8,9,10);

$clause = implode(',', array_fill(0, count($array), '?'));

if($request = $this->getConnexion()->prepare('SELECT col1, col2 FROM table WHERE col1 IN ('.$clause.') AND col2>=?') or die(mysqli_error($this->getConnexion()))) {

    // The problem starts here
    call_user_func_array(array($request, 'bind_param'), $array);

    $request->bind_param('i', $this->getTime());
    // Until here

    $request->execute();
    $request->bind_result($col1, $col2);
    $request->store_result();

    // Following the code

}

You could add your `$this->getTime()` to the end of your array, before using `call_user_func_array`, or build another `array_fill` using `i` and then implode in the `bind_param` — Sean, Jan 18 '15 at 17:08
What is your issue? It looks like instead of an additional call to `bind_param()`, you should add one more `?` onto the `$clause` array, and add the value of `$this->getTime()` onto the `$array` array of values, so you are just dealing with one array of params covering both the `IN ()` clause and the other condition. — Michael Berkowski, Jan 18 '15 at 17:09
@Michael Berkowski , please post your answer, I don't understand what you are thinking about :) — w3spi, Jan 18 '15 at 17:16

Michael Berkowski · Accepted Answer · 2015-01-18T17:56:17.637

The important thing here is that you are calling bind_param() just once, with an array containing all of the parameters you'll need to bind, so your solution will be to just add the additional WHERE clause parameter onto your $array of values to bind. The IN() clause isn't a special case requiring call_user_func_array() separated from other parameters. You call it on all of them.

Something is missing though - bind_param()'s first parameter is a string of data types. All your types are i, so you'll need to use str_repeat() to create that.

// Eventually, this array will contain the other params too
$array = (1,2,3,4,5,6,7,8,9,10);

// This creates a string of ?,?,?,?... for the IN () clause    
$clause = implode(',', array_fill(0, count($array), '?'));

// Add the additional value onto the $array array
// so the last param is bound with the others.
$array[] = $this->getTime();

$types = str_repeat('i', count($array));

// The params passed to call_user_func_array() must have as references, each subsequent value. Add those in a loop, but start with the $types string
$params = array($types);
foreach ($array as $key => $value) {
   $params[] = &$array[$key];
}

if($request = $this->getConnexion()->prepare('SELECT col1, col2 FROM table WHERE col1 IN ('.$clause.') AND col2>=?') or die(mysqli_error($this->getConnexion()))) {

    // Then bind_param() is called on all parameters
    // using the $params array which includes types and param references
    call_user_func_array(array($request, 'bind_param'), $params);

    // Execute & fetch.
    $request->execute();
    $request->bind_result($col1, $col2);
    $request->store_result();

    // Following the code
}

What stupidity, I hadn't thought about that at all! Thank you. I tested and I validate your answer — w3spi, Jan 18 '15 at 17:29
@zlen Not stupidity. MySQLi in conjunction with `call_user_func_array()` is really complicated. I can never remember how to do it without looking it up, and examples are not often well commented to understand what is actually being done. — Michael Berkowski, Jan 18 '15 at 17:39
I have now this error : "Parameter 2 to mysqli_stmt::bind_param() expected to be a reference," I found the issue above, but this don't answer me : http://stackoverflow.com/questions/2045875/pass-by-reference-problem-with-php-5-3-1 — w3spi, Jan 18 '15 at 17:41
@zlen Okay, that should be sufficient. There was a large portion I overlooked, actually creating the array of references — Michael Berkowski, Jan 18 '15 at 17:56
Indeed, before you edit your post, I understood that the problem came from the reference, so the 'i' number. You explanation is very good. I test that — w3spi, Jan 18 '15 at 18:03

Mysqli prepared statement : several WHERE clauses and WHERE IN (Array)

1 Answers1