What is a simple solution for dynamic mysqli bind_param arguments in PHP?

Question

To build a bind_param dynamically, I have found this on other SO posts.

call_user_func_array(array(&$stmt, 'bindparams'), $array_of_params);

Can someone break this down in plain english for me? I get especially lost that the first argument is an array.

Answer 1

array($stmt, 'bindparams') 

is PHP's way of identifying method bind_params on the object $stmt, since PHP 5 you don't need to use the & in front any longer (and mysqli is PHP 5 so this looks like a glitch in the older post).

you can see a similar example here

so

call_user_func_array(array($stmt, 'bindparams'), $array_of_params);

basically means

$stmt->bind_params($array_of_params[0], $array_of_params[1] ... $array_of_params[N])

Answer 2

As far as I know, you cannot pass the result of e.g. $userid == "ALL" to a mysqli-statement-Object's bind_param method, because this method wants the parameters to be passed by reference. Obviously this is not possible with the result of an expression evaluated "in place".

As a workaround, I changed the program's second part to

$userIdEmpty = $userid == "ALL";
$locationEmpty = $location = "ALL";
$stmt->bind_param( "siiiii", 
  "active", $userid, $userIdEmpty,
  $location, $locationEmpty,
  $limit);

Like that, the result of the boolean operation can be passed by reference.

Answer 3

There's a much simper way to do this.

create this prepared statement:

select * from mytable 
 where status = ? and (userid = ? or ?) 
 and (location = ? or ?)
 order by `date` desc, time desc
 limt ?

and pass the args to bind like this:

$stmt = $mysqli->prepare( [statement above] );
$stmt->bind_param( "siiiii", 
  "active", $userid, $userid == "ALL", 
  $location, $location == "ALL", 
  $limit); 

The predicate (user_id = ? or ?) will be true when the user_id equals the first replaced parameter, or when the second replaced parameter is true.

$user_id when converted to an int will be its value when it's a string representation of a number, or zero otherwise. The expression $userid == "ALL" will evaluate to a boolean, which will be passed to bind_param. We can't tell bind_param that a parameter is a boolean (the format string only understand string, int, double, and blob), so bind_param will convert the boolean to an int, which works for us.

As long as no user_id or location_id in the database is zero, you're fine.