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

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

Tons of answers with stackoverflow.com/search?q=mysqli+dynamic+bind+ — u_mulder
– u_mulder, Commented Jun 13, 2017 at 16:52
Possible duplicate of PHP Dynamic Prepared Statement - Number of Elements Not Matching — Anthon
– Anthon, Commented Jun 13, 2017 at 18:24

rray · Accepted Answer · 2017-06-13 18:26:32Z

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' => '[email protected]');

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

answered Jun 13, 2017 at 18:26

rray

2,5461 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user982853 Over a year ago

Thank you as this seem to get rid of my first error of my numbers not matching but now im getting the error: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in.... Any idea why?

rray Over a year ago

@user982853 that means your query failed, get the error message: if(!mysqli_stmt_execute($stmt)){ echo mysqli_error($db); }

user982853 Over a year ago

mysqli_error() expects parameter 1 to be mysqli, object given

rray Over a year ago

@user982853 what is the name of your connection variable? you must pass it to mysqli_error()

user982853 Over a year ago

the ... worked. I also had an error in my code that was type casting all my variables to strings and that's where I was getting a second error. But as it relates to my original questions, the ...elipsies unpacked my variables. Thank you.

|

Chukwu Remijius · Accepted Answer · 2017-06-13 18:41:29Z

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.

Jeyaram · Accepted Answer · 2017-06-14 02:25:43Z

0

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;
}

edited Jun 14, 2017 at 2:25

answered Jun 13, 2017 at 18:05

Jeyaram

195 bronze badges

2 Comments

user982853 Over a year ago

Everyone is responding with your same response. I fully understand the structure of a prepared statement so i don't need someone to just drop in the structure of a prepared statement and say this should work....The problem Im having is that my bind param is not working because im passing a string and not individual variables. I need someone to show me how to turn my string (created via the loop) in the bind params into individual variables. Right now it's passing a string "$first, $middle $last". 1 var and not 3 individual vars. How do i turn string into $first, $middle $last variables???

Jeyaram Over a year ago

@user982853 i am edited my code. hopefully it may help you.

Collectives™ on Stack Overflow

PHP Prepared Statement - Dynamic Vars Number of Element Error

This question is different for two reasons

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

This question is different for two reasons

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related