php动态mysql查询过滤器 [英] Php dynamic mysql query for filters

问题描述

我正在尝试实现动态SQL查询，以便用户可以灵活地使用一个或多个过滤器来挖掘他想要的数据。我正在使用预准备语句来确保没有SQL注入。我正在使用WAMP Server 64位。我在这篇文章中提到了编写我的代码：示例以下是我的代码：

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 ..

我哪里出错了，解决方案是什么？



我尝试了什么：



也尝试了很多google和stackoverflow的解决方案，但没有奇迹。

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.

推荐答案

myqry =SELECT * FROM students WHERE;

myqry="SELECT * FROM students WHERE ";

initflag = 0; //控制没有AND的第一个子句，并使用AND

initflag=0; //controls the first clause without AND and rest with AND

paramtypes = array（）;

paramtypes=array();

这篇关于php动态mysql查询过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！