mysql语句带变量参数_MySQL准备的带有可变大小变量列表的语句

I had a similiar problem today and I found this topic. Looking at the answers and searching around the google I found a pretty solution.

Although, my problem is a little bit more complicated. Because I have fixed binding values and dynamic too.

This is the solution.

$params = array()

$all_ids = $this->get_all_ids();

for($i = 0; $i <= sizeof($all_ids) - 1; $i++){

array_push($params, $all_ids[$i]['id']);

}

$clause = implode(',', array_fill(0, count($params), '?')); // output ?, ?, ?

$total_i = implode('', array_fill(0, count($params), 'i')); // output iiii

$types = "ss" . $total_i; // will reproduce : ssiiii ..etc

// %% it's necessary because of sprintf function

$query = $db->prepare(sprintf("SELECT *

FROM clients

WHERE name LIKE CONCAT('%%', ?, '%%')

AND IFNULL(description, '') LIKE CONCAT('%%', ?, '%%')

AND id IN (%s)", $clause));

$thearray = array($name, $description);

$merge = array_merge($thearray, $params); // output: "John", "Cool guy!", 1, 2, 3, 4

// We need to pass variables instead of values by reference

// So we need a function to that

call_user_func_array('mysqli_stmt_bind_param', array_merge (array($query, $types), $this->makeValuesReferenced($merge)));

And the function makeValuesreferenced:

public function makeValuesReferenced($arr){

$refs = array();

foreach($arr as $key => $value)

$refs[$key] = &$arr[$key];

return $refs;

}