PHP + MYSQLI: binding parameter / result to prepared statements

Question

In the project that I am about to complete, I wrote and implemented an object-relational mapping solution for PHP. Before the doubters and dreamers shout “how on earth?”, Relax - I have not found a way to do the late static binding work - I just work on it in the best way that I can.

In any case, I do not currently use prepared statements for queries, because I could not find a way to pass a variable number of arguments to the bind_params() or bind_result() methods.

Why do I need to maintain a variable number of arguments, you ask? Since the superclass of my models (I think my solution as a hacked PHP ActiveRecord wannabe) is where the query is defined, so the find () method, for example, does not know how many parameters it needs to bind.

Now I was thinking about creating an argument list and passing a string to eval (), but I don't really like this solution - I would rather just implement my own security checks and pass instructions.

Does anyone have any suggestions (or success stories) on how to do this? If you can help me solve this first problem, perhaps we can relate the result set (what I suspect will be more complex or at least more resource intensive if it includes the original query to determine the structure of the table).

Answer 1

In PHP, you can pass a variable number of arguments to a function or method using call_user_func_array . An example for a method:

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

The function will be called with each member in the array passed as its own argument.

Answer 2

You must make sure that $ array_of_params is an array of variable references , not the values ​​themselves. Must be:

 $array_of_params[0] = &$param_string; //link to variable that stores types 

And then...

 $param_string .= "i"; $user_id_var = $_GET['user_id'];// $array_of_params[] = &$user_id_var; //link to variable that stores value 

Otherwise (if it is an array of values) you will get:

PHP warning: Parameter 2 for mysqli_stmt :: bind_param () must be a reference

---

One more example:

 $bind_names[] = implode($types); //putting types of parameters in a string for ($i = 0; $i < count($params); $i++) { $bind_name = 'bind'.$i; //generate a name for variable bind1, bind2, bind3... $$bind_name = $params[$i]; //create a variable with this name and put value in it $bind_names[] = & $$bind_name; //put a link to this variable in array } 

and BOOOOOM:

 call_user_func_array( array ($stmt, 'bind_param'), $bind_names); 

Answer 3

I am not allowed to edit, but I believe in code

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

The link before $ stmt is not required. Since $stmt is an object, and bindparams is a method in this object, no reference is required. It should be:

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

For more information, see the PHP manual Callback Functions .

Answer 4

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

Not working for me in my environment, but this answer set me on the right track. Actually it was:

 $sitesql = ''; $array_of_params = array(); foreach($_POST['multiselect'] as $value){ if($sitesql!=''){ $sitesql .= "OR siteID=? "; $array_of_params[0] .= 'i'; $array_of_params[] = $value; }else{ $sitesql = " siteID=? "; $array_of_params[0] .= 'i'; $array_of_params[] = $value; } } $stmt = $linki->prepare("SELECT IFNULL(SUM(hours),0) FROM table WHERE ".$sitesql." AND week!='0000-00-00'"); call_user_func_array(array(&$stmt, 'bind_param'), $array_of_params); $stmt->execute(); 

Answer 5

A more modern way to dynamically bind parameters is to use the splat / spread operator ( ... ).

Assuming that:

• you have a non-empty array of values ​​to bind to your request and
• the values ​​of your array are appropriately treated as string type values ​​in the request context and
• Your input array is called $values

Code for PHP5.6 and higher:

 $stmt->bind_param(str_repeat('s', count($values)), ...$values);