mysqli prepare fails with generic error: mysql: 5.5.27

[JA12]

I'm trying to upgrade from mysql to mysqli and I'm having real problems.

 

I've finally got connected to the database, I'm now having problems using the prepare with the bind functionality.

$sql = "INSERT INTO tb_test (field1,field2,field3) VALUES {?, ?, ?}";
$types = Array('s','s','i');
$values = Array('field1','field2','1');
if(!$mysqli->execute_bind($sql,$types, $values)) {
}
echo $mysqli->getErrorMsg();
echo '<br />';
print_r($mysqli->getErrorLst());
echo '<br />';

this is my function

  public function execute_bind($Query, $Types, $Values, $Commit = true, $AutoCommit = true) {
    # set autocommit
    mysqli_autocommit($this->_mysqli, $AutoCommit);
    $bind = new BindParam;
    $arrParams = array();

echo 'step 1<br />';

    $numValues = count($Values);
    $type = array();
    $val = array();
    # process the types
    if(is_array($Types)) {
      # array passed
      foreach($Types as $value){
        # must have the same number of types as values
        $type[] = $value;
      }
    }
echo 'step 2:<br />'; print_r($type); echo '<br />';

    # process the values
    if(is_array($Values)) {

echo 'step 2:<br />'; print_r($Values); echo '<br />';

      for($i=0;$i<$numValues;$i++) {
        if($this->check_type($Values[$i],$type[$i])) {
         array_push($val, $Values[$i]);
         $bind->add($type[$i], $val[$i]);


        } else {
        $this->errorMsg .= mysqli_stmt_error($stmt);
        array_push($this->errorLst,mysqli_error_list($stmt));
          $this->errorMsg = "Type/Value combination does not match. @" . $i;
          return false;
        }
      }
    } else {
      # only one value passed
      if($this->check_type($Values,$type)) {
        $bind->add($Types, $Values);
      } else {
        $this->errorMsg = "Type/Value combination does not match.";
        return false;
      }
    }
echo 'step 3:<br />'; print_r($bind->get()); echo '<br />';

    # values need to be passed by reference for PHP 5.3
    $arrParams = $this->refValues($bind->get());
echo 'step 4:<br />';

    if ($stmt = mysqli_prepare($this->_mysqli, $Query)) { // <<<<<<<<<< the error is happening here

echo 'step 5:<br />';

       $method = new ReflectionMethod('mysqli_stmt', 'bind_param'); 
       $method->invokeArgs($stmt, $arrParams);    
      # Execute the statement 
      mysqli_stmt_execute($stmt);
      # check to see if the execute worked
      $numRows = mysqli_affected_rows($this->_mysqli);

echo 'step 6:<br />', $numRows; echo '<br />';

      if($numRows == 0) {
        # something failed in this execute
        $this->errorMsg = mysqli_stmt_error($stmt);
        $this->errorLst = mysqli_error_list($stmt);
        return false;
      }
      # close statement 
echo 'step 7:<br />';
      mysqli_stmt_close($stmt);
    } else {
echo 'step 8:<br />';
      $this->errorMsg = mysqli_error($this->_mysqli);
      return false;
    }
echo 'step 9:<br />';
    return true;  
  }

this is the results:

step 1
step 2:
Array ( [0] => s [1] => s [2] => i ) 
step 2:
Array ( [0] => field1 [1] => field2 [2] => 1 ) 
step 3:
Array ( [0] => ssi [1] => field1 [2] => field2 [3] => 1 ) 
step 4:
step 8:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{?, ?, ?}' at line 1
Array ( )

The error I'm getting is the "generic" and really doesn't help me trouble shoot. I know I'm passing matching type/values. I know the table exists with the right field types.

Edited June 19, 2013 by JA12

[mac_gyver]

the error is calling your attention to the point in the query statement that mysql could not understand.

 

you are using {  } instead of (  )

[rwhite35]

Crongrats on migrating to a better db process!  I think you have a few issues here, but the one critical to your questions is here:

$bind = new BindParam;

You're trying to mix PDO with MySQLi and its not going to work.  Additionally, you're using MySQLi procedural style (which is fine) however, PDO is ONLY object oriented.  The binding method you want to use for MySQLi procedural would be something like:

