Commands out of sync; you can't run this command now
You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements; it's the opposite for vanilla mysql_query
). You can either fetch the first one into an array and loop through that, or tell mysqli to buffer the queries (using $stmt->store_result()
).
See here for details.
MySqli Commands out of sync; you can't run this command now
If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.
From here:
http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html
Update
If you make the a variable for the query and paste the variable directly into something like MySQL Workbench you can check the syntax prior to execution.
<?php
function myConnection(){
$myConnection = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
return $myConnection;
}
function register_user($register_data) {
array_walk($register_data, 'array_sanitize');
//Make the array readable and seperate the fields from data
$fields = '`' . implode('`, `', array_keys($register_data)) . '`';
$data = "'" . implode("', '", $register_data) . "'";
//Insert the data and email an activation email to the user
$query = "INSERT INTO `members` ($fields) VALUES ($data)";
$myNewConnection = myConnection();
if($result = mysqli_query($myNewConnection, $query)){
email($register_data['mem_email'], 'Activate your account', "Hello " . $register_data['mem_first_name'] . ",\n\nThank you for creating an account with H Fencing. Please use the link below to activate your account so we can confirm you identity:\n\nhttp://blah.blah.co.uk/activate.php?mem_email=" . $register_data['mem_email'] . "&email_code=" . $register_data['email_code'] . "\n\n - David & Jay ");
mysqli_free_result($result);
return ("Success");
} else {
echo $query;
die(mysqli_error($myNewConnection));
}
}
?>
Why I am getting the Error Commands out of sync; you can't run this command now
There are result set pending from the query:
mysqli_multi_query($connection,$query);
You need to use/store result before you can proceed with next query after:
Since you look like you don't really care about the first result set, do this after the multi query..
do
{
$result = mysqli_store_result($connection);
mysqli_free_result($result);
}while(mysqli_next_result());
Another alternative is to close the connection and starts it again..
mysqli_close($connection);
$connection = mysqli_connect("localhost","username","password","tbl_msgs");
It all depends on your requirements.
Why is mysqli giving a Commands out of sync error?
The MySQL client does not allow you to execute a new query where there are still rows to be fetched from an in-progress query. See Commands out of sync in the MySQL doc on common errors.
You can use mysqli_store_result()
to pre-fetch all the rows from the outer query. That will buffer them in the MySQL client, so from the server's point of view your app has fetched the full result set. Then you can execute more queries even in a loop of fetching rows from the now-buffered outer result set.
Or you mysqli_result::fetch_all()
which returns the full result set as a PHP array, and then you can loop over that array.
Calling stored procedures is a special case, because a stored procedure has the potential for returning multiple result sets, each of which may have its own set of rows. That's why the answer from @a1ex07 mentions using mysqli_multi_query()
and looping until mysqli_next_result()
has no more result sets. This is necessary to satisfy the MySQL protocol, even if in your case your stored procedure has a single result set.
PS: By the way, I see you are doing the nested queries because you have data representing a hierarchy. You might want to consider storing the data differently, so you can query it more easily. I did a presentation about this titled Models for Hierarchical Data with SQL and PHP. I also cover this topic in a chapter of my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
Here is how to implement mysqli_next_result()
in CodeIgnitor 3.0.3:
On line 262 of system/database/drivers/mysqli/mysqli_driver.php
change
protected function _execute($sql)
{
return $this->conn_id->query($this->_prep_query($sql));
}
to this
protected function _execute($sql)
{
$results = $this->conn_id->query($this->_prep_query($sql));
@mysqli_next_result($this->conn_id); // Fix 'command out of sync' error
return $results;
}
This has been an issue since 2.x. I just updated to 3.x and had to copy this hack over to the new version.
MySQL Error - Commands out of sync; you can't run this command now
Got the Answer! It seems like codeigniter's mysql driver has bugs handling stored procedures.
I changed the drivers from mysql to mysqli in the config/database file by changing
$db['default']['dbdriver'] = 'mysql';
to
$db['default']['dbdriver'] = 'mysqli';
Post that i modified the system/database/drivers/mysqli/mysqli_result.php file and added the below function
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
and modified the model as below
$db = $this->load->database('mailbox',TRUE);
$qry_res = $db->query('Call circle_pending_p()');
echo $db->_error_message();
$res = $qry_res->result_array();
$qry_res->next_result();
$qry_res->free_result();
if (count($res) > 0) {
return $res;
} else {
return 0;
}
This solved the problem!
PHP Commands Out of Sync error
In mysqli::query If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()
When calling multiple stored procedures, you can run into the following error: "Commands out of sync; you can't run this command now".
This can happen even when using the close() function on the result object between calls.
To fix the problem, remember to call the next_result() function on the mysqli object after each stored procedure call. See example below:
<?php
// New Connection
$db = new mysqli('localhost','user','pass','database');
// Check for errors
if(mysqli_connect_errno()){
echo mysqli_connect_error();
}
// 1st Query
$result = $db->query("call getUsers()");
if($result){
// Cycle through results
while ($row = $result->fetch_object()){
$user_arr[] = $row;
}
// Free result set
$result->close();
$db->next_result();
}
// 2nd Query
$result = $db->query("call getGroups()");
if($result){
// Cycle through results
while ($row = $result->fetch_object()){
$group_arr[] = $row;
}
// Free result set
$result->close();
$db->next_result();
}
else echo($db->error);
// Close connection
$db->close();
?>
I hope this will help
I get: Commands out of sync; you can't run this command now?
This is what to do, if I've understood the other answer correctly:
if ($link->multi_query($sql) === TRUE) {
// the loop below is the fix. It just cycles through the results, ignoring them
while(mysqli_more_results($link)) {
mysqli_next_result($link);
// or $link->next_result();
}
//...
mysqli_multi_query - Commands out of sync; you can't run this command now
I just found the answer in the PHP manual:
WATCH OUT: if you mix
$mysqli->multi_query
and$mysqli->query
, the
latter(s) won't be executed!BAD CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
$mysqli->query(" SQL statement #1 ; ") // not executed!
$mysqli->query(" SQL statement #2 ; ") // not executed!
$mysqli->query(" SQL statement #3 ; ") // not executed!
$mysqli->query(" SQL statement #4 ; ") // not executed!The only way to do this correctly is:
WORKING CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
I just insert this code after mysqli_multi_query()
:
while(mysqli_next_result($connect)){;}
Related Topics
Pcntl Runs the Same Code Several Times, Assistance Required
Woocommerce New Order Action - Get Order Information
Symfony: Change Database Dynamically
Why Does 'Intval(19.9 * 100)' Equal '1989'
Key of Null Variable Equals Null Not Error
How to Detect If a User Uploaded a File Larger Than Post_Max_Size
Does PHP Feature Short Hand Syntax for Objects
Can Not Increase File Upload Size Wamp
Laravel 5 - Session Doesn't Work
Count Word Frequency in a Text
Php: How to Load the Content of a Web Page into a Variable
Import Class Conditionally with the Keyword 'Use'
PHP Require_Once Failed to Open Stream Permission Denied
Why Don't PHP and JavaScript's Timestamps Match
Why Are My PHP Tags Converted to HTML Comments
Implementing Oauth2 Login, Fatal Error: Class 'Google_Service' Not Found