MySQLi Bind Param With an Array For In

MySQLi Bind Param with an array for IN

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 . ')');

PHP bind_param with arrays

If your PHP is not outdated (>= 5.6 namely), just add three dots to the first example,

$stmt->bind_param('iss', ...array(101, 'SomeString 1', 'Some string 2'));

Bind Param with array of parameters

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

mysqli's bind_param using an array

Can you try this, to send reference to values instead of real values:

    $params = array_merge(array($sql_types), $data);
    foreach( $params as $key => $value ) {
        $params[$key] = &$params[$key];
    }
    call_user_func_array(array($stmt, "bind_param"), $params);

Binding an array in MySQLi prepared Insert statement PHP

You seem to be binding a single string as a second argument in your bind_param(). This method takes a number of variables by reference and binds them to the placeholders in the query and since you bound a single string the number of bound parameters does not match.

You need to store the values in an array and then unpack them using the splat operator.

if (count($fields) == count($values)) {
    $fielddata = implode(", ", $fields);
    $questions = rtrim(str_repeat("?, ", count($values)), ", ");

    $statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
    $stmt = $db->prepare($statement);
    $stmt->bind_param(str_repeat("s", count($values)), ...$values);
    $stmt->execute();
}

Also, the type should be a list of letters denoting the type of each variable being bound. The best case is to bind them all as strings, so just repeat s for each bound variable.

Take care of SQL injection. You need to make sure that the field names are properly whitelisted. If these can be arbitrary values you could be vulnerable to SQL injection.

How to pass a list of parameters contained in an array to bind_param?

Using PHP 5.6, you can do this easily with the help of the unpacking Operator (...$var) and use get_result() instead of bind_result().

$stmt->bind_param($types, ...$list);
$stmt->get_result();

---

Related Topics