Asked  8 Months ago    Answers:  5   Viewed   33 times

I am trying to pass an array to $stmt->bind_param for as an IN variable. How can I do this?

$values = array('a','b','c','d');
$values = '"' . implode('","', $values) . '"';

$stmt->prepare('SELECT value1, value2 FROM table1 WHERE value3 IN (?)');
$stmt->bind_param('s', $values);

I can't get it to work for the life of me. Any thoughts? The above code is just a sample.

 Answers

61

This is a scenario where doing it this way is inappropriate. You're constructing actual SQL (that's what the commas and quotes are), and passing it in as a parameter. It's basically evaluating to value3 IN ('...') where ... is the entirety of $values.

Also that's a good call about the quotes. MySQL uses single quotes.

You'll need to either build the SQL using string concatenation alone, or use more than one parameter.

EDIT

As an example:

$values = array('a','b','c','d');
$values = "'" . implode("','", $values) . "'";
$stmt->prepare('SELECT value1, value2 FROM table1 WHERE value3 IN (' . $values . ')');
Wednesday, March 31, 2021
 
lena
answered 8 Months ago
15

call_user_func_array "Call a callback with an array of parameters"

call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), $params));

should do the job

UPDATE: you have also to change your params array:

$params = array(&$firstName, &$lastName, &$address, &$postcode, &$email, &$password);

as mysqli_stmt::bind_param expects the second and the following parameters by reference.


EDIT: Your query seems to be wrong. Maybe you have less fields than you have variables there. Do:

"INSERT INTO Users (field1, field2, field3, field4, field5, field6) VALUES (?, ?, ?, ?, ?, ?)"

where you replace the name of the fields by the correct names

Wednesday, March 31, 2021
 
BradM
answered 8 Months ago
66

I wouldn't know why you have to use call_user_func_array, but that's another story.

The only thing that could be wrong in my eyes is that you are using a reference to the object. Assuming you're using PHP 5.*, that is not necessary:

call_user_func_array(array($stmt, 'bind_param'), $params);
Wednesday, March 31, 2021
 
wavyGravy
answered 8 Months ago
55

In my case, as shown on the update part of the question, I think arrays have better performance than mysql databases.

Array usage showed 10 times faster response even when I search through the cells to find desired values in a row. Even good indexing of the table couldn't beat the array functionality and speed.

Saturday, May 29, 2021
 
Hat
answered 5 Months ago
Hat
23

UPDATE

This answer is outdated. Please use the spread operator in newer PHP versions like answered by Stacky.

From php docu:

Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.

And on the page mysqli-stmt.bind-param you have different solutions:

For example:

call_user_func_array(array($stmt, 'bind_param'), refValues($params));

function refValues($arr){
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}
Tuesday, June 1, 2021
 
lechup
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share