php dynamic mysql query for filters

Question

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:

$myqry="SELECT * FROM students WHERE ";
$initflag=0; //controls the first clause without AND and rest with AND
$paramtypes=array();
$paramvalues=array();
$params=array();
if(!empty($_POST['awr_admyear']))
{
    $myqry.= "YEAR(adm_date)=? ";
    $initflag=1;
    $paramtypes[]='s';
    $paramvalues[]=$_POST['awr_admyear'];
}
if(!empty($_POST['awr_admfrom']) && !empty($_POST['awr_admto']))
{
    if($initflag==0)
        $myqry.= "YEAR(adm_date) BETWEEN ? AND ? ";
    else
        $myqry.= "AND YEAR(adm_date) BETWEEN ? AND ? ";
    $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 ? ";
    else
        $myqry.= "AND adm_no LIKE ? ";
    $initflag=1;
    $paramtype[]='s';
    $paramvalues[]=$_POST['awradm_no'];
}
$params = array_merge($paramtype,$paramvalues);
if(isset($myqry) && !(empty($myqry)))
{
    if($result1 = $mysqli->prepare($myqry))
    {
        call_user_func_array(array($result1, 'bind_param'), $params);
        if($result1->execute())
        {
            $finrest=$result1->get_result();
            while($row= $finrest->fetch_assoc()) 
            {
  //and so on

I am getting the following error:

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in..................

Where I am going wrong and what is the solution?

check this [link](http://stackoverflow.com/questions/16120822/mysqli-bind-param-expected-to-be-a-reference-value-given) — hungrykoala, Jan 30 '17 at 12:17
@hungrykoala That did the trick. But in code above, second if section passes two parameters instead of only one and this brings the error as: mysqli_stmt::bind_param(): Number of elements in type definition string doesnt match number of bind variables — ITSagar, Jan 30 '17 at 12:25
check the bind_param() definition and what you are sending there. And for goodnes sake, switch for PDO — Your Common Sense, Jan 30 '17 at 12:43
@YourCommonSense this is my bind_param definition: call_user_func_array(array($result1, 'bind_param'), $params); and can see in the code above, all parameters are passed fine, still do a cross check for me — ITSagar, Jan 30 '17 at 12:45
@YourCommonSense Also, echo count($paramtype); count($paramvalues);count($params); returns 3, 3, and 6 respectively when i use second and third if blocks. Count is correct for me according to the if blocks code. I also looped through and printed the values of all arrays, value sets are correct. — ITSagar, Jan 30 '17 at 12:48
The query i got by using 2nd and 3rd if blocks is: SELECT * FROM students WHERE (YEAR(adm_date) BETWEEN ? AND ?) AND adm_no LIKE ? — ITSagar, Jan 30 '17 at 12:55
@YourCommonSense I found that whenever there are more than 1 clauses in the generated SQL query, i get the same error about number of parameters. — ITSagar, Jan 30 '17 at 13:03
check the **mysqli_statement::bind_param() function definition** and compare to what you are sending there. — Your Common Sense, Jan 30 '17 at 13:08
And for goodnes sake, switch for PDO, where you will need just `$result1->execute($params);` instead of all that mess. — Your Common Sense, Jan 30 '17 at 13:08
@YourCommonSense I already mentioned above that everything seems fine and i dont understand why its is not taking equal number of parameters. Please suggest a solution — ITSagar, Jan 30 '17 at 13:21
Everything is fine? All right, what are correct parameters for bind_param()? — Your Common Sense, Jan 30 '17 at 13:22
@YourCommonSense My query is: SELECT * FROM students WHERE YEAR(adm_date)=? AND adm_no LIKE ? Parameter values in paramtype array are: ss and Parameter values in paramvalues array are: 1978%2% --- Then I pass these values as: call_user_func_array(array($result1, 'bind_param'), refValues($params)); — ITSagar, Jan 30 '17 at 13:31
what is "ss"? where did you get it? I don't see any code above that can give you a string like this — Your Common Sense, Jan 30 '17 at 13:32
@YourCommonSense In every if block, i have appended s to create a list of parameter types. In the code above, it is written as: $paramtype[]='s'; In current scenario, I am working on first and third if blocks passing 1978 and 2 as the values i textboxes. — ITSagar, Jan 30 '17 at 13:34
Don't tell me what is written in your code. I can read it all right. Tell me where did you get that "ss". show me the code that can print it — Your Common Sense, Jan 30 '17 at 13:35
This I have used at my end recently to test after you startedasking about the parameter checking. for($i=0; $i — ITSagar, Jan 30 '17 at 14:21

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

1) Follow this answer, mentioned here: mysqli bind_param() expected to be a reference, value given

2) Do the following changes:

$params = array_merge($paramtype,$paramvalues);

Replace it with:

$params[] = implode("", $paramtype);

$params = array_merge($params, $paramvalues);

$paramtype is array but bind_param needs first parameter should be string.

edited May 23 '17 at 10:29

Community

1
1

answered Jan 30 '17 at 16:38

Parminder Singh

351
1
10

php dynamic mysql query for filters

1 Answers1