"Uncaught mysqli_sql_exception: The command is out of sync; you cannot run this command now" when updating to php8.2.5 from an older PHP version

Question

I have a mysqli wrapper for PHP 5 that I wrote years ago, but I get an error when trying to run it on PHP 8.2.5. I've tried everything. Here are the two main features:

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

To get a row, this can be done using a standard query:

$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: The command is out of sync; you cannot run this command now

I've tried almost everything, including trying to add $stmt->free_result() and $stmt->close() to different parts of the code.

The problem is that I'm specifically asking for help rewriting this code snippet using $stmt->free_request() or $stmt->close() so minimal code changes occur and It can be left as untouched as possible. Where will these go in the db_query and db_fetch functions?

Answer 1

The error you receive means that you are trying to execute mysqli functions (and therefore MySQL commands) out of order. MySQL replies with this error when it receives a command it does not expect at this time.

You do not add free_result() or close() anywhere in your code. Properly structured code should not have any of these features. They are just to prevent memory leaks in spaghetti code.

To fix this error, you must ensure that the operations are performed in the correct order. But this code is too confusing to figure out what's going on. You have to 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 pruned in a similar manner.

The actual problem is that you are calling store_result() twice. The easiest solution is to delete one of them. But which one to remove is a mystery, since this code doesn't make it clear which one (if any) is actually necessary. You can probably delete both, but db_num_rows won't work.