PHP mysql error while calling multiple stored procedure

Question

First of all i explain my working environment. I m using Windows 7, CodeIgniter 3.0.2, PHP 5.2.0 and MySQL 5.5. I facing difficulty while call 2 different procedure one after another. From the first procedure I am getting room availability and based on that i am calling another procedure. The 1st procedure is running fine but while calling 2nd procedure it is showing error as :

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

CALL ConfirmRoom('40331411072018', 5,3, '2018-07-02', '2018-07-04', '27062018I10023',1)

i mention the code below:

//1st Procedure
$sql = $this->db->query("CALL CheckAvailbility(GuestHouse,RoomType,'StayDate','StayOutDate',room)");
if($sql->num_rows()>0) {
  $row = $sql->first_row();
  if($row->avail='Y') {
    //2nd Procedure
    $sql = $this->db->query("CALL ConfirmRoom('BookingId', GuestHouse,RoomType, 'StayDate', 'StayOutDate', 'GuestID',noroom)");
    if($sql->num_rows()>0) {
      //statement
    }
  }
}
else {
  //statement
}

Possible duplicate of [Commands out of sync; you can't run this command now](https://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now) — Hermanto, Jul 11 '18 at 08:54
Pointing at the obvious: `PHP 5.2.0`? Why? This is slow, insecure, unmaintained since 8 Years! Or was this a typo, and you're at 7.2.0? — Tom Regner, Jul 11 '18 at 09:15
the other solutions which are similar but none of them solved my problem. The other solution are for them who use "mysqli" but i am using "mysql" functions for connection and those function not worked for me. also i am using codeigniter framework and those functions not worked for me. — Deepu, Jul 11 '18 at 09:38
hello tom, actually i am working on an old project and adding some new feature to this project and i want to complete this project fast. that's why i didn't think about upgrading. i will upgrade that after completion of the project. — Deepu, Jul 11 '18 at 09:43

score 1 · Accepted Answer · answered Jul 11 '18 at 09:13

I ran into it before and this is how I solved it (might not be the best solution, but it works):

$this->db->close();
$this->db->initialize();

$test = $this->db->query("CALL sp_testProcedure(0, 1)");

$this->db->close();
$this->db->initialize();

$test2 = $this->db->query("CALL sp_testProcedure2(2, 3)"); 

$this->db->close();
$this->db->initialize();

score 0 · Answer 2 · answered Jul 11 '18 at 09:42

I think the problem lies in trying to use 2 database functions at the same time from the Object ($this). Instead of concatenating 'if statements' that are reliable on the other. Try and create a variable that stores a count of results outside of your 'if statements' to determine the desired response.

For example:

class Guest{

    private $_count= 0;

    public function count(){
        return $this->_count;
    }

}

//1st Procedure
$sql = $this->db->query("CALL 
    CheckAvailbility(GuestHouse,RoomType,'StayDate','StayOutDate',room)");
    if($sql->num_rows()>0) {
        $this->$_count++
        //have the $_count value auto increment to say the room is available
    }

if($this->count === 1) {
//2nd Procedure
$sql = $this->db->query("CALL ConfirmRoom('BookingId', GuestHouse,RoomType, 
    'StayDate', 'StayOutDate', 'GuestID',noroom)");
    if($sql->num_rows()>0) {
        //statement
        }
      }
else {
  //statement
}

If my example makes any sense...... I don't know the layout of your code but here's an example of using class variables to store results so the database can be called consecutively instead of simultaneously.

PHP mysql error while calling multiple stored procedure

2 Answers2