Binding an unknown number of parameters using mysqli

Question

I'm trying to make a search in a table, something like this: http://www.phpjabbers.com/free-scripts/mysql-search-table/search.php

I figured out that I could just concatenate a string depending on the search form $_GET so I can query it all after getting the parameters:

$query = "SELECT * FROM table WHERE status = 1"
if($_GET['param1']{
  $query = $query." AND param1 = ?";
}
$stmt = $mysqli->prepare($query);

That would be perfect if I wouldn't have to add:

$stmt->bind_param('i',$_GET['art']);

I was following this post's instructions: https://stackoverflow.com/a/11152781/679333, but the wildcard part didn't work. Instead of that for loop I referenced the variables when I pushed them into the array:

array_push($user_terms, &$_GET['var']);

It works, but now I'm getting a "Deprecated: Call-time pass-by-reference has been deprecated" warning.

I don't want to ignore the warning because I read Call-time pass-by-reference has now been killed from PHP.

I don't know how can I transform a value to a reference in a loop (if I push the variable to the array without the reference, the value will be pushed, not the reference). $stmt->bind_param() expects references, not values. — Kirby, Sep 18 '12 at 22:41
Add the reference values to an array, it will keep order if you don't sort it. Once you prepare the statement, iterate over that array and bind the params. — wesside, Sep 18 '12 at 22:43
FWIW, http://php.net/manual/en/mysqli-stmt.bind-param.php has examples on how to "bind to an array". YMMV. — , Sep 18 '12 at 22:55

wesside · Accepted Answer · 2012-09-19T04:58:00.950

3

A bit sloppy, but gets the job done.

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;
}

$params = array();

$query = "SELECT * FROM table WHERE status = 1";

// Iterate over your paramters from $_GET
foreach ($_GET as $k => $v) 
{ 
  if(!empty($v)
  {
    $query .= " AND $k = ?";
    $params[$k] = helper::sanitize($v);
  }
}
// After you get through all your params...

$stmt = $mysqli->prepare($query);

// Bind em.
call_user_func_array(array($stmt, 'bind_param'), refValues($params));

That should do it, though I've never bound with mysqli before. Let me know how that works.

edited Sep 19 '12 at 04:58

answered Sep 18 '12 at 22:51

wesside

5,622
5
30
35

It worked, thank you very much. If you post refValues() (so other users can see) I'll accept the answer c: – Kirby Sep 19 '12 at 01:24
You don't need it, but there it is. Cheers. – wesside Sep 19 '12 at 04:58
`empty` is not sufficient in this case: e.g., when a `$_GET` variable contains `"0"`, `empty` will say it's empty, but it is a perfect legit value for an SQL statement. – Marcel Korpel Nov 24 '13 at 21:24

Binding an unknown number of parameters using mysqli

1 Answers1

Linked