PHP8.2.5升级后mysqli封装报Commands out of sync错误修复咨询

问题背景 #

我有一个多年前基于PHP 5编写的mysqli封装类，升级到PHP 8.2.5后出现错误：Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now，错误出现在db_fetch函数的store_result()代码行。尝试在代码不同位置添加$stmt->free_result()和$stmt->close()但问题未解决，希望以最小代码改动为前提，在db_query和db_fetch函数中正确放置这两个方法修复问题。

核心代码 #

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

标准查询调用示例 #

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

修复方案 #

问题根源 #

1. db_query和db_fetch中重复调用store_result()，导致结果集状态混乱
2. 结果集耗尽时未正确释放资源，残留的未处理结果集干扰后续操作
3. 处理完stmt结果后，代码继续执行普通结果集的fetch_array逻辑，引发类型不匹配问题

最小改动步骤 #

1. 修改db_query函数

移除execute()后的重复store_result()调用：

$stmt->execute();
// 移除该行：$stmt->store_result();
$r = $stmt;

2. 修改db_fetch函数

• 移除stmt分支中的store_result()调用
• 结果集耗尽时先释放结果集再关闭stmt
• 添加return $array终止函数，避免执行后续普通结果集逻辑：

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->free_result(); // 先释放结果集
        $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);
    return $array; // 添加该行，终止函数
}

3. 优化db_num_rows函数（可选）

确保stmt结果集已存储，避免行数统计错误：

if($res instanceof mysqli_stmt) {
    $res->store_result(); // 确保结果集已加载
    return mysqli_stmt_num_rows($res);
}

验证 #

修复后原调用方式无需改动，可正常执行且不会再出现"Commands out of sync"错误。

内容的提问来源于stack exchange，提问作者cricketplayer123