Page 1 of 1

Problem with mysqli and bind variables in php 5.3

Posted: Thu Jan 06, 2011 11:24 am
by darwin_tech
Hi I have an infuriating problem which is stalling me with two large projects for a well known NGO right now.

I am using mysqli and bound variables where the number of variables to bind is dependent on user input. I have a version of the code below working in php 5.2 but as of php 5.3 this method is no longer valid, specifically due to a change in the behavior of call_user_func_array with bound variables as arrays. I have read about this problem eslewhere but cannot get any of the workarounds to work with my example. Any help would be greatly appreciated.

Code: Select all

# $parts    is an array with variable number of values
# $type     is an array with variable number of values
# $params   is an array with variable number of values



$query = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT 
 taxon.TaxonID,
FROM taxon
WHERE'
. join('', $parts) . 
" ORDER BY taxon.TaxonID";



# Prepare stmt
if ($stmt = $mysqli->prepare($query)) {


   call_user_func_array (array($stmt, 'bind_param'),array_merge(array(join('', $type)), $params)); 


	# execute
	$stmt->execute();
	
	
	# bug info
	echo $stmt->errno, ':', $stmt->error;

        #store result
	$stmt->store_result();

	# bind results
	$stmt->bind_result($ID);

        # fetch values 
        while ($stmt->fetch()) {

    	  # results code goes here!
    		
        }

       # free memory
       $stmt->free_result();

       # close statement 
       $stmt->close();
}


Re: Problem with mysqli and bind variables in php 5.3

Posted: Thu Jan 06, 2011 2:45 pm
by mikosiko
is a typo or are you missing a ' here after WHERE?

Code: Select all

$query = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT 
 taxon.TaxonID,
FROM taxon
WHERE
. join('', $parts) . 
" ORDER BY taxon.TaxonID";

Re: Problem with mysqli and bind variables in php 5.3

Posted: Mon Jan 10, 2011 8:19 am
by darwin_tech
Oops, you are right. The code is an abridged version of that which I'm using. I have edited the post accordingly.

Anyone know how to solve the actual problem??

Any assistance would be much appreciated as I am banging my head against a brick wall right now...

Re: Problem with mysqli and bind variables in php 5.3

Posted: Mon Jan 10, 2011 10:13 am
by mikosiko
post real values for your variables:
$parts, $type & $params

and any error message that you are getting

Re: Problem with mysqli and bind variables in php 5.3

Posted: Tue Jan 11, 2011 3:17 pm
by darwin_tech
OK, for anyone who might have stumbled across the same problem, I have a found a solution!

mostly it is detailed at http://php.net/manual/en/mysqli-stmt.bind-param.php

One key factor was that $type cannot be passed as an array. Here I use the join function to pass $type as a string.

Hope this may be useful...

Code: Select all

if ($stmt = $mysqli->prepare($sql)) {

	$type = join('', $type);
	
	call_user_func_array('mysqli_stmt_bind_param', array_merge (array($stmt, $type), refValues($params)));

	$stmt->execute();

        # bind results and fetch
}

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