Use one bind_param () with a variable number of input vars

Question

I am trying to use variable binding as follows:

$stmt = $mysqli->prepare("UPDATE mytable SET myvar1=?, myvar2=... WHERE id = ?")) { $stmt->bind_param("ss...", $_POST['myvar1'], $_POST['myvar2']...); 

but some of $ _POST ['...'] may be empty, so I don’t want to update them in the database.

It is impractical to take into account all the different combinations of empty $ _POST ['...'], and although I can build the string "UPDATE mytable SET ..." for my needs, bind_param () is another beast.

I could try to build my call as a string and use eval () on it, but that doesn't seem right: (

Answer 1

You can use the call_user_func_array function to call the bind_param method with a variable number or arguments:

 $paramNames = array('myvar1', 'myvar2', /* ... */); $params = array(); foreach ($paramNames as $name) { if (isset($_POST[$name]) && $_POST[$name] != '') { $params[$name] = $_POST[$name]; } } if (count($params)) { $query = 'UPDATE mytable SET '; foreach ($params as $name => $val) { $query .= $name.'=?,'; } $query = substr($query, 0, -1); $query .= 'WHERE id = ?'; $stmt = $mysqli->prepare($query); $params = array_merge(array(str_repeat('s', count($params))), array_values($params)); call_user_func_array(array(&$stmt, 'bind_param'), $params); } 

Answer 2

This is what I use to execute prepared mysqli commands with a variable number of parameters. This is part of the class that I wrote. This is convenient for you, but it should show you the right direction.

 public function __construct($con, $query){ $this->con = $con; $this->query = $query; parent::__construct($con, $query); //We check for errors: if($this->con->error) throw new Exception($this->con->error); } protected static $allowed = array('d', 'i', 's', 'b'); //allowed types protected static function mysqliContentType($value) { if(is_string($value)) $type = 's'; elseif(is_float($value)) $type = 'd'; elseif(is_int($value)) $type = 'i'; else throw new Exception("type of '$value' is not string, int or float"); return $type; } //This function checks if a given string is an allowed mysqli content type for prepared statement (s, d, b, or i) protected static function mysqliAllowedContentType($s){ return in_array($s, self::$allowed); } public function feed($params){ //These should all be empty in case this gets used multiple times $this->paramArgs = array(); $this->typestring = ''; $this->params = $params; $this->paramArgs[0] = ''; $i = 0; foreach($this->params as $value){ //We check the type: if(is_array($value)){ $temp = array_keys($value); $type = $temp[0]; $this->params[$i] = $value[$type]; if(!self::mysqliAllowedContentType($type)){ $type = self::mysqliContentType($value[$type]); } } else{ $type = self::mysqliContentType($value); } $this->typestring .= $type; //We build the array of values we pass to the bind_params function //We add a refrence to the value of the array to the array we will pass to the call_user_func_array function. Thus say we have the following //$this->params array: //$this->params[0] = 'foo'; //$this->params[1] = 4; //$this->paramArgs will become: //$this->paramArgs[0] = 'si'; //Typestring //$this->paramArgs[1] = &$this->params[0]; //$this->paramArgs[2] = &$this->params[1]. //Thus using call_user_func_array will call $this->bind_param() (which is inherented from the mysqli_stmt class) like this: //$this->bind_param( 'si', &$this->params[0], &$this->params[1] ); $this->paramArgs[] = &$this->params[$i]; $i++; } unset($i); $this->paramArgs[0] = $this->typestring; return call_user_func_array(array(&$this, 'bind_param'), $this->paramArgs); } 

You use it as follows:

  $prep = new theClassAboveHere( $mysqli, $query ); $prep->feed( array('string', 1, array('b', 'BLOB DATA') ); 

The class must extend the mysqli_stmt class.

Hope this helps you in the right direction. If you cannot publish the whole class, it involves binding variable results.

Answer 3

Obviously, you are creating your statement using an array:

 $params = array(); $fragments = array(); foreach($_POST as $col => $val) { $fragments[] = "{$col} = ?"; $params[] = $val; } $sql = sprintf("UPDATE sometable SET %s", implode(", ", $fragments)); $stmt = $mysqli->prepare($sql); $stmt->bind_param($params); 

Answer 4

array_insert does not exist, I suppose it refers to some home function, but I don’t know exactly what it does ... inserts the parameter types into the array somewhere at the beginning, I would guess since the value is 0, but hey it too maybe at the end;)

Answer 5

Create it as a string, but put your values ​​in an array and pass this to bindd_param. (and replace? for the values ​​in your SQL string.

$ stmt = $ mysqli-> prepare ("UPDATE mytable SET myvar1 = ?, myvar2 = ... WHERE id =?")) {$ stmt-> bind_param ("ss ...", $ _POST ['myvar1'] , $ _POST ['myvar2'] ...);

For example:

 $args = array(); $sql = "UPDATE sometable SET "; $sep = ""; $paramtypes = ""; foreach($_POST as $key => $val) { $sql .= $sep.$key." = '?'"; $paramtypes .= "s"; // you'll need to map these based on name array_push($args, $val); $sep = ","; } $sql .= " WHERE id = ?"; array_push($args, $id); array_insert($args, $paramtypes, 0); $stmt = $mysqli->prepare($sql); call_user_func_array(array(&$stmt, 'bindparams'), $array_of_params); $stmt->bind_param($args);