0

I'm writing an application in PHP and am currently designing a form which includes two drop-down style boxes. These boxes need to be populated with data from a database I've got access to. The database contains two stored procedures, each returning the data to populate one of the drop-down boxes.

In my PHP, I prepare the queries I need to run like: 

$viewLevelQuery = "CALL pr_getViewLevels();";               
$publishLevelQuery = "CALL pr_getPublishLevels();";

And, in the lines immediately following, execute them like:

    $con=mysqli_connect($host,$user,$pass,$dbnm);               
    $viewLevelResult = mysqli_query($con,$viewLevelQuery);                      
    $publishLevelResult = mysqli_query($con,$publishLevelQuery);
    mysqli_close($con);

Later in the PHP, I render and populate the drop-down boxes like:

echo "<div class='col-sm-10'>
<select class='form-control' id='viewLvl' name='viewLvl'>";

while($row=mysqli_fetch_array($viewLevelResult))
{
    echo "<option>".$row['viewName']."</option>";
}
echo "</select> </div>";

And:

echo "<div class='col-sm-10'>
<select class='form-control' id='pubLvl' name='pubLvl'>";

while($row=mysqli_fetch_array($publishLevelResult))
{
    echo "<option>".$row['publishName']."</option>";
}

echo "</select> </div>";

My issue is that only one of the boxes is being populated and it seems as though only the first of the MySQLi_query calls is being performed. No error is returned and the page continues to render as it should. If the MySQLi_query lines at the beginning are switched, the other drop-down box is populated as expected which leads me to believe the queries to the database are correct.

Does the call to MySQLi_query "consume" the $con variable? If I execute the queries from PHP like: 

            $con=mysqli_connect($host,$user,$pass,$dbnm);               
            $viewLevelResult = mysqli_query($con,$viewLevelQuery);      

            $con=mysqli_connect($host,$user,$pass,$dbnm);//NOTICE Extra call to connect

            $publishLevelResult = mysqli_query($con,$publishLevelQuery);
            mysqli_close($con);

Both boxes get populated correctly. Doing this, however, doesn't feel right. Am I missing something? Or is this the way to go?.

Many thanks

EDIT: In case anyone with similar issues stumbles across this, as discovered below the error I was getting was because of mysqli's use of unbuffered queries. This means that it can't hold the results of two queries at once and so this memory needs to be freed once you have stored/used the results in your PHP. I simply copied the resultset to a PHP array and then included a:

while(mysqli_next_result($con))
{
    mysqli_free_result($con);//Clears results
}

after each query. My understanding of what is going on is a little flakey, but this seems to make sense. It works, anyhow.

cprlkleg
  • 363
  • 1
  • 5
  • 21
  • This is a quite strange behaviour. Try to remove mysqli_close($con). Further you should test the result value of mysqli_query if the query was successful. E.g. if($viewLevelResult === false) die("Query failed: " . mysqli_error()); – Mario A Jan 20 '15 at 13:21
  • @MarioA Thank you for your reply! Removing the mysqli_close($con) had no effect. In testing the result value as suggested, I found that whichever result was generated second gives the following error message: "Query failed: Commands out of sync; you can't run this command now". Any thoughts? – cprlkleg Jan 20 '15 at 13:29
  • Ok, but what is with the result values of the mysqli_query calls, are they both !== false ? – Mario A Jan 20 '15 at 13:34
  • @MarioA The first one performed always returns what it should. The second is what generates the error, so (given the if statement in your first comment) I assume the second always has the values === false. Now that I have the error message (thank you for that), I found [this question](http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now) which seems to answer my problem. I need to fetch the array out of the result before performing the second query. – cprlkleg Jan 20 '15 at 13:39

0 Answers0