Alex Rivera | Logout

Use one bind_param() with variable number of input vars

Asked 2009-04-27T13:24:47.373
10

I try to use variable binding like this:

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

but some of the $_POST['...'] might be empty so I don't want to update them in the DB.

It's not practical to take into account all the different combination of empty $_POST['...'] and although I can build the string " UPDATE mytable SET..." to my needs, bind_param() is a different beast.

I could try building its call as a string and use eval() on it but it doesn't feel right :(

php mysqli
Edit
Report

1 Answer

-2

Build it as a string, but put your values into an array and pass that to bindd_param. (and substitute ?'s for 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);
answered 2009-04-27T13:28:26.053

Your Answer