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.