I am trying to implement dynamic sql query so that users has flexibility to use one or more filters to dig out the data he wants. I am using prepared statements to ensure that there is no SQL Injection. I am using WAMP Server 64 bit. I referred to this article for writing my code: Sample Following is my code:
<pre>$myqry="SELECT * FROM students WHERE ";
$initflag=0; //controls the first clause without AND and rest with AND
$paramtypes=array();
$paramvalues=array();
$params=array();
$ptypes="";
$pvalues="";
//echo "Admission Year " . $_POST['awr_admyear1'];
if(!empty($_POST['awr_admyear1']))
{
$myqry.= "YEAR(adm_date)=? ";
$initflag=1;
$ptypes='s';
$pvalues=$_POST['awr_admyear1'];
$paramtype[]="s";
$paramvalues[]=$_POST['awr_admyear1'];
}
if(!empty($_POST['awr_admfrom']) && !empty($_POST['awr_admto']))
{
if($initflag==0)
{
$myqry.= "(YEAR(adm_date) BETWEEN ? AND ?) ";
$ptypes.="ss";
$pvalues.=$_POST['awr_admfrom'] . "," . $_POST['awr_admto'];
}
else
{
$myqry.= "AND (YEAR(adm_date) BETWEEN ? AND ?) ";
$ptypes.="ss";
$pvalues.="," . $_POST['awr_admfrom'] . "," . $_POST['awr_admto'];
}
$initflag=1;
$paramtype[]="s";
$paramtype[]="s";
$paramvalues[]=$_POST['awr_admfrom'];
$paramvalues[]=$_POST['awr_admto'];
}
if(!empty($_POST['awradm_no']))
{
if($initflag==0)
{
$myqry.= "adm_no LIKE ? ";
$ptypes.='s';
$pvalues.="%".$_POST['awradm_no']. "%";
}
else
{
$myqry.= "AND adm_no LIKE ? ";
$ptypes.='s';
$pvalues.="%".$_POST['awradm_no']. "%";
}
$initflag=1;
$paramtype[]="s";
$paramvalues[]="%".$_POST['awradm_no']. "%";
}
$params = array_merge($paramtype,$paramvalues);
<pre>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;
}
if(isset($myqry) && !(empty($myqry)))
{
if($result1 = $mysqli->prepare($myqry))
{
call_user_func_array(array($result1, 'bind_param'), refValues($params));
//$result1->bind_param($paramtype, $paramvalues);
if($result1->execute())
{
$finrest=$result1->get_result();
while($row= $finrest->fetch_assoc())
{
I am getting the following error:
Warning: Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in ..
Where I am going wrong and what is the solution?
What I have tried:
have tried a lot of solutions from google and stackoverflow too, but no wonders.