CodeIgniter Forums

Full Version: Commands out of sync
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I am calling two models in a controller function. And in these two model I am calling two Procedures to get the data from the database. But I am getting the error in the controller.

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

My code is like below

This is my controller code

  $this->load->model('model_get_fixed_data');
           $degrees=$this->model_get_fixed_data->get_degree_dropdown();
 $this->load->model('model_users');
           $doctor_data=$this->model_users->get_doctor_details_by_id($this->session->userdata('doctor_id'));


This is my model code

 function get_degree_dropdown(){
       $query= $this->db->query("call getDegree_master()");
       return $query->result();
   }

public function get_doctor_details_by_id($doctor_id)
   {
       $query= $this->db->query('call get_DoctorDetaisById('.$doctor_id.')');
       return $query->result();
   }

If anyone have faced this kind of problem. Please help me.

Thnks & regards
This is a problem by your query logic

http://dev.mysql.com/doc/refman/5.0/en/c...-sync.html
Hack:

/system/database/drivers/mysqli/mysqli_result.php

CodeIgniter does not currently allow more than one stored procedure to be run during the same request.

The following method was added to the CI_DB_mysqli_result class:

Code:
/**
* Read the next result
*
* @return  null
*/  
function next_result()
{
    if (is_object($this->conn_id))
    {
        return mysqli_next_result($this->conn_id);
    }
}

// --------------------------------------------------------------------

Then you simply call next_result() between calls to your stored procedures. This assumes you're using MySQLi.
(01-15-2015, 03:21 AM)skunkbad Wrote: [ -> ]Hack:

/system/database/drivers/mysqli/mysqli_result.php

CodeIgniter does not currently allow more than one stored procedure to be run during the same request.

The following method was added to the CI_DB_mysqli_result class:

Code:
/**
* Read the next result
*
* @return  null
*/  
function next_result()
{
    if (is_object($this->conn_id))
    {
        return mysqli_next_result($this->conn_id);
    }
}

// --------------------------------------------------------------------

Then you simply call next_result() between calls to your stored procedures. This assumes you're using MySQLi.

i have done this but still facing same problem

here is my retrieving data code

foreach($result->next_result() as $values){ }
What you're doing is calling more than 1 procedures which is a multi query equivalent of php I think, and according to the documentation of mysql:

Quote: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.

That's the solution for C, and the equivalent for php is to call mysqli_next_result function which takes the mysqli connection as a parameter. You have to call that in between your procedure calls. Like this:

PHP Code:
function get_degree_dropdown(){
 
      $query$this->db->query("call getDegree_master()");
 
      mysqli_next_result($this->db->conn_id);
 
      return $query->result();
 
  }

public function 
get_doctor_details_by_id($doctor_id)
 
  {
 
      $query$this->db->query('call get_DoctorDetaisById('.$doctor_id.')');
 
      mysqli_next_result($this->db->conn_id);
 
      return $query->result();
 
  

Also, it's a good practice to free your result resource like this:

PHP Code:
$query->free_result(); 
But don't forget to save the data first before freeing.
PHP Code:
$query$this->db->query('call get_DoctorDetaisById('.$doctor_id.')');
...
$data $query->result();
$query->free_result();
return 
$data
Is it possible to simulate the next_result() function with pdo driver?