Click here to Skip to main content
15,037,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

PHP
<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:

PHP
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.
Posted
Updated 30-Jan-17 7:26am
v2

1 solution

As the error message says: adding more variables than values for these variables - maybe one of your values is NULL? To check this you should print the resulting query string to a file or return it for display.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900