Jump to content

command out of sync / clearing the result set.

skygremlin

By skygremlin
in MySQL Help

 Share
Go to solution Solved by skygremlin,

Recommended Posts

skygremlin Member

skygremlin

Posted
Posted
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 Newbie

mikosiko

Posted
Posted

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 Member

skygremlin

Posted
  • Author
Posted

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 Member

skygremlin

Posted
  • Author
Posted

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 Newbie

mikosiko

Posted
Posted (edited)

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 by mikosiko
skygremlin Member

skygremlin

Posted
  • Author
Posted

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 Prolific Member

kicken

Posted
Posted

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));
}
  • Solution
skygremlin Member

skygremlin

Posted
  • Author
  • Solution
Posted

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>
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.