2

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); 
}
user982853
  • 2,470
  • 14
  • 55
  • 82
  • Tons of answers with https://stackoverflow.com/search?q=mysqli+dynamic+bind+ – u_mulder Jun 13 '17 at 16:52
  • Check [this answer](https://stackoverflow.com/a/36071202/1342547) – rray Jun 13 '17 at 18:20
  • 1
    Possible duplicate of [PHP Dynamic Prepared Statement - Number of Elements Not Matching](https://stackoverflow.com/questions/44525617/php-dynamic-prepared-statement-number-of-elements-not-matching) – Anthon Jun 13 '17 at 18:24

3 Answers3

2

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);
rray
  • 2,518
  • 1
  • 28
  • 38
  • 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? – user982853 Jun 14 '17 at 14:23
  • @user982853 that means your query failed, get the error message: `if(!mysqli_stmt_execute($stmt)){ echo mysqli_error($db); }` – rray Jun 14 '17 at 14:25
  • mysqli_error() expects parameter 1 to be mysqli, object given – user982853 Jun 14 '17 at 14:29
  • @user982853 what is the name of your connection variable? you must pass it to `mysqli_error()` – rray Jun 14 '17 at 14:34
  • Just updated my post above with "Updated code" so you can see my entire code. Please advise and thank you so much for the help and your time. – user982853 Jun 14 '17 at 14:37
  • @user982853 change `echo "Error: ".mysqli_error($db);` to `echo "Error: ".mysqli_error($dbconnection);` – rray Jun 14 '17 at 14:38
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146674/discussion-between-user982853-and-rray). – user982853 Jun 14 '17 at 14:43
  • 1
    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. – user982853 Jun 14 '17 at 20:10
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;
}
Jeyaram
  • 19
  • 5
  • 1
    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??? – user982853 Jun 14 '17 at 00:30
  • @user982853 i am edited my code. hopefully it may help you. – Jeyaram Jun 14 '17 at 02:21
0

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.

Chukwu Remijius
  • 323
  • 1
  • 14