如何在PHP中动态绑定mysqli bind_param参数？
如何在PHP中动态绑定mysqli bind_param参数？
我一直在学习为我的sql查询使用预备语句和绑定语句，到目前为止我已经提出了这个问题，它可以正常工作但是当涉及到多个参数或者当不需要参数时，它根本不是动态的，
public function get_result($sql,$parameter)
{
# create a prepared statement
$stmt = $this->mysqli->prepare($sql);
# bind parameters for markers
# but this is not dynamic enough...
$stmt->bind_param("s", $parameter);
# execute query
$stmt->execute();
# these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$var = $field->name;
$$var = null;
$parameters[$field->name] = &$$var;
}
call_user_func_array(array($stmt, 'bind_result'), $parameters);
while($stmt->fetch())
{
return $parameters;
//print_r($parameters);
}
# close statement
$stmt->close();
}这就是我调用对象类的方式，
$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);$output = new search($mysqli);
有时候我不需要传递任何参数，
$sql = " SELECT * FROM root_contacts_cfm ";print_r($output->get_result($sql));
有时我只需要一个参数，
$sql = " SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ? ORDER BY cnt_id DESC ";print_r($output->get_result($sql,'1'));
有时我只需要一个以上的参数，
$sql = " SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ? AND root_contacts_cfm.cnt_firstname = ? ORDER BY cnt_id DESC ";print_r($output->get_result($sql,'1','Tk'));
所以，我认为这条线对于上面的动态任务来说不够动态，
$stmt->bind_param("s", $parameter);要动态构建bind_param，我在其他帖子上发现了这一点。
call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);
我试图从php.net修改一些代码，但我无处可去，
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$array_of_param[$key] = &$arr[$key];
call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);
}为什么？任何想法我怎么能让它工作？
或者也许有更好的解决方案？
互换的青春浏览 1128回答 3
3回答
-
桃花长相依
找到了mysqli的答案：public function get_result($sql,$types = null,$params = null) { # create a prepared statement $stmt = $this->mysqli->prepare($sql); # bind parameters for markers # but this is not dynamic enough... //$stmt->bind_param("s", $parameter); if($types&&$params) { $bind_names[] = $types; for ($i=0; $i<count($params);$i++) { $bind_name = 'bind' . $i; $$bind_name = $params[$i]; $bind_names[] = &$$bind_name; } $return = call_user_func_array(array($stmt,'bind_param'),$bind_names); } # execute query $stmt->execute(); # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc() $meta = $stmt->result_metadata(); while ($field = $meta->fetch_field()) { $var = $field->name; $$var = null; $parameters[$field->name] = &$$var; } call_user_func_array(array($stmt, 'bind_result'), $parameters); while($stmt->fetch()) { return $parameters; //print_r($parameters); } # the commented lines below will return values but not arrays # bind result variables //$stmt->bind_result($id); # fetch value //$stmt->fetch(); # return the value //return $id; # close statement $stmt->close(); }然后：$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);$output = new search($mysqli);$sql = " SELECT * FROM root_contacts_cfm ORDER BY cnt_id DESC ";print_r($output->get_result($sql));$sql = " SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ? ORDER BY cnt_id DESC ";print_r($output->get_result($sql,'s',array('1')));$sql = " SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ? AND root_contacts_cfm.cnt_firstname = ? ORDER BY cnt_id DESC ";print_r($output->get_result($sql, 'ss',array('1','Tk')));mysqli在谈到这个时是如此蹩脚。我想我应该迁移到PDO！ -
慕容3067478
使用PHP 5.6可以在解包operator（...$var）的帮助下轻松完成，并使用bind_result（）的get_result（）public function get_result($sql,$types = null,$params = null) { $stmt = $this->mysqli->prepare($sql); $stmt->bind_param($types, ...$params); if(!$stmt->execute()) return false; return $stmt->get_result();}例：$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);$output = new search($mysqli);$sql = "SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ? AND root_contacts_cfm.cnt_firstname = ? ORDER BY cnt_id DESC";$res = $output->get_result($sql, 'ss',array('1','Tk'));while($row = res->fetch_assoc()){ echo $row['fieldName'] .'<br>';} -
交互式爱情
或者也许有更好的解决方案？这个答案对你没什么帮助，但是你应该认真考虑从mysqli 切换到PDO。这样做的主要原因是PDO通过内置函数完成了你在mysqli中尝试做的事情。除了具有手动param绑定之外，execute方法还可以使用一组参数。PDO很容易扩展，并且添加方便的方法来获取所有东西和返回而不是做准备 - 执行舞蹈是非常容易的。
随时随地看视频慕课网APP
PHP
MySQL