• 1
  • 新人请关照

navicat可正确执行sql语句,php中却报错Fatal error:non-object

如题,navicat可正确执行sql语句,php中却无法执行报错。我有一条sql语句,处理过期未支付订单,order_state=0 未支付,order_state=4过期

诡异的是,我在navicat和cmd命令行中执行,都没有任何错误可以执行。但是在php中却报错:Fatal error: Call to a member function execute() on a non-object

相关代码

sql语句:

UPDATE seat_table a
LEFT JOIN order_table b ON a.seat_id = b.seat_id
SET a.count = a.count + 1,
 b.order_state = '4'
WHERE
    a.count < 50
AND b.seat_id IN (
    SELECT
        c.seat_id
    FROM
        (
            SELECT
                seat_id
            FROM
                order_table
            WHERE
                DATE_ADD(
                    order_time,
                    INTERVAL 15 MINUTE
                ) < now()
            AND order_state = '0'
        ) c
)

php语句:

$stmt = $mysqli->prepare("update seat_table a LEFT JOIN order_table b on a.seat_id = b.seat_id set a.count = a.count + 1,b.order_state = '4' WHERE a.count < 50 and b.seat_id in (SELECT c.seat_id FROM (SELECT seat_id FROM order_table WHERE DATE_ADD( order_time,INTERVAL 15 MINUTE ) < now() AND order_state = '0') c)");
    

    if ($stmt->execute()) { // 更新成功
        $stmt->close();
    } else { // 更新失败
        die("500");
    }

运行结果

navicat中:执行正确

clipboard.png

小程序连接php后台则报错:

clipboard.png

然而更加奇怪的是,我将这句sql语句分为两段:

完整版:

update seat_table a LEFT JOIN order_table b on a.seat_id = b.seat_id set a.count = a.count + 1,b.order_state = '4' WHERE a.count < 50 and b.seat_id in (SELECT c.seat_id FROM (SELECT seat_id FROM order_table WHERE DATE_ADD( order_time,INTERVAL 15 MINUTE ) < now() AND order_state = '0') c)

分为两段:in()语句里面的单独提取出来
1、用in(1)代替是因为简便同时我第二部分查找出来的就是一条记录值为1

update seat_table a LEFT JOIN order_table b on a.seat_id = b.seat_id set a.count = a.count + 1,b.order_state = '4' WHERE a.count < 50 and b.seat_id in(1)

2、

SELECT c.seat_id FROM (SELECT seat_id FROM order_table WHERE DATE_ADD( order_time,INTERVAL 15 MINUTE ) < now() AND order_state = '0') c)

这两部分都能够在navicat和php中正确执行。所以可以排除我的php版本不支持sql语句中某些特性的原因。我搜索了很多相关问题均没有解决,或许是我疏忽了什么,但是能解决的话将帮我很大忙,非常感谢

阅读 752
评论
    3 个回答

    打印下$mysqli

      • 3.2k

      打印下$mysqli->error;看看是不是有报错信息

        show variables like '%log_error%'; 看下错误日志

          撰写回答

          登录后参与交流、获取后续更新提醒