Binding an array in MySQLi prepared Insert statement PHP

Question

I tried multiple ways to create a function to bind dynamic array values into the MySQLi prepared statements. But I am getting error 'Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement'

Here is my code:

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

    foreach ($values as $index => $current_val){ // build type string and parameters
        $value .= '$values['.$index.'],';
    }
    $value = rtrim($value,',');

    $statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
    $stmt = $db->prepare($statement);
    $stmt->bind_param("sss", $value);

    $stmt->execute();
    $stmt->close();
    echo "inserted";
}

The same code works when I replace

$stmt->bind_param("sss", $value);

with

$stmt->bind_param("sss",$values[0],$values[1],$values[2]);
        

Answer 1

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.