mysqli isn't returning the affected rows.

Question

Can someone tell me what I'm doing wrong? I cannot get the affected rows returned after a row has been deleted. The function always returns int(0) even though the delete action was preformed. I read where I needed to clear or close the results which I have done but it still does not work. A debug shows this error: Command out of sync. The manual states that to overcome this, $result->free must be used. In my case, it doesn't work.

    $res = db::query("CALL deleteUser('$phone')");

    if($res !== 1) {
        echo 'failed';
    } else {
        echo 'success';
    }

This is the part of the function where the query does its thing.

    if(self::$instance->query($query) === false){
        throw new exception("Failed");
    } return self::$instance->affected_rows;

Answer 1

When calling a stored procedure or function the int(0) is noting that the stored procedure exited without any errors. You'll need to select the number of rows affected in the stored procedure then have your stored procedure return the rows affected via an output variable.

Answer 2

When executing MySQL Stored Procedures in PHP, there are occasions where there is extra data being sent from the server that you have yet to retrieve. This will cause the "Commands out of Sync" error. I can't remember exactly why, but I read this on the MySQL forums a long time ago. You can clean up your result data with something like this:

while (self::$instance->next_result()) { }