PHP Prepared Statement - Dynamic Vars Number of Element Error

Question

My previous question was closed because they said it was a duplicate but the duplicate posts did not answer my question. So here I go again and I put some additional comments in the edit section to state why the duplicate posts did not help me.

I am trying to construct a prepared statement dynamically and I keep getting the following error:

mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in

When I echo my statements the number of type definitions matches the bind variable so I don't know what is wrong. I think my code may be passing in strings, quotes or something instead of variables but I'm new to prepared statement and not sure how to check my query. When using simple mysqli_query I can echo the query and see were my error is at. I'm not sure how to do this with prepared statements so I'm hoping someone can help uncover my error.

I am trying to construct the prepares statement dynamically so that I can reuse the code as follows:

$db = mysqli_stmt_init($dbconnection);

// I have looped through my fields and constructed a string that when 
// echoed returns this:
// ?, ?, ?, ?, 
// I use sub str just to remove the last comma and space leaving me 
// with the string
// ?, ?, ?, ?. 
// Ive echoed this to the browser to make sure it is correct.

$preparedQs = substr($preparedQs, 0, -2);

 // I then loop through each field using their datatype and constructs
 // the type string as follows ssss. Ive echoed this to the browser to 
 // make sure it is correct.

$preparedType = 'ssss';

 // I then loop through my post array verifying and cleaning the data 
 // and then it constructing a string of clean values that results in
 // Mike, null, Smith, Sr., (First, Middle, Last, Suffix) I use substr 
 // again just to remove the last comma and space. Ive echoed this to 
 // the browser to make sure it is correct.

    $cleanstr = substr($cleanstr, 0, -2);

 // I then explode that string into a an array that I can loop through 
 // and assign/bind each value to a variable as follows and use substr
 // again to remove last comma and space.

    $cleanstr = explode(", ", $cleanstr);
    $ct2 = 0;
    foreach ( $cleanstr as $cl){
        $name = "a".$ct2;
        $$name = $cl;
        $varstr .= "$".$name.", ";
        $ct2 = $ct2 +1;    
    }
   $varstr = substr($varstr, 0, -2);

 // I've echoed the $varstr to the browser and get $a1, $a2, $a3, $a4.
 // I have also echo their value outside of the loop and know values 
 // have been assigned.

 // I then try to assign each step above the appropriate 
 // prepared statement place holder

   $stmt = mysqli_stmt_prepare($db, "INSERT INTO Contacts VALUES (". $preparedQs. ")");
    mysqli_stmt_bind_param($db, "'".$preparedType."'", $varstr);
    mysqli_stmt_execute($stmt);

I'm am not sure what I am doing wrong because when I echo $preparedQs, $preparedType and $varstr they all have the same number of elements yet I'm getting the "mysqli_stmt_bind_param(): Number of elements in type definition string doesn't match number of bind variables in.." error. All i can think is that I have quotes or something where I shouldn't but I've tried adding and removing quotes in certain areas and cant get the error to resolve.

Also, I read some posts about passing null in prepared statement but even when I replace the null with an actual value, I still get the same error.

It's probably worth noting that when using simple procedural mysqli_query and mysqli_real_escape_string to clean my data things work fine. I am trying to improve my security by converting my application to prepared statement simply for the added security.

This question is different for two reasons

1. I am using procedural coding and not object or PDO. So being new to prepared statements, the examples given aren't helpful even after trying to make sense of them.

2. I am using an insert statement, not a select or update statement which in procedural php the query string is written differently for insert than for select or update statements.

//UPDATED CODE

global $dbconnection;
if(!$dbconnection){
    die("Function wm_dynamicForm connection failed.</br>");
} else {
    //echo "</br>Function wm_connectionToDatabase connection success</br>";
}
$db = mysqli_stmt_init($dbconnection);
$preparedQs = substr($preparedQs, 0, -2); //removes the end , from my string
$cleanstr = substr($cleanstr, 0, -2); //removes the end , from my string
$cleanstr = explode(", ", $cleanstr);
$ct = 0;
foreach ( $cleanstr as $cl){
    $items[] = array(
        'a'.$ct => $cl,
    );
    $ct = $ct + 1;
}

$stmt = mysqli_stmt_prepare($db, "INSERT INTO Contacts VALUES (". $preparedQs. ")");
mysqli_stmt_bind_param($db, $preparedType, ...$items);
mysqli_stmt_execute($stmt);
if(!mysqli_stmt_execute($stmt)){ 
echo "Error: ".mysqli_error($db); 
}

Answer 1

try to use like this in prepared statement.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$conn = new mysqli($servername, $username, $password, $dbname);
$cleanstr = "John,Dew,Doe,Sr.";
$cleanstr = explode(",", $cleanstr);
$varstr=array();
foreach($cleanstr as $cl){
    $varstr[] = "$".$cl;
}

$operation = "INSERT INTO Contacts (firstname, middlename, lastname, suffix) VALUES (?, ?, ?, ?)";

$callfunc = insertCommon($conn,$varstr, $operation);

function insertCommon($conn,$varstr, $operation){
    $types = "";
    foreach($varstr as $value)
        $types .= "s";
    $varstr = array_merge(array($types),$varstr);
    $insertQry = $conn->prepare($operation);
    $refArray = array();
    foreach($varstr as $key => $value) $refArray[$key] = &$varstr[$key];
    call_user_func_array(array($insertQry, 'bind_param'), $refArray);
    $insertQry->execute();
    return true;
}

Answer 2

I've been here before, dynamic prepared statement, dynamic query preparation.

The problem is not your code so far, the problem is the array of your sql fields you dynamically prepared to bind. The index of that array starts with zero(0), but the index of your bindValue needs to start with one(1). So what you will do is to make your field array index to start with 1.

In php you can force defaul index of an array to start with 1 instead of zero.

If am not wrong you have:

 dbfield="username, password, name"

dbvalue="?, ?, ?"

and you have an array of input value you are looping with like:

 foreach($inputarray as $key=>$value){
 // key index must start from 1

 //now you can bind
 bindValue($key, $value);
 }

If am flowing hit me answer accept.

Answer 3

You could do dynamic bind with php 5.6 feature called unpacking operator/elipsies the ....

$db = mysqli_connect('localhost', 'root', 'pass', 'database');


$data = array('name' => 'foo', 'age' => 99, 'email' => 'abc@abc.com');

$stmt = mysqli_stmt_prepare($db, "INSERT INTO Contacts VALUES (". $preparedQs. ")");
mysqli_stmt_bind_param($db, $preparedType, ...$data);
mysqli_stmt_execute($stmt);