Function returning array with references intact? - MySQLi bind_param

Question

I have been using MySQLi and for the most part finding it very useful, bind_param() falls down when it comes to dynamic queries though. Anyway im using

call_user_func_array(array($stmt, 'bind_param'), $array_of_params);

which allow me to get around this, and my code worked fine. However it was a bit messy as I had various if statements to determine the $array_of_params, something like this

if($switch=="coreSkill_only"){
            $array_of_params[0]='s';
            $array_of_params[1]=&$coreSkill;

        }else if($switch=="region_only"){
            $array_of_params[0]='s';
            $array_of_params[1]=&$region;

        }else if($switch=="coreSkill_region"){
            $array_of_params[0]='si';
            $array_of_params[1]=&$coreSkill;
            $array_of_params[2]=&$region;
        }

So rather than cluttering up the method which deals with the actual query execution I decided to stick all the conditional stuff into its own method like so.

function &generateQueryDropDowns($coreSkill,$region){

$queryDetails=array();

$query="select id,title,location,salary,employer,image from jobs where";
$switch="";
$and=0;

$array_of_params=array();

if($coreSkill!="Any"){
    $query.=" coreSkill=?";
    $and=1;
    $override=1;
    $switch="coreSkill_only";
    $array_of_params[0]='s';
    $array_of_params[1]=&$coreSkill;
}
if($region!="Any"){
        if($and==1){
            $query.=" and";
            $switch="coreSkill_region";
            $array_of_params[0]='si';
            $array_of_params[1]=&$coreSkill;
            $array_of_params[2]=&$region;
        }else{
            $switch="region_only";
            $array_of_params[0]='s';
            $array_of_params[1]=&$region;
        }
    $query.=" region=?";
    $override=1;
}

$queryDetails['query']=$query;
$queryDetails['switch']=$switch;
$queryDetails['override']=$override;
$queryDetails['bind_params']=$array_of_params;


return $queryDetails;
}

And i call it from the original method like so

$foo = &$this->generateQueryDropDowns($coreSkill,$region);
$query=$foo['query'];
$switch=$foo['switch'];
$override=$foo['override'];
$array_of_params=$foo['bind_params'];

if($stmt = $connection->prepare($query)) {
call_user_func_array(array($stmt, 'bind_param'), $array_of_params);
etc....

But i get the following error about references

  PHP Warning:  Parameter 2 to mysqli_stmt::bind_param() expected to be a reference

According to http://www.php.net/manual/en/language.references.return.php

As far as i can work out im doing everything correctly to keep the references intact in the return array?

Any advice is very welcome.

This is a big part of the reason I recommend PDO_mysql over MySQLi. The MySQLi API is ridiculous in a number of ways. However, if memory serves, you need to do `$array_of_params = &$foo['bind_params'];`, for a reason I am still not entirely clear on. — DaveRandom, Oct 19 '12 at 10:00
check the [mysqli or PDO - what are the pros and cons?](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons) and [Moving from mysql to mysqli or pdo?](http://stackoverflow.com/questions/770782/moving-from-mysql-to-mysqli-or-pdo) — NullPoiиteя, Oct 19 '12 at 10:03
**And On the top check [PDO vs. MySQLi: Which Should You Use?](http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/)** — NullPoiиteя, Oct 19 '12 at 10:05
I have heard that PDO is much better in a number of ways, one day when i have time to play with it im sure i will move over. — cosmicsafari, Oct 19 '12 at 10:06
$array_of_params = &$foo['bind_params']; still gives the same error :S — cosmicsafari, Oct 19 '12 at 10:07

score 0 · Accepted Answer · answered Oct 19 '12 at 10:32

0

Turns out you need to pass the parameters as references too.

so

function &generateQueryDropDowns($coreSkill,$region){}

becomes

function &generateQueryDropDowns(&$coreSkill,&$region){}

and it now works as expected.

answered Oct 19 '12 at 10:32

cosmicsafari

3,949
11
37
56

Function returning array with references intact? - MySQLi bind_param

1 Answers1