問題描述
我對以下語法錯誤感到頭疼.我正在嘗試將內爆數組綁定到準備好的語句中，但出現以下語法錯誤:
I am beating my head over the below syntax error. I am trying to bind an imploded array into a prepared statement, but I am getting the following syntax error:
您的 SQL 語法有錯誤；檢查與您的 MySQL 服務器版本相對應的手冊，了解在?"附近使用的正確語法.在第 1 行
這是我的代碼.誰能看出我哪里出錯了?
Here is my code. Can anyone see where I am going wrong?
<?php
include('config.php');
$selected = $_POST['selected'];
if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN ?")) {
$stmt->bind_param("s", "('" . implode("', '", $selected) . "')" );
$stmt->execute();
$stmt->close();
print "ok";
} else {
print $mysqli->error;
}
$mysqli->close();
?>
作為測試，我嘗試了:
print "('" . implode("', '", $selected) . "')";
哪個正確地給了我
('me@me.com', 'you@you.com')
推薦答案
讓我為您省去一些麻煩并告訴您您嘗試做的事情無論如何都行不通.您只將一個參數綁定到您的 IN() 函數調用.您認為您正在傳遞一個逗號分隔的列表，但實際上您只傳遞了一個逗號分隔的字符串，該字符串被視為一個值.這意味著您將搜索具有'me@me.com'、'you@you.com'"值的一條記錄，而不是與me@me.com"匹配的記錄或you@you.com".
Let me save you some trouble and tell you what you're trying to do won't work anyway. You are only binding one parameter to your IN() function call. You think you're passing a comma separated list but you are actually only passing a comma separated string which is treated as one value. This means you will be search for one record with a value of "'me@me.com', 'you@you.com'" instead of records that match "me@me.com" or "you@you.com".
要克服這個問題，您需要:
To overcome this you need to:
- 動態生成類型字符串
- 使用
call_user_func_array()綁定你的參數
您可以像這樣生成類型字符串:
You can generate the types string like this:
$types = str_repeat('s', count($selected));
所有這些都是創建一個 s 的字符串，它的字符數與數組中的元素數一樣多.
All this does is create a string of s's that is as many characters as the number of elements in the array.
然后你可以像這樣使用 call_user_func_array() 綁定你的參數(注意我把括號放回 IN() 函數):
You would then bind your parameters using call_user_func_array() like this (notice I put the parenthesis back in for the IN() function):
if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
call_user_func_array(array($stmt, "bind_param"), array_merge($types, $selected));
但是如果你嘗試這個你會得到一個關于 mysqli_stmt::bind_param() 期望參數二通過引用傳遞的錯誤:
But if you try this you will get an error about mysqli_stmt::bind_param() expecting parameter two to be passed by reference:
警告:mysqli_stmt::bind_param() 的參數 2 應為引用，已給定值
Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given
這有點煩人，但很容易解決.要解決此問題，您可以使用以下函數:
This is kind of annoying but easy enough to work around. To work around that you can use the following function:
function refValues($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
它只是創建一個值數組，這些值是對 $selected 數組中值的引用.這足以讓 mysqli_stmt::bind_param() 開心:
It just creates an array of values that are references to the values in the $selected array. This is enough to make mysqli_stmt::bind_param() happy:
if ($stmt = $mysqli->prepare("DELETE FROM email_addresses WHERE email_addresses IN (?)")) {
call_user_func_array(array($stmt, "bind_param"), array_merge($types, refValues($selected)));
編輯
從 PHP 5.6 開始，您現在可以使用 ... 運算符來使這更簡單:
As of PHP 5.6 you can now use the ... operator to make this even simpler:
$stmt->bind_param($types, ...$selected);
這篇關于無法將內爆數組綁定到 mysql 準備好的語句中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！