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 — Hermanto
– Hermanto, Commented Jul 11, 2018 at 8: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? — user594138
– user594138, Commented Jul 11, 2018 at 9: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
– Deepu, Commented Jul 11, 2018 at 9: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
– Deepu, Commented Jul 11, 2018 at 9:43

Igor Ilic · Accepted Answer · 2018-07-11 09:13:40Z

1

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();

answered Jul 11, 2018 at 9:13

Igor Ilic

1,3681 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HarrisonWD · Accepted Answer · 2018-07-11 09:42:23Z

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.

Collectives™ on Stack Overflow

PHP mysql error while calling multiple stored procedure

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related