如何在PHP中動態(tài)綁定mysqli bind_param參數(shù)?我一直在學習為我的sql查詢使用預備語句和綁定語句,到目前為止我已經(jīng)提出了這個問題,它可以正常工作但是當涉及到多個參數(shù)或者當不需要參數(shù)時,它根本不是動態(tài)的,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();
}這就是我調(diào)用對象類的方式,$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);$output = new search($mysqli);有時候我不需要傳遞任何參數(shù),$sql = "
SELECT *
FROM root_contacts_cfm
";print_r($output->get_result($sql));有時我只需要一個參數(shù),$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";print_r($output->get_result($sql,'1'));有時我只需要一個以上的參數(shù),$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'));所以,我認為這條線對于上面的動態(tài)任務來說不夠動態(tài),$stmt->bind_param("s", $parameter);要動態(tài)構建bind_param,我在其他帖子上發(fā)現(xiàn)了這一點。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);
}為什么?任何想法我怎么能讓它工作?或者也許有更好的解決方案?
3 回答

桃花長相依
TA貢獻1860條經(jīng)驗 獲得超8個贊
找到了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
TA貢獻1773條經(jīng)驗 獲得超3個贊
使用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>';}

交互式愛情
TA貢獻1712條經(jīng)驗 獲得超3個贊
或者也許有更好的解決方案?
這個答案對你沒什么幫助,但是你應該認真考慮從mysqli 切換到PDO。
這樣做的主要原因是PDO通過內(nèi)置函數(shù)完成了你在mysqli中嘗試做的事情。除了具有手動param綁定之外,execute方法還可以使用一組參數(shù)。
PDO很容易擴展,并且添加方便的方法來獲取所有東西和返回而不是做準備 - 執(zhí)行舞蹈是非常容易的。
添加回答
舉報
0/150
提交
取消