問題描述
我遵循了一堆關(guān)于在準備好的語句中使用 SELECT 的不同示例，但沒有返回任何內(nèi)容.編輯我已將代碼更改為如下所示:
$date1 = 2012-01-01;$date2 = 2012-01-31;$sql_con = new mysqli('db', 'username', 'password', 'database');if($stmt = $sql_con->prepare("SELECT eventLogID FROM Country WHERE countryCode=? AND date BETWEEN ? AND ?")){$stmt->bind_param("sss", $country_code, $date1,$date2);$stmt->execute();$i=0;而 ($stmt-> fetch()){$stmt->bind_result($row[$i]);$i++;}$stmt->close();$sql_con->close();現(xiàn)在所有需要的條目，除了第一個，都被添加到 $row[].為什么不添加第一個條目?提前致謝！
EDIT 07/2015(問題自原始答案以來已被編輯，但基本原則相同)
從不 SELECT * 在生產(chǎn)環(huán)境中，它只會回來以奇怪的、不可預測的和看似無關(guān)的方式咬你.通過指定所需的列，您將確保列排序、數(shù)據(jù)類型、約束和各種其他元素從長遠來看不會給您帶來問題.
這個答案大部分仍然有效，所以我將其保留原樣，但主要的收獲是:使用 PDO，它可以用更干凈、更簡潔的方式完成您需要的 98% 的事情API 在同一后端.如果您需要更復雜的特定于 RDBMS 的 API，那么您將已經(jīng)了解您遇到的問題以及為什么需要 mysqli 等.
<小時>SELECT * 不適用于 MySQLi 準備好的語句.這是我推薦 PDO 的主要原因之一 - 以及綁定變量引用而不是綁定變量引用的荒謬要求參數(shù)值.
$stmt->bind_result($row);這不會將結(jié)果 row 綁定到變量，它只會綁定單個列.并且因為您使用了 SELECT *，它不會按照您的意愿行事.
如果您確實想在 PDO 上使用 MySQLi(正如我所說，我會推薦)有一些很好的示例說明如何在 SELECT *http://uk3.php.net/manual/en/mysqli-stmt.bind-result.php">bind_result() 手冊頁上的這個.
或者您可以指定要檢索的列:
$sql_con = new mysqli('db', 'username', 'password', 'database');if($stmt = $sql_con->prepare("SELECT name, countryCode FROM Country WHERE countryCode = ?")) {$stmt->bind_param("s", $country_code);$stmt->execute();$stmt->bind_result($name, $countryCode);而 ($stmt-> fetch()) {//因為 $name 和 $countryCode 是通過引用傳遞的，所以它們的值//每次迭代都會改變以反映當前行echo "";echo "name: $name
";回聲國家代碼:$國家代碼
"；echo "</pre>";}$stmt->close();
<小時>
EDIT 根據(jù)您的新代碼，您應該這樣做:
//$date1 將是 int(2010)，$date2 將是 int(1980) 因為你沒有//引用字符串！//$date1 = 2012-01-01;//$date2 = 2012-01-31;//連接數(shù)據(jù)庫$sql_con = new mysqli('db', 'username', 'password', 'database');//在這里檢查連接錯誤！//我們要執(zhí)行的查詢$sql = "選擇事件日志ID發(fā)件人國家哪里國家代碼 = ?和日期之間?和 ?"；//嘗試準備查詢?nèi)绻?($stmt = $sql_con->prepare($sql)) {//傳遞參數(shù)$date1 = '2012-01-01';$date2 = '2012-01-31';$stmt->bind_param("sss", $country_code, $date1, $date2);//執(zhí)行查詢$stmt->execute();如果 (!$stmt->errno) {//這里處理錯誤}//傳遞一個變量來保存結(jié)果//記住你綁定的是*列*，而不是行$stmt->bind_result($eventLogID);//循環(huán)結(jié)果并取入數(shù)組$logIds = array();而 ($stmt-> fetch()) {$logIds[] = $eventLogID;}//整理$stmt->close();$sql_con->close();//對結(jié)果做一些事情打印_r($logIds);} 別的 {//這里處理錯誤}Ive followed a bunch of different examples regarding using a SELECT in a prepared statement, but nothing is returned. EDIT I have changed my code a bit to look like this:
$date1 = 2012-01-01;
$date2 = 2012-01-31;
$sql_con = new mysqli('db', 'username', 'password', 'database');
if($stmt = $sql_con->prepare("SELECT eventLogID FROM Country WHERE countryCode=? AND date BETWEEN ? AND ?")){
$stmt->bind_param("sss", $country_code, $date1,$date2);
$stmt->execute();
$i=0;
while ($stmt->fetch()){
$stmt->bind_result($row[$i]);
$i++;
}
$stmt->close();
$sql_con->close();
Now all the desired entries, except for the first, are added to $row[]. Why isnt the first entry being added? Thanks in advance!
EDIT 07/2015 (question has been edited since original answer but underlying principles are the same)
Never SELECT * in a production environment, it will only come back to bite you in weird, unpredictable and seemingly unrelated ways. By specifying the columns you want, you will ensure that column ordering, data-type, constraint and all sorts of other elements won't cause you problems in the long run.
This answer is still mostly valid so I'll leave it here as-is, but the main take-away is: use PDO, it does 98% of the things you'll ever need with a much cleaner and more succinct API over the same back end. If you need a more complex RDBMS-specific API then you'll already understand the problems you have and why you need mysqli etc instead.
SELECT * doesn't work very well with MySQLi prepared statements. It's one of the major reasons I recommend PDO instead - that and the ridiculous requirement to bind variable references instead of values to the parameters.
$stmt->bind_result($row);
This is not binding the result row to a variable, it would just be binding a single column. And because you have used SELECT *, it doesn't do what you want it to.
If you do want to use MySQLi over PDO (which, as I say, I would recommend) there are a few good examples of how to SELECT * in the comments like this one on the bind_result() manual page.
Or you can just specify the columns you want to retrieve:
$sql_con = new mysqli('db', 'username', 'password', 'database');
if($stmt = $sql_con->prepare("SELECT name, countryCode FROM Country WHERE countryCode = ?")) {
$stmt->bind_param("s", $country_code);
$stmt->execute();
$stmt->bind_result($name, $countryCode);
while ($stmt->fetch()) {
// Because $name and $countryCode are passed by reference, their value
// changes on every iteration to reflect the current row
echo "<pre>";
echo "name: $name
";
echo "countryCode: $countryCode
";
echo "</pre>";
}
$stmt->close();
EDIT based on your new code, you should be doing this:
// $date1 will be int(2010), $date2 will be int(1980) because you didn't
// quote the strings!
//$date1 = 2012-01-01;
//$date2 = 2012-01-31;
// Connect to DB
$sql_con = new mysqli('db', 'username', 'password', 'database');
// Check for connection errors here!
// The query we want to execute
$sql = "
SELECT eventLogID
FROM Country
WHERE countryCode = ?
AND date BETWEEN ? AND ?
";
// Attempt to prepare the query
if ($stmt = $sql_con->prepare($sql)) {
// Pass the parameters
$date1 = '2012-01-01';
$date2 = '2012-01-31';
$stmt->bind_param("sss", $country_code, $date1, $date2);
// Execute the query
$stmt->execute();
if (!$stmt->errno) {
// Handle error here
}
// Pass a variable to hold the result
// Remember you are binding a *column*, not a row
$stmt->bind_result($eventLogID);
// Loop the results and fetch into an array
$logIds = array();
while ($stmt->fetch()) {
$logIds[] = $eventLogID;
}
// Tidy up
$stmt->close();
$sql_con->close();
// Do something with the results
print_r($logIds);
} else {
// Handle error here
}
這篇關(guān)于在準備好的語句中執(zhí)行 SELECT 查詢時遇到問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！