1 min read

PHP:关于mysqli连续预编译查询

mysqli的预编译查询,在用第二次使用的时候,会提示错误 比如

//错误代码
//查询1
$stmt = mysqli_prepare($mysqli,'SELECT msgid FROM message WHERE msgid = ? LIMIT 0,1'); 
mysqli_stmt_bind_param($stmt,"i",$msg_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $msgid);
if( mysqli_stmt_fetch($stmt) ) echo '查询成功';
//查询2
$stmt_isread = mysqli_prepare($mysqli,'UPDATE xx_msg SET isread=1 WHERE msgid=?'); 
mysqli_stmt_bind_param($stmt_isread,"i",$msg_id);
mysqli_stmt_execute($stmt_isread);

提示错误: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in … mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in …

确保SQL语句正确的情况下(单独执行都可以正常操作)。下面是正确执行姿势

//查询语句1
$stmt = mysqli_prepare($mysqli,'SELECT msgid FROM message WHERE msgid = ? LIMIT 0,1'); 
//查询语句2
$stmt_isread = mysqli_prepare($mysqli,'UPDATE xx_msg SET isread=1 WHERE msgid=?'); 

//开始第一段句查询(SELECT)
mysqli_stmt_bind_param($stmt,"i",$msg_id);
if (mysqli_stmt_execute($stmt))
    {
        mysqli_stmt_store_result($stmt);  //!!关键语句,如果不加会提示错误
        //错误提示:Commands out of sync; you can't run this command now
        //绑定查询语句1的返回结果
    mysqli_stmt_bind_result($stmt, $msgid);
    if (mysqli_stmt_fetch($stmt))
    {
        //开始查询语句2(UPDATE)
        mysqli_stmt_bind_param($stmt_isread,"i",$msg_id);
        if( mysqli_stmt_execute($stmt_isread))
        {
            //UPDATE没获取返回值..
        }
        else printf("Comment statement error: %s\n", mysqli_error($mysqli));
    }
    else printf("stmt statement error: %s\n", mysqli_error($mysqli));
}

以上是大体思路,仅供参考。

参考网址1(面向对象样式):PHP MySQLi and Multiple Prepared Statements 参考网址2:php.net:mysqli_stmt_store_result