PHP: Commands out of sync; you can't run this command now

近日凍仁使用 PROCEDURE 及動態 LIMIT 改寫某 PHP 網站的列出最後 10 筆資料功能，套上新程式碼後，再持續送出 query 就會跳「 Commands out of sync; you can't run this command now」的錯誤訊息，其 MySQL 文件的解釋如下:

18.2.8 Commands out of sync in client 錯誤

如果你在你的客戶代碼中得到 Commands out of sync; You can't run this command now，你正在以錯誤的次序調用客戶函數！

這可能發生，例如，如果你正在使用 mysql_use_result() 並且在你已經調用了 mysql_free_result() 之前試圖執行新查詢。如果你在 mysql_use_result()或mysql_store_result() 之間試圖執行返回數據的 2 個查詢，它也可能發生。

最後試出來的解法是補個手動關閉連線的函式 mysql_close() 即可解決。

 1 <?php
 2 
 3 # = 連結資料庫 =
 4 $dbhost = "localhost";          #資料庫網址或IP
 5 $dbusername = "db_user";        #資料庫帳號
 6 $dbuserpassword = "db_pwd";     #資料庫密碼
 7 $default_dbname = "db_name";    #資料庫名稱
 8 $connection = mysql_connect($dbhost, $dbusername, $dbuserpassword) or die("無法連結資料庫！");
 9 $db = mysql_select_db($default_dbname, $connection) or die("無法選擇資料庫");
10 
11 mysql_query("SET NAMES 'UTF8'");
12 mysql_query("SET CHARACTER SET UTF8");
13 mysql_query("SET CHARACTER_SET_RESULTS=UTF8'");
14 
15 # = Lab. =
16 $sql_last_10_sort="
17 DROP PROCEDURE IF EXISTS last_10_sort;
18 
19 DELIMITER //
20 
21 CREATE PROCEDURE last_10_sort()
22 BEGIN
23     DECLARE record_total, record_from int default 0;
24     SET @record_total = (select count(time) from tableA);
25     SET @record_from = (@record_total - 10);
26 
27     if @record_total < 10 then
28         select
29         time,
30         round(((`rowsA` + `rowsB`) / (select `rowsC` from tableB where bid = '1') * 100), 2) as 'lab'
31         from tableA order by time limit 10;
32     else
33         prepare stmt_last_10_sort from
34         " . "select `time`, round(((`rowsA` + `rowsB`) / (select rowsC from tableB where bid = '1') * 100), 2) as 'lab' from tableA order by time limit ?, ?" . ";
35         EXECUTE stmt_last_10_sort USING @record_from, @record_total;
36         DEALLOCATE PREPARE stmt_last_10_sort; 
37     end if;
38 END //
39 DELIMITER ;";
40 mysql_query($sql_last_10_sort);
41 
42 $sql_last_10_sort_value="call last_10_sort();";
43 $result_last_10_sort_value = mysql_query($sql_last_10_sort_value) or die(mysql_error());
44 
45 ......
46 
47 mysql_close($connection);
48 
49 ?>

[SQL on the Pastebin.com]

2013-01-22

補上使用 Sub-Query 的改寫的版本，做同樣的事情，但只需短短的六行即可，這樣似乎也比較省資源。

 1 /*
 2  * 1. select last 10 record and sort.
 3  * 2. calc (`rowsA` + `rowsB`) / `rowsC`.
 4  * 3. use Sub-Query.
 5  */
 6 
 7 SELECT `record_time`, `tmp` FROM (
 8 
 9         SELECT
10         `record_time`,
11         round(((`rowsA` + `rowsB`) / (SELECT rowsC FROM tableB WHERE bid = '1') * 100), 2) AS 'tmp'
12         FROM tableA ORDER BY `record_time` DESC LIMIT 10
13 
14 ) AS `lab_last_10_sort` ORDER BY `record_time` ASC;

[SQL on the Pastebin.com]

資料來源：
★18.2.8 Commands out of sync in client錯誤 | MySQL 中文參考手冊
★Commands out of sync; you can't run this command now - What's X- 點部落
★php - Commands out of sync; you can't run this command now - Stack Overflow
★PHP: mysqli::query - Manual