调用存储过程Mysql时命令不同步 [英] Commands out of sync while calling stored procedure Mysql
本文介绍了调用存储过程Mysql时命令不同步的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个存储过程,我需要将记录分页(例如选择每个下一个n
记录)到第一个选择所有匹配记录的记录.
i have two store procedures, i needed to page the record (say select every next n
records) to the first one which select all matching records is.
CREATE PROCEDURE `trans_all`(IN varphone VARCHAR(15))
BEGIN
Select
loans.amt,
loans.date,
loans.pay_period,
borrower.phone As borrower_phone,
borrower.name As borrower_name,
lender.phone As lender_phone,
lender.name As lender_name,
From
loans Left Join
users borrower On borrower.id = loans.borrower_id Left Join
users lender On lender.id = loans.lender_id
Where
(lender.phone = varphone) or (borrower.phone = varphone);
END
然后我在php中获得计数;像这样
i then get the count in php ;like this
$result = $mysqli->query(sprintf("call trans_all('%s')",$phone));
$num_recs = $result->num_rows;
然后我需要选择精确的记录才能做到这一点
then i needed to select precise records to i do do this
$result = $mysqli->query(sprintf("call trans_history('%s','%s','%s')",$phone,$start,$limit));
$row = $result->fetch_assoc(); // this like gives the error Commands out of sync; you can't run this command now
第二个存储过程是
CREATE PROCEDURE `trans_history`(IN varphone VARCHAR(15), IN page INT, IN items INT)
BEGIN
Select
loans.amt,
loans.date,
loans.pay_period,
borrower.phone As borrower_phone,
borrower.name As borrower_name,
lender.phone As lender_phone,
lender.name As lender_name,
From
loans Left Join
users borrower On borrower.id = loans.borrower_id Left Join
users lender On lender.id = loans.lender_id
Where
(lender.phone = varphone) or (borrower.phone = varphone) LIMIT page , items;
END
什么可能导致此错误?
推荐答案
SP返回包含状态的第二个结果集.进行后续查询之前,需要使用next_result()
.
SPs return second resultset which contains the status. You need to use next_result()
before you make subsequent queries.
$result = $mysqli->query(sprintf("call trans_all('%s')",$phone));
$num_recs = $result->num_rows;
$result->close();
$mysqli->next_result(); // <- you need this before you make another query
//Then make call second SP
$result = $mysqli->query(sprintf("call trans_history('%s','%s','%s')",$phone,$start,$limit));
$row = $result->fetch_assoc();
$result->close();
$mysqli->next_result();
这篇关于调用存储过程Mysql时命令不同步的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文