Login

xanabobana · 11-21-2022, 03:29 PM

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/4323...ommand-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!