問(wèn)題描述
我對(duì)以下語(yǔ)法錯(cuò)誤感到頭疼.我正在嘗試將內(nèi)爆數(shù)組綁定到準(zhǔn)備好的語(yǔ)句中，但出現(xiàn)以下語(yǔ)法錯(cuò)誤:
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 語(yǔ)法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對(duì)應(yīng)的手冊(cè)，了解在?"附近使用的正確語(yǔ)法.在第 1 行
這是我的代碼.誰(shuí)能看出我哪里出錯(cuò)了?
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();
?>
作為測(cè)試，我嘗試了:
print "('" . implode("', '", $selected) . "')";
哪個(gè)正確地給了我
('me@me.com', 'you@you.com')
推薦答案
讓我為您省去一些麻煩并告訴您您嘗試做的事情無(wú)論如何都行不通.您只將一個(gè)參數(shù)綁定到您的 IN() 函數(shù)調(diào)用.您認(rèn)為您正在傳遞一個(gè)逗號(hào)分隔的列表，但實(shí)際上您只傳遞了一個(gè)逗號(hào)分隔的字符串，該字符串被視為一個(gè)值.這意味著您將搜索具有'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".
要克服這個(gè)問(wèn)題，您需要:
To overcome this you need to:
- 動(dòng)態(tài)生成類型字符串
- 使用
call_user_func_array()綁定你的參數(shù)
您可以像這樣生成類型字符串:
You can generate the types string like this:
$types = str_repeat('s', count($selected));
所有這些都是創(chuàng)建一個(gè) s 的字符串，它的字符數(shù)與數(shù)組中的元素?cái)?shù)一樣多.
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() 綁定你的參數(shù)(注意我把括號(hào)放回 IN() 函數(shù)):
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));
但是如果你嘗試這個(gè)你會(huì)得到一個(gè)關(guān)于 mysqli_stmt::bind_param() 期望參數(shù)二通過(guò)引用傳遞的錯(cuò)誤:
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() 的參數(shù) 2 應(yīng)為引用，已給定值
Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given
這有點(diǎn)煩人，但很容易解決.要解決此問(wèn)題，您可以使用以下函數(shù):
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;
}
它只是創(chuàng)建一個(gè)值數(shù)組，這些值是對(duì) $selected 數(shù)組中值的引用.這足以讓 mysqli_stmt::bind_param() 開(kāi)心:
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 開(kāi)始，您現(xiàn)在可以使用 ... 運(yùn)算符來(lái)使這更簡(jiǎn)單:
As of PHP 5.6 you can now use the ... operator to make this even simpler:
$stmt->bind_param($types, ...$selected);
這篇關(guān)于無(wú)法將內(nèi)爆數(shù)組綁定到 mysql 準(zhǔn)備好的語(yǔ)句中的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！