+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Commands out of sync; you can't run this command now [SOLVED] (/showthread.php?tid=84899)
Commands out of sync; you can't run this command now [SOLVED] - xanabobana - 11-21-2022
I'm trying to run a few queries in sequence and I keep getting the error Commands out of sync; you can't run this command now in my php logs.
I have read: https://stackoverflow.com/questions/43232250/codeigniter-commands-out-of-sync-you-cant-run-this-command-now, however I am not able to update /system/database/drivers/mysqli/mysqli_result.php
I've tried:
```
$this->db->reset_query();
$this->db->close();
$this->db->initialize();
$this->db->reconnect();
mysqli_next_result( $this->db->conn_id );
$query->free_result();
```
But they either give me the same error, or different errors which I will detail in the comments of my code.
The way my code is organized- I have a make_query method that takes a bunch of search options and figures out which tables to join and fields to search based on those. Sometimes, I just want to count results, sometimes I want all of the resulting data, sometimes I just want distinct values for certain fields or to group by certain fields. So I call make_query with my options, and then decide what to select afterwards. I have also been saving my query text to report, so that users can see what query is being run.
One interesting thing I have noted is that when I have no options set (ie there are no WHERE clauses in my SQL), I do not get this error and the multiple queries are able to run with no problem!
Here is my code:
Code:
//Get rows from tblPlots based on criteria set in options array
public function get_plot_data($options=array()){
$this->db->save_queries = TRUE;
log_message('debug','before make query 1 get plot data');
$this->make_query($options,'plot');
log_message('debug','after make query 1 get plot data');
$this->db->distinct();
//Now, set select to return only requested fields
if (!empty($options['fields']) and $options['fields']!="all" ){
//Because other tables could be joined, add table name to each select
array_walk($options['fields'], function(&$value, $key) { $value = 'tblPlots.'.$value;} );
$this->db->select($options['fields']);
}
if (!empty($options['limit']) and $options['limit']>0){
$this->db->limit($options['limit'], $options['offset']);
}
//Get the resulting data
$result=$this->db->get('tblProgram')->result();
$query_text = $this->db->last_query(); //tried removing this but didn't help
log_message('debug','query text '.$query_text);
$this->db->save_queries = FALSE;
//get the number of rows
//$this->db->reset_query();
//$this->db->close();
//$this->db->initialize();
//$this->db->reconnect();
//mysqli_next_result( $this->db->conn_id );
//$this->db->free_result(); //Call to undefined method CI_DB_mysqli_driver::free_result()
//$result->free_result(); //Call to a member function free_result() on array
log_message('debug','before make query 2');
$this->make_query($options,"plot");
$this->db->select('pkProgramID');
log_message('debug','before count results');
//this is where my code errors out trying to do the next step:
$count=$this->db->count_all_results('tblProgram');
}I'm not including the code for make_query because it is long and calls other functions, but it runs successfully the first time and outputs the SQL query I would expect. If it would be helpful, I can include that code as well.
I'm not sure how to correctly call free_result() given that the CI documentation only gives an example using query('SQL QUERY'), so I'm not sure how to use free result in my scenario? Maybe that's the issue?
Code:
$query2 = $this->db->query('SELECT name FROM some_table');
$query2->free_result();Thank you for any help!
RE: Commands out of sync; you can't run this command now - InsiteFX - 11-22-2022
Please Read:
stackoverflow - Why is mysqli giving a "Commands out of sync" error?
And
MySQLi - B.3.2.12 Commands out of sync
RE: Commands out of sync; you can't run this command now - xanabobana - 11-22-2022
OK, so I've tried everything in https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html (see above) but maybe I'm not calling those commands correctly? I've tried:
$result = $this->db->store_result();
$this->db->free_result();
also tried:
if ($result = $this->mysqli->store_result())
{
while ($row = $result->fetch_assoc())
{
$this->Data[$i][] = $row;
}
//mysqli_free_result($result); this didn't do anything
$this->mysqli->free_result($result) //this didn't either
}
also tried calling this after each query:
public function clearStoredResults(){
$db_id = $this->db->conn_id;
do {
if ($res = $this->db->call_function("store_result", $db_id)) {
$res->free();
}
} while ($this->db->call_function("more_results", $db_id) && $this->db->call_function("next_result", $db_id));
}
Also tried updating the _execute code in this system folder according to: https://stackoverflow.com/questions/3632075/why-is-mysqli-giving-a-commands-out-of-sync-error
Also tried after each query:
$this->free_result();
function free_result() {
while (mysqli_more_results($this->conn) && mysqli_next_result($this->conn)) {
$dummyResult = mysqli_use_result($this->conn);
if ($dummyResult instanceof mysqli_result) {
mysqli_free_result($this->conn);
}
}
}
Still no luck. Now I'm not even getting an error message, I just get my messages I've added myself using log_message. I get successfully logged messages after every line, up until I try to get a result using: $result=$this->db->get('tblProgram')->result(); Then the logs are blank and I eventually get a 500 Server Error (sometimes I then get repeated 500 server errors for minutes after trying to refresh the page)
I've also tried just writing out my query to the console, and not getting results using $result=$this->db->get('tblProgram')->result(); to make sure the query is correct. The query is coming back as a successful query I can run on the database.
Any other suggestions?
RE: Commands out of sync; you can't run this command now - InsiteFX - 11-22-2022
I found this YouTube video that may help.
Fix Commands out of Sync ERROR
RE: Commands out of sync; you can't run this command now - xanabobana - 11-23-2022
That worked, thank you!!!
RE: Commands out of sync; you can't run this command now - InsiteFX - 11-24-2022
Your welcome, some times it takes a little bit of effort to find the correct answer.
RE: Commands out of sync; you can't run this command now - rodrigoguariento - 12-13-2024
Try:
$this->db->close();
$this->db->initialize();
Or even:
$this->db->reconnect();
RE: Commands out of sync; you can't run this command now - InsiteFX - 12-13-2024
It's already been solved.