0

Before this post is closed or I'm refered to an existing ref for this issue, here's a list of documentation I've referenced & other threads I've reviewed for this. I know I'm missing something glaring simple but I can not for the life of me see what's in front of me. I really am at my wits end right now

As I'm only allowed to post 2 links at a time currently, so I've chosen the first two that stress, I've looked this up!

http://www.artfulsoftware.com/infotree/tip.php?id=799&r=1 Commands out of sync; you can't run this command now

Before 1st query on 1st set

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)
}

After 1st query on 1st set

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)
}

Before 2nd query on 2nd set

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)
}

After 2nd query on 2nd set

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)
}

An example result set that outputs from my queries, there are generally 8 per Eng, but thought it best to shorten it

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 in question -

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) in question -

// 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;
}
Community
  • 1
  • 1
Raffers
  • 38
  • 7
  • For some reason didn't provide neither the relevant code nor didn't even mark the line where the error occurs so there is very little we can do – Your Common Sense Mar 15 '17 at 09:45
  • What exactly am I missing? I stated the error within my title which is visible within my vardumps of my mysqli object. I then provided the storedproc in question & the associated PHP code on how I'm handling each query? I've stripped down my php to the basics relating to mysqli, in an attempt to provide only what I thought was needed. – Raffers Mar 15 '17 at 10:41
  • The php is apart of a larger function most of it is just handling the output & adding it to an array. Outside of the function it's within a `foreach` loop passing settings to the function/storedproc. The error occurs on the 1st loop, but is only displayed within `["stat"]` until it comes round to the 2nd set of queries on the 2nd loop, when it error's out completely. – Raffers Mar 15 '17 at 10:44
  • I do suspect that it's something within the storedproc that's causing it, but I can't see how this is falling "out of sync" when I've not run any actual statements until the end after it's finished building the query. – Raffers Mar 15 '17 at 10:46

1 Answers1

0

So I've managed to resolve my issue, after going through this step by step & trying different things within some test code. I still don't quite understand why it works this way & not the other, as it's going through the same process. The only difference I can see is it would attempt until it evaluates as false & the inclusion of $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;
}
Raffers
  • 38
  • 7