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

Question

I applied all the possible answers but still same problem. also tried

$this->db->reconnect();

there is no problem in my query

MyCode:

public function GetdistributorsDetails($username){
        $sql = "SELECT u.FirstName, u.Email, u.Telephone, u.MobileNumber, u.AlternateMobileNumber, ud.Address1, ud.Pincode,ud.City,s.Statename FROM users u JOIN userdetails ud ON ud.UserId = u.UserId JOIN states s ON s.StateId = ud.StateId WHERE u.Username = ? ";
        $result = $this->db->query($sql,array($username));
        return $result->result_array();
    }

Did you maybe make a query that uses a stored procedure earlier in the shown code? — DFriend, Apr 05 '17 at 13:30
possible duplicate of http://stackoverflow.com/questions/3632075/mysqli-giving-commands-out-of-sync-error-why — qwertzman, Apr 05 '17 at 15:42
@qwertzman, Not really a duplicate as the OP is dealing with the database abstraction layer of codeigniter. That said, it is likely the problem and has been know to occur in CI when a stored procedure has just (or recently) been used. However, it may be a duplicate of http://stackoverflow.com/questions/9647699/commands-out-of-sync-you-cant-run-this-command-now-while-calling-stored-proced and or http://stackoverflow.com/questions/9821377/command-out-of-sync-when-i-run-stored-procedure-using-mysqli-driver — DFriend, Apr 05 '17 at 16:52
yes i am using SP and it returns proper result but after that i need to fire this query so what's the problem? — Juned Ansari, Apr 07 '17 at 04:47
Can any body explain why this happening and after using mysqli_next_result( $this->db->conn_id ) it sorted out. But why ? — Fawwad, Nov 01 '18 at 11:35

Juned Ansari · Accepted Answer · 2017-04-07T06:03:00.633

23

add following code into /system/database/drivers/mysqli/mysqli_result.php

 function next_result()
 {
     if (is_object($this->conn_id))
     {
         return mysqli_next_result($this->conn_id);
     }
 }

then in model when you call Stored Procedure

$query    = $this->db->query("CALL test()");
$res      = $query->result();

//add this two line 
$query->next_result(); 
$query->free_result(); 
//end of new code

return $res;

edited Apr 07 '17 at 06:03

answered Apr 07 '17 at 05:54

Juned Ansari

5,035
7
56
89

1

This is a beautiful fix to a problem I've been trying to solve for a while now. Thank you Juned, really. I did need to add a check for next results to avoid potential errors. – DanimalReks Mar 20 '19 at 20:47
Just update https://stackoverflow.com/a/65628126/10455858 – Shamim Shaikh Jan 08 '21 at 11:28

score 6 · Answer 2 · answered Jul 14 '17 at 21:33

6

you can use this after call

mysqli_next_result( $this->db->conn_id );

answered Jul 14 '17 at 21:33

Sudik Maharana

704
1
7
16

1

Thank you for providing such awesome solution. Kindly elaborate the above mentioned line of code if posssible. – Raham Dec 22 '21 at 04:47

Deepu Bhasin · Answer 3 · 2021-04-03T06:41:24.357

2

Here is example if you don't want change any thing in mysqli drivers files 

$sql = "CALL procedureName(IntParam,'VarcharParm1','VaracharParm2','VarcharParm3');";
$query = $this->db->query($sql);
mysqli_next_result( $this->db->conn_id );
$query->free_result();

these are important line place after to remove the error 
mysqli_next_result( $this->db->conn_id );
$query->free_result();

edited Apr 03 '21 at 06:41

answered Apr 02 '21 at 16:55

Deepu Bhasin

91
6

This saves my day. Please explain how this is working. – MdFarzan Nov 14 '22 at 06:49

score 0 · Answer 4 · answered Feb 10 '18 at 10:09

0

Just use this one if you use multiple query make in same function: $this->db->close();

answered Feb 10 '18 at 10:09

codekman

71
3

score 0 · Answer 5 · answered Aug 02 '19 at 19:40

If your stored procedure returns more than one result, try to add this code between the queries:

$storedProcedure = 'CALL test(inputParam, @outputParam)';
$this->db->query($storedProcedure);    

$conn = $this->db->conn_id;
do {
    if ($result = mysqli_store_result($conn)) {
        mysqli_free_result($result);
    }
} while (mysqli_more_results($conn) && mysqli_next_result($conn));

$sql = 'SELECT @outputParam;';
$this->db->query($sql);

score 0 · Answer 6 · answered Jan 08 '21 at 11:28

Just add next_result function in system\database\drivers\mysqli\mysqli_result.php file

public function next_result(){
    if(is_object($this->conn_id) && mysqli_more_results($this->conn_id)){
        return mysqli_next_result($this->conn_id);
    }
}

To call query function

  $result = $this->db->query("CALL {$Name}({$Q})",$Param);
  $result->next_result();
  return  $result;

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

6 Answers6

Linked