CodeIgniter Forums
Commands out of sync - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forum-6.html)
+--- Forum: CodeIgniter 2.x (https://forum.codeigniter.com/forum-18.html)
+--- Thread: Commands out of sync (/thread-758.html)



Commands out of sync - debajyoti.saha09 - 01-14-2015

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


RE: Commands out of sync - Rufnex - 01-14-2015

This is a problem by your query logic

http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html


RE: Commands out of sync - skunkbad - 01-15-2015

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.


RE: Commands out of sync - mehmood sanjrani - 03-01-2016

(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){ }


RE: Commands out of sync - meSmashsta - 01-09-2017

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



RE: Commands out of sync - dimas - 03-21-2017

Is it possible to simulate the next_result() function with pdo driver?