如题，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中：执行正确
小程序连接php后台则报错：
然而更加奇怪的是，我将这句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语句中某些特性的原因。我搜索了很多相关问题均没有解决，或许是我疏忽了什么，但是能解决的话将帮我很大忙，非常感谢
- 3 回答
- 0 关注
- 804 浏览
添加回答
举报
0/150
提交
取消