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 文件的解釋如下:
最後試出來的解法是補個手動關閉連線的函式 mysql_close() 即可解決。
補上使用 Sub-Query 的改寫的版本,做同樣的事情,但只需短短的六行即可,這樣似乎也比較省資源。
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 ?>
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;
資料來源:
★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
留言
張貼留言
喜歡這篇文章嗎?歡迎在底下留言讓凍仁知道。😉