在这篇文章关闭之前,或者我指的是此问题的现有参考文献,以下是我引用过的文档列表&我对此进行了评论的其他线程。我知道我错过了一些简单的东西,但我一生无法看到我面前的事。我现在真的处于我的智慧
由于目前我只能一次发布2个链接,所以我选择了前两个压力,我已经查找了!
http://www.artfulsoftware.com/infotree/tip.php?id=799&r=1命令不同步;您现在无法运行此命令
在第1个查询之前
object(mysqli)#1 (18) {
["affected_rows"]=>
int(-1)
["client_info"]=>
string(6) "5.5.52"
["client_version"]=>
int(50552)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(0)
["error"]=>
string(0) ""
["field_count"]=>
int(2)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(23) "5.5.43-0ubuntu0.12.04.1"
["server_version"]=>
int(50543)
["stat"]=>
string(147) "Uptime: 4702713 Threads: 3 Questions: 19875140 Slow queries: 504 Opens: 30587 Flush tables: 1 Open tables: 128 Queries per second avg: 4.226"
["sqlstate"]=>
string(5) "00000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(1143580)
["warning_count"]=>
int(0)
}
在第1个查询之后
object(mysqli)#1 (18) {
["affected_rows"]=>
int(10)
["client_info"]=>
string(6) "5.5.52"
["client_version"]=>
int(50552)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(0)
["error"]=>
string(0) ""
["field_count"]=>
int(2)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(23) "5.5.43-0ubuntu0.12.04.1"
["server_version"]=>
int(50543)
["stat"]=>
string(52) "Commands out of sync; you can't run this command now"
["sqlstate"]=>
string(5) "HY000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(1143586)
["warning_count"]=>
int(0)
}
在第二套2nd查询之前
object(mysqli)#1 (18) {
["affected_rows"]=>
int(0)
["client_info"]=>
string(6) "5.5.52"
["client_version"]=>
int(50552)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(2014)
["error"]=>
string(52) "Commands out of sync; you can't run this command now"
["field_count"]=>
int(2)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(23) "5.5.43-0ubuntu0.12.04.1"
["server_version"]=>
int(50543)
["stat"]=>
string(52) "Commands out of sync; you can't run this command now"
["sqlstate"]=>
string(5) "HY000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(1143613)
["warning_count"]=>
int(0)
}
在第二套2nd查询之后
object(mysqli)#1 (18) {
["affected_rows"]=>
int(0)
["client_info"]=>
string(6) "5.5.52"
["client_version"]=>
int(50552)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(2014)
["error"]=>
string(52) "Commands out of sync; you can't run this command now"
["field_count"]=>
int(2)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(23) "5.5.43-0ubuntu0.12.04.1"
["server_version"]=>
int(50543)
["stat"]=>
string(52) "Commands out of sync; you can't run this command now"
["sqlstate"]=>
string(5) "HY000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(1143626)
["warning_count"]=>
int(0)
}
从我的查询中输出的示例结果集,通常每英语8个,但认为最好缩短它
Array
(
[1st Set] => Array
(
[Eng1] => Array
(
[Live] => 24
[Score] => 47
[Res] => 102
)
[Eng2] => Array
(
[Live] => 22
[Score] => 39
[Res] => 79
)
[Eng3] => Array
(
[Live] => 24
[Score] => 33
[Res] => 54
)
[Eng4] => Array
(
[Live] => 11
[Score] => 31
[Res] => 46
)
[Eng5] => Array
(
[Live] => 10
[Score] => 25
[Res] => 51
)
)
[2nd Set] =>
)
有问题的storedproc-
CREATE DEFINER=`chris_test`@`%` PROCEDURE `countSingleStat`(
IN statTableLoc VARCHAR(20),
IN statCountLoc VARCHAR(20),
IN statIDLoc VARCHAR(20),
IN teamID INT(2),
IN singleTeam INT(1),
IN rangeStart DATE,
IN rangeEnd DATE,
IN dateRequired INT(1),
IN dataHandling INT(1))
countStat:BEGIN
-- Set passed attributes as uservar so these can be used with concat
SET @statTableLoc = statTableLoc;
SET @statCountLoc = statCountLoc;
SET @statIDLoc = statIDLoc;
SET @rangeStart = rangeStart;
SET @rangeEnd = rangeEnd;
SET @teamID = teamID; -- Excluding or requesting a specific team
-- Example completed query
-- SELECT e.displayname, SUM(r.sessions) AS totalres FROM rescue r JOIN engineers e ON e.id=r.engid
-- WHERE r.date BETWEEN '2017-01-06' AND '2017-01-07'
-- AND NOT e.id=0
-- GROUP BY e.id
-- ORDER BY totalres DES
-- start of query required to find single stat
-- datahandling specifies how the data is being present, currently only string & sum listed
CASE dataHandling
WHEN 0 THEN
SET @qry = concat('SELECT e.displayname, ', @statTableLoc ,'.', @statCountLoc ,' AS string');
WHEN 1 THEN
SET @qry = concat('SELECT e.displayname, sum(', @statTableLoc ,'.', @statCountLoc ,') AS total');
END CASE;
-- Is a date start/end required for query.
CASE dateRequired
WHEN 0 THEN
SET @qry = concat(@qry,' FROM ', @statTableLoc ,' JOIN engineers e ON e.id=', @statTableLoc ,'.engid WHERE');
WHEN 1 THEN
SET @qry = concat(@qry,' FROM ', @statTableLoc ,' JOIN engineers e ON e.id=', @statTableLoc ,'.engid WHERE ', @statTableLoc ,'.date BETWEEN '', @rangeStart ,'' AND '', @rangeEnd ,'' AND');
END CASE;
-- Has a single team been requested or a team been excluded from results
CASE singleTeam
WHEN 0 THEN
SET @qry = concat(@qry,' NOT e.teamid=', @teamID ,' GROUP BY e.id ORDER BY');
WHEN 1 THEN
SET @qry = concat(@qry,' e.teamid=', @teamID ,' GROUP BY e.id ORDER BY');
END CASE;
CASE dataHandling
WHEN 0 THEN
SET @qry = concat(@qry,' string DESC');
WHEN 1 THEN
SET @qry = concat(@qry,' total DESC');
END CASE;
PREPARE statsCount FROM @qry;
EXECUTE statsCount;
DROP PREPARE statsCount;
END
php(5) -
// Set query for specifed stat & it's assoicated settings. Request between specific dates if required
$qry = "CALL SingleStat('". $statsTable ."','". $statsRequired ."','". $statTableEngID ."',". $teamID .",". $excludeORinclude .",'". $dateRangeStart ."','". $dateRangeEnd ."',". $dateRequired .",". $dataHandling .")";
try
{ // run query
$result = $db->query($qry);
echo $db->error;
}
catch (Exception $e)
{
echo $db->error;
vardumpCount($e);
}
// do we have results?
if($result->num_rows > 0)
{
// loop through results
while ($resultsArray = $result->fetch_assoc())
{
/* LOTS OF CODE GOES HERE */
}
#####
// do we have any results remaining to be processed.
if ($db->next_result())
{
if ($results = $db->use_result())
{
while ($row = $result->fetch_assoc())
{
echo "<pre>";
print_r($row);
echo "</pre>";
echo "</br>-----------</br></br>";
}
}
}
#####
return $engStatArray;
}
,因此在逐步完成此问题之后,我已经设法解决了我的问题。在某些测试代码中尝试不同的事情。我仍然不太明白为什么它以这种方式工作&amp;不是另一个,因为它正在经历相同的过程。我看到的唯一区别是它将尝试直到评估为false&amp;包含$db->more_results()
// Set query for specifed stat & it's assoicated settings. Request between specific dates if required
$qry = "CALL SingleStat('". $statsTable ."','". $statsRequired ."','". $statTableEngID ."',". $teamID .",". $excludeORinclude .",'". $dateRangeStart ."','". $dateRangeEnd ."',". $dateRequired .",". $dataHandling .")";
try
{ // run query
$result = $db->query($qry);
echo $db->error;
}
catch (Exception $e)
{
echo $db->error;
vardumpCount($e);
}
// do we have results?
if($result->num_rows > 0)
{
while($db->more_results() && $db->next_result())
{
// loop through results
while ($resultsArray = $result->fetch_assoc())
{
/* LOTS OF CODE GOES HERE */
}
}
return $engStatArray;
}