"Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now" when updating to php8.2.5 from older PHP version

Question

I have a mysqli wrapper from PHP 5 written years ago, but it gives an error when trying to run on PHP 8.2.5. I have tried everything. Here are the two main functions:

global $db_obj, $db_driver;

function db_login() {
    global $db_obj;
    $res = @$db_obj->connect(DB_HOST, DB_USER, DB_PASS);
    if($res == false) {
        display_error("Could not connect to database.");
    }
    db_maybe_handle_error();
    db_select_db();
    db_set_charset();
}

function db_select_db($db = DB_NAME) {
    global $db_obj;
    @$db_obj->select_db($db);
    db_maybe_handle_error();
}

function db_set_charset($charset = 'utf8') {
    global $db_obj;
    @$db_obj->set_charset($charset);
    db_maybe_handle_error();
}

function db_error() {
    global $db_obj;
    return $db_obj->error();
}

function db_maybe_handle_error($r = null, $extra_info = "") {
    $e = db_error();
    if($e != '') {
       echo "mysql error: " . $e . " " . hsc($extra_info);
    }
    return $r;
}

function db_query($q) {

    $num_args = func_num_args();
    $arg_list = func_get_args();
    $r = null;

    global $db_obj;

    if($num_args > 1) {
        $stmt = $db_obj->stmt_init();
        if($stmt->prepare($q)) {
            $types = "";
            $refArr = array("");
            for($i = 1; $i < $num_args; $i++) {
                $type = gettype($arg_list[$i]) === "integer" ? "i" : "s";
                //$stmt->bind_param($type, $arg_list[$i]);
                // PHP <=5.2: Remove the ampersand
                // See http://php.net/manual/de/mysqli-stmt.bind-param.php
                $refArr[] = &$arg_list[$i];
                $types .= $type;
            }
            $refArr[0] = $types;
            $ref = new ReflectionClass('mysqli_stmt');
            $method = $ref->getMethod("bind_param");
            $method->invokeArgs($stmt, $refArr);
            $stmt->execute();
            $stmt->store_result();
            $r = $stmt;
        } else {
            db_maybe_handle_error(null, $q);
        }
    } else {
        $r = @$db_obj->query($q);
    }

    db_maybe_handle_error(null, $q);

    return $r;
}

function db_fetch($res, $use_numerical_indices = null) {
    global $db_obj;

    $use_numerical_indices = $use_numerical_indices === true ||
        ($use_numerical_indices === null && DB_DEFAULT_USE_NUMERICAL_INDEXES);

    if(is_array($res)) {
        return array_shift($res);
    }

    if($res instanceof mysqli_stmt) {
        $res->store_result();
        //$res->get_result();
        $variables = array();
        $data = array();
        $meta = $res->result_metadata();

        while($field = $meta->fetch_field()) {
            $variables[] = &$data[$field->name];
        }

        call_user_func_array(array($res, 'bind_result'), $variables);

        if(!$res->fetch()) {
            $res->close();
            return null;
        }
        $array = array();
        $i = 0;
        foreach($data as $k => $v) {
            $array[$k] = $v;
            if($use_numerical_indices) {
                $array[$i] = $v;
            }
            $i++;
        }
        db_maybe_handle_error($array);
    }
    $result_type = $use_numerical_indices ? $db_obj->BOTH : $db_obj->ASSOC;
    $r = @$db_obj->fetch_array($res, $result_type);
    return db_maybe_handle_error($r);
}

function db_num_rows($res) {
    global $db_obj;
    if(is_array($res)) {
        return count($res);
    }
    if($res instanceof mysqli_stmt) {
        return mysqli_stmt_num_rows($res);
    }
    $r = @$db_obj->num_rows($res);
    return db_maybe_handle_error($r);
}

function db_insert_id() {
    global $db_obj;
    $r = @$db_obj->insert_id();
    return db_maybe_handle_error($r);
}

And to get a row, a standard query can be done with:

$row = db_fetch(db_query("SELECT * FROM $table WHERE id=? LIMIT 1", $id));

The line with store_result() (in the db_fetch function) gives this error:

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

I have tried just about everything, including trying to add in $stmt->free_result() and $stmt->close() into different parts of the code segment.

The issue is that I'm specifically requesting help with rewriting this code segment using $stmt->free_request() or and $stmt->close() so that minimum code changes happen and can keep things in place as much as possible. Where would those go in the db_query and db_fetch functions?

Answer 1

The error you got means that you are trying to execute mysqli functions (and in turn MySQL commands) out of order. MySQL will reply with this error when it gets a command that is not expected at this moment.

You do not add free_result() or close() anywhere in your code. A properly structure code should NOT have any of these functions. They are only there to prevent memory leaks in spaghetti code.

To fix the error you must make sure that the operations are executed in the right sequence. But this code is way too messy to make heads or tails of it. You must rewrite it!

Try something simple like this:

function db_query($q, $params = []): mysqli_result
{
    global $db_obj; // DO NOT USE GLOBALS!!!

    return $db_obj->execute_query($q, $params);
}

function db_fetch(mysqli_result $res, $use_numerical_indices = null)
{
    $use_numerical_indices = $use_numerical_indices === true ||
        ($use_numerical_indices === null && DB_DEFAULT_USE_NUMERICAL_INDEXES);

    $result_type = $use_numerical_indices ? MYSQLI_NUM : MYSQLI_ASSOC;
    return $res->fetch_array($result_type);
}

Other functions need to be trimmed in a similar manner.

The actual problem is that you are calling store_result() twice. The easiest fix would be to remove one of them. But which one you remove is a mystery as this code doesn't make it clear which one, if any, is actually necessary. You could probably remove both, but then db_num_rows will not work.