$stmt = mysqli_prepare($link, "INSERT INTO tb_test VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'ssi', $value1, $value2, $int);

Good luck, hope that helps move you in the right direction.

[JA12]

OK, I've progessed. I hadn't quoted the two string fields:

$sql = "INSERT INTO tb_test (field1,field2,field3) VALUES ('?', '?', ?)";
$types = Array('s','s','i');
$values = Array('field1','field2',1);
if(!$mysqli->execute_bind($sql,$types, $values)) {
}
  echo $mysqli->getErrorMsg();
  echo '<br />';
  print_r($mysqli->getErrorLst());
  echo '<br />';

It's now failing @

    if ($stmt = mysqli_prepare($this->_mysqli, $Query)) { 

echo 'step 5:<br />';
       if($numValues==1) {
         mysqli_stmt_bind_param($stmt, $Types, $Values);
       } else {
         $method = new ReflectionMethod('mysqli_stmt', 'bind_param'); 
         $method->invokeArgs($stmt, $arrParams);   // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< fails here
       }

$type: Array ( [0] => s [1] => s [2] => i ) 
$Values: Array ( [0] => field1 [1] => field2 [2] => 1 ) 
field1 - s
check_type: field1 - s - string
field2 - s
check_type: field2 - s - string
1 - i
check_type: 1 - i - integer
step 3:
Array ( [0] => ssi [1] => field1 [2] => field2 [3] => 1 ) 
step 4:
step 5:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in ...
 step 6:
-1
step 7:
step 9:

Array ( )

   Irritating, as I know the value/type pairs match...

Edited June 19, 2013 by JA12

[JA12]

Crongrats on migrating to a better db process!  I think you have a few issues here, but the one critical to your questions is here:

$bind = new BindParam;

You're trying to mix PDO with MySQLi and its not going to work.  Additionally, you're using MySQLi procedural style (which is fine) however, PDO is ONLY object oriented.  The binding method you want to use for MySQLi procedural would be something like:

$stmt = mysqli_prepare($link, "INSERT INTO tb_test VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'ssi', $value1, $value2, $int);

Good luck, hope that helps move you in the right direction.

 

Ah, I hadn't spotted the bind stuff is PDO...

I had been looking for a way to pass an array of values to mysqli_stmt_bind_param so I could dynamically pass any number of field/value combintations to the method....

 

Is there any way with mysqli to do this?

[JA12]

will this work?

$Values = $this->refValues($Values);
mysqli_stmt_bind_param($stmt, $Types, implode(',',$Values));

  function refValues($arr){
    //Reference is required for PHP 5.3+  
    if (strnatcmp(phpversion(),'5.3') >= 0) { 
      $refs = array(); 
      foreach($arr as $key => $value) {
         $refs[$key] = &$arr[$key]; 
      }
      return $refs; 
    } 
    return $arr; 
   }  

Edited June 19, 2013 by JA12

[mac_gyver]

i'm going to guess that the BindParam class you found was from the user notes on php.net?

 

if so, the ->add() method is dynamically building the string of 'ssi...' types for the first parameter to the mysql bind_parm() statement and it is also dynamically building the references to the data as an array. you would basically ONLY need to use the ->get() method to pass the combined array to the call_user_func_array() statement to dynamically build and run the bind_param() statement.

[kicken]

OK, I've progessed. I hadn't quoted the two string fields:

You're not supposed to quote parameters. Not having the quotes is correct. By having them you change those ?'s from parameter placeholders into literal strings. As such, that query only contains one parameter, not three, which is why you get the mis-match error.

 

I had been looking for a way to pass an array of values to mysqli_stmt_bind_param so I could dynamically pass any number of field/value combintations to the method....

It is possible with call_user_func_array

[rwhite35]

Hmm, I'm still trying to understand what you ultimately want to do.  If the user can input variable data, then you will have to match up the inputs values with the appropriate columns.  That could be tricky, but not impossible.  If you just want to dynamically build query string, you'll want to do this in a multi-step process.  Starting with getting the table columns names, then syncing those with the data input.  The code below is a method in a much larger class, but it builds dynamic query string.  Also, it implements PDO and not MySQLi, but you'll get an idea of the sub routines that went in to generating the query when all that's know if the table name.

/* 
* CLASS METHODS ADDRECORD
* Add a record to the target db table, We only need to know the table name
* sub routine 1 constructs the insert statement by calling getdbcols method
* sub routine 2 binds the post array value to the alias(?) positions.
* note - each position MUST have a value, even if the value is null (ie string_id)
*/
private function addnew($array,$tbl,$_conn){
		
	// MAKE PDO DB CONNECTION
	try{
		self::$_conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.'', DB_UNAME, DB_UPWORD);
	} catch (PDOException $e){
		print "Error: ".$e->getMessage()."<br>";
		die();
	}
		
	//SUB ROUTINE 1
	$tblarr = $this->getdbcols($tbl);		//returns array of db table column names
	$query = "INSERT INTO $tbl SET ";		//generate a query string
	foreach ($tblarr as $key){			//iterate over the cols names array
		  $query .= ($key."=?, ");		//using ? placeholder for bindParam
	}
	$query = rtrim($query,", ");			//remove EOL whitespace and comma
		
	//Prepare the statement, self refers to the sub class itself.
	$stmt = self::$_conn->prepare($query);
			
	//SUB ROUTINE 2 :construct the bindParam statement
	$arcount=count($array); 				//max loop count
	foreach($array as $key=>$value){
		$stmtarr[]=$value;				//convert assoc to numerated array
	}
	//re index array so array count, placeholder count and incrementor will sync up
	$stmtarr = array_combine(range(1, count($stmtarr)), array_values($stmtarr));
			
	for($i=1;$i<=$arcount;$i++){ //bind values to placeholder, one for each array element 
		$stmt->bindParam($i,$stmtarr[$i]);
	}
	
	//Execute the statement
	$stmt->execute();
	
	if($stmt==true){
		return $stmt;			//returns true on success
	} else {
		echo "There was an error in the query.";
	}
	self::$_conn->close();
}

Also, this isn't the complete code, so you won't be able to cup/paste this into you own source.  But hopefully it give you some idea on how to proceed with your code.

[JA12]

That didn't work, I'm getting:

Strict Standards: Only variables should be passed by reference 

I print_r'd the $Values array and spotted a possible problem so I tried quoting the value strings:

$values = Array("'field1'","'field2'",1); 

But I still got the same error...

Edited June 19, 2013 by JA12

[JA12]

rwhite35> I'm not using PDO - not yet anyway.

 

 

I've got an SQL statement that includes the "?" character to be replaced in the function by an array of values

 

 

The number of fields/value pairs can be any number, so I'm passing an array of values to be matched to the fields in the SQL statement (INSERT/UPDATE).

 

I can do all of this by fully forming the SQL statement outside of this class and just passing it to a prepare/execute without attempting to use the  "mysqli_stmt_bind_param" method. But I'm trying to develop a generic function...

 

Possibly silly, but there you go...

Edited June 19, 2013 by JA12

[kicken]

That didn't work, I'm getting:

Strict Standards: Only variables should be passed by reference

 

That error is because of this:

mysqli_stmt_bind_param($stmt, $Types, implode(',',$Values));

The arguments after $Types in mysqli_stmt_bind_params are references, which means they have to be variables, not the result of a function call. Aside from that, what you are doing is incorrect is it only passes a single value (a comma-separated string) not the three values you want.

 

In order to pass a variable-list of parameter values, you have to use call_user_func_array to execute the function. If you search around, there are implementations showing how to do this, I've posted one on this form before somewhere. Edit: here

 

On a side note, the inability of mysqli to support such a task nativly is one of the primary reasons why I tell people to use PDO instead and not bother with mysqli. While mysqli is an improvement over mysql_*, I find it to be sub-par and more difficult to use compared to PDO.

Edited June 19, 2013 by kicken

[rwhite35]

That error is because of this:

mysqli_stmt_bind_param($stmt, $Types, implode(',',$Values));

The arguments after $Types in mysqli_stmt_bind_params are references, which means they have to be variables, not the result of a function call. Aside from that, what you are doing is incorrect is it only passes a single value (a comma-separated string) not the three values you want.

 

In order to pass a variable-list of parameter values, you have to use call_user_func_array to execute the function. If you search around, there are implementations showing how to do this, I've posted one on this form before somewhere. Edit: here

 

On a side note, the inability of mysqli to support such a task nativly is one of the primary reasons why I tell people to use PDO instead and not bother with mysqli. While mysqli is an improvement over mysql_*, I find it to be sub-par and more difficult to use compared to PDO.

 

I second that last statement.

[JA12]

I've now got this:

  public function execute_bind($Query, $Types, $Values, $Commit = true, $AutoCommit = true) {
    # set autocommit
    mysqli_autocommit($this->_mysqli, $AutoCommit);
    $bind = new BindParam;

    $numValues = 1;
    if(is_array($Values)) { 
      $numValues = count($Values); 
      for($i=0;$i<$numValues;$i++) {
        $bind->add($Types[$i],$Values[$i]);
      }
    } else {
      $bind->add($Types,$Values);
    }

    if ($stmt = mysqli_prepare($this->_mysqli, $Query)) { 
      $Values = $this->refValues($bind->get());
      call_user_func_array(array($stmt, 'bind_param'),$Values);
      # Execute the statement 
      mysqli_stmt_execute($stmt);
      # check to see if the execute worked
      $numRows = mysqli_affected_rows($this->_mysqli);
      if($numRows == 0) {
        # something failed in this execute
        $this->errorMsg = mysqli_stmt_error($stmt);
        $this->errorLst = mysqli_error_list($stmt);
        return false;
      }
      # close statement 
      mysqli_stmt_close($stmt);
    } else {
      $this->errorMsg = mysqli_error($this->_mysqli);
      return false;
    }
    return true;  
  }

But now the blasted code produces:

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in

Grrrrr....

 

[rwhite35]

rwhite35> I'm not using PDO - not yet anyway.

 

 

I've got an SQL statement that includes the "?" character to be replaced in the function by an array of values

 

 

The number of fields/value pairs can be any number, so I'm passing an array of values to be matched to the fields in the SQL statement (INSERT/UPDATE).

 

I can do all of this by fully forming the SQL statement outside of this class and just passing it to a prepare/execute without attempting to use the  "mysqli_stmt_bind_param" method. But I'm trying to develop a generic function...

 

Possibly silly, but there you go...

Take a look at my code block.  I believe it does what you want it to do.  I simple pass the table name and array of input values, the method constructs the query and executes the query against the table.  For the most part, the code is complete. You would have to write your own getdbcols method which would query the db for the column names.