command out of sync / clearing the result set.

skygremlin · June 5, 2013

I have another question on my MySqli DB queries and using PHP. I’m trying to run 2 queries, 1 at page load, and another from a form submit. I’m getting the “Commands out of sync; you can't run this command now” Which from what I’ve read is because I haven’t cleared the result set, in order to run another query. My issue is I’m trying to clear the result set (or at least think I’m trying) but still getting the same error. Below is my code. I know there are other issues (should be using prepared statements) but this is just a basic query a issues list on a project.

Any advice would be greatly appreciated. If I have either one or the other commented out the page will load. Just not one after the other..

This part loads a simple drop down menu from a table in the DB

<?php
                
                //Get the a listing of all the pages on the site from the pages table
               $page_set = mysqli_query($connection, "call all_pages");
                    if (!$page_set) {
                        printf("Error in getting the pages: %s\n", mysqli_error($connection));
                        exit();
                    }
                
                //Populate the Pages drop down menu
                echo "<select name='page'>";            
                        //Transfer the result to an array
                        while ($row = mysqli_fetch_array($page_set)){
                            $page_list[] = $row;
                        }

                        //Loop thru the array and get the 'page name'
                        foreach($page_list as $row){ 
                            echo "<option value='" . $row['page_name'] . "'>" . $row['page_name'] . "</option>";
                        }
                echo "</select>";
                
                //Free result set
                mysqli_free_result($page_set);
                
             ?>
            <br /><br /> 
                <input type="submit" name="submit"/>
            </form>

This uses one of the values from the drop down menu to query the DB and get a listing of all the issues associated to value (page)

<?php //if(!empty($message)){
         /* $page = "home.php"; */
              //$issues = mysqli_query($connection, "call page_issues(\"ser_ms.php\")");
                $issues = mysqli_query($connection, "call page_issues('$page')");
                if (!$issues) {
                    printf("Error: %s\n", mysqli_error($connection));
                    exit();
               }

Simple table is printed below..

Error:

Commands out of sync; you can't run this command now

thank you

mikosiko · June 5, 2013

test this... after your first while loop..

//Transfer the result to an array
while ($row = mysqli_fetch_array($page_set)){
$page_list[] = $row;
}

add this lines:

   do {
         mysqli_next_result($page_set);
   }  while (mysqli_more_results($page_set));

alternatively an IF should have the same effect:

IF (mysqli_more_results($page_set)) {
  mysqli_next_result($page_set);
}

skygremlin · June 5, 2013

Same response "Error: Commands out of sync; you can't run this command now " I tried both the do and the IF statements..Current Code: <?php //Get the a listing of all the pages on the site from the pages table $page_set = mysqli_query($connection, "call all_pages"); if (!$page_set) { printf("Error in getting the pages: %s\n", mysqli_error($connection)); exit(); } //Populate the Pages drop down menu echo ""; //Transfer the result to an array while ($row = mysqli_fetch_array($page_set)){ $page_list[] = $row; } do { mysqli_next_result($page_set); } while (mysqli_more_results($page_set)); /*//Loop thru the array and get the 'page name' while ($page = mysqli_fetch_array($page_set)){ echo "" . $page['page_name'] . ""; }*/ //Loop thru the array and get the 'page name' foreach($page_list as $row){ echo "" . $row['page_name'] . ""; } echo ""; //Free result set mysqli_free_result($page_set); ?>

skygremlin · June 5, 2013

Sorry that's a little hard to read - Let me try this again..

Same response "Error: Commands out of sync; you can't run this command now " I tried both the do and the IF statements..

<form action="issues_by_page_2.php" method="post"> 
            <?php
                
                //Get the a listing of all the pages on the site from the pages table
               $page_set = mysqli_query($connection, "call all_pages");
                    if (!$page_set) {
                        printf("Error in getting the pages: %s\n", mysqli_error($connection));
                        exit();
                    }
                
                //Populate the Pages drop down menu
                echo "<select name='page'>";            
                        //Transfer the result to an array
                        while ($row = mysqli_fetch_array($page_set)){
                            $page_list[] = $row;
                        }
                        
                        IF (mysqli_more_results($page_set)) {
                            mysqli_next_result($page_set);
                        }

                        /*//Loop thru the array and get the 'page name' 
                        while ($page = mysqli_fetch_array($page_set)){
                            echo "<option value='" . $page['page_name'] . "'>" . $page['page_name'] . "</option>";   
                        }*/
                        //Loop thru the array and get the 'page name'
                        foreach($page_list as $row){ 
                            echo "<option value='" . $row['page_name'] . "'>" . $row['page_name'] . "</option>";
                        }
                echo "</select>";
                
                //Free result set
                mysqli_free_result($page_set);
                
             ?>
            <br /><br /> 
                <input type="submit" name="submit"/>
            </form>

mikosiko · June 5, 2013

the last code that you posted is different to the one in your first post... you have a new (commented) while loop here why?.... where exactly are you running your second query?... post your updated and complete relevant code.

The code suggested works fine on my side to solve the "out of sync" issue after a SP call.

Here is a complete working example that you can follow

<?php 
  // Define how to display/report errors
   ini_set("display_errors", "1");
   error_reporting(E_ALL);


   /*** mysql hostname ***/ 
   $hostname = 'localhost'; 
   $username = 'xxxxx'; 
   $password = 'xxxxx'; 
   $dbname = 'xxx'; 


   /*** create a new mysqli object ***/ 
   $link = new mysqli($hostname, $username, $password, $dbname); 


   if (mysqli_connect_errno()) 
    {
      printf("Connection failed: %s\n", mysqli_connect_error());
      exit();
    }


  /* Stored Procedure to be executed*/
  $query  = "CALL Mq_Test1()";
  
  /* Send query (execute the stored pocedure) */ 
  if ($result = mysqli_query($link, $query)) { 
  
    /* Fetch the results of the query */ 
    while( $row = mysqli_fetch_array($result) ){ 
       $data[] = $row;
  
       echo ($row[0]. "--------- SR. " . $row[1] . "<br>"); 
    }
      
   do {
      mysqli_next_result($link);
   }  while (mysqli_more_results($link))


  /* Display results of the SP */
  var_dump($data);
  
  /* Define a Second Query */
  $query2 = "SELECT * FROM test1";
  $result2 = mysqli_query($link, $query2) or die("Error:" . mysqli_error($link));
  
  while( $row = mysqli_fetch_array($result2) ){ 
     $data2[] = $row;
  }
     
     
  /* Display Results from second query */
  var_dump($data2);
  
  /* Destroy the result set and free the memory used for it */ 
  mysqli_free_result($result); 
  } 
  
  /* Close the connection (Not really neccesary)*/ 
  mysqli_close($link); 
?>

Edited June 5, 2013 by mikosiko

skygremlin · June 5, 2013

Thanx for the quick response - Yeah sorry I might have deleted out the commented while loop in the first post so it was easier to read - forgot in the second..

Here's what I'm working with - currently (with the do loop back in) ..

<table id="structure">
    <tr>
        <!-- Menu bars on the left col. -->
	<td id="navigation">
            <br /><br />
            <button onclick="location.href='issues_all.php'">All Issues</button>
            <br /><br />
            <button onclick="location.href='issues_all_open.php'">Open Issues</button>
            <br /><br />
            <button onclick="location.href='issues_closed.php'">Closed Issues</button>
            <br /><br />

            <form action="issues_by_page_2.php" method="post"> 
                <?php

                    //Get the a listing of all the pages on the site from the pages table
                   $page_set = mysqli_query($connection, "call all_pages");
                        if (!$page_set) {
                            printf("Error in getting the pages: %s\n", mysqli_error($connection));
                            exit();
                        }

                    //Populate the Pages drop down menu
                    echo "<select name='page'>";            
                            //Transfer the result to an array
                            while ($row = mysqli_fetch_array($page_set)){
                                $page_list[] = $row;
                            }

                            do {
                                mysqli_next_result($page_set);
                            }  while (mysqli_more_results($page_set));

                            //Loop thru the array and get the 'page name'
                            foreach($page_list as $row){ 
                                echo "<option value='" . $row['page_name'] . "'>" . $row['page_name'] . "</option>";
                            }
                    echo "</select>";

                    //Free result set
                    mysqli_free_result($page_set);

                 ?>
             <br /><br /> 
                 <input type="submit" name="submit"/>
             </form>
        </td>
      <td id="page">
            <?php //if(!empty($message)){
                $page = "home.php";
                //$issues = mysqli_query($connection, "call page_issues(\"ser_ms.php\")");
                $issues = mysqli_query($connection, "call page_issues('$page')");
                if (!$issues) {
                    printf("Error: %s\n", mysqli_error($connection));
                    exit();
                }
echo "<table border='1' style=\"margin-right: 30px\">
	<tr>
        <th>ID</th>
	<th>Date</th>
	<th>Priority</th>
        <th>Reported By</th>
	<th>Issues</th>
	<th>Page</th>
	<th>Owner</th>
	<th>Action_Item</th>
	<th>Update</th>
	<th>Resolve Date</th>
        <th>Closed</th>
	</tr>";

while ($row = mysqli_fetch_array($issues)){

	
	echo "<tr>";
	echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['date'] . "</td>";
	echo "<td>" . $row['priority'] . "</td>";
	echo "<td>" . $row['reported_by'] . "</td>";
        echo "<td>" . $row['issue'] . "</td>";
	echo "<td>" . $row['page'] . "</td>";
	echo "<td>" . $row['owner'] . "</td>";
	echo "<td>" . $row['action_item'] . "</td>";
	echo "<td>" . $row['update'] . "</td>";
	echo "<td>" . $row['resolved_date'] . "</td>"; 
        echo "<td>" . $row['closed'] . "</td>"; 
	echo "</tr>";
}
 
echo "</table>";
                
              
           // echo "<p class=\"message\">" . $message . "</p>";
           // }else{
             // echo " <p>No open issues found for " . $message . "</p> ";
               
            //}  
        ?>   
<?php
	//Close the mysql connection
	if (isset($connection)){
		mysqli_close($connection);
	}
?>
               
      </td> 
    </tr>
  </table>

kicken · June 5, 2013

According to the docs the param for mysqli_next_result and mysqli_more_results should be $connection, not $page_set. You may also need to fetch the results, not sure on that. Try:

while (mysqli_next_result($connection)){
   mysqli_fetch_all(mysqli_store_result($connection));
}

mikosiko · June 5, 2013

Yes .. kicken is correct.... should be $connection...as it is show in my last example (my mistake in the previous post)... fetching is not necessary

skygremlin · June 5, 2013

Thanx for the advice.. I think we got this working... I did a bit of rearranging the code - both the Dooo Loop and IF statement work (or at least looks like they're working) ::)

Current Code - Working:

<form action="issues_by_page_2.php" method="post">
            <?php
                
                //Get the a listing of all the pages on the site from the pages table
               $page_set = mysqli_query($connection, "call all_pages");
                    if (!$page_set) {
                        printf("Error in getting the pages: %s\n", mysqli_error($connection));
                        exit();
                }
                
                //Populate the Pages drop down menu
                echo "<select name='page'>";            
                //Transfer the result to an array
                while ($row = mysqli_fetch_array($page_set)){
                    $page_list[] = $row;
                }

                //Loop thru the array and get the 'page name'
                foreach($page_list as $row){ 
                    echo "<option value='" . $row['page_name'] . "'>" . $row['page_name'] . "</option>";
                }
                echo "</select>";
                echo "<br /><br />"; 

                echo "<input type=\"submit\" name=\"submit\"/>";

                //clear the result set
                //Works too..
                /*IF (mysqli_more_results($connection)) {
                    mysqli_next_result($connection);
                }*/
                
                do {
                    mysqli_next_result($connection);
                }  while (mysqli_more_results($connection));

                //Free result set
                mysqli_free_result($page_set);
                
              ?>  
            </form>

Sign In

command out of sync / clearing the result set.

Recommended Posts

skygremlin

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

skygremlin

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information