CODEWITHSUNDEEP
phphtmlmysqli
ygongdev
ygongdev

Reputation: 284

How to call 2 stored procedures using mysqli

Bascially I have a form with 2 select dropdown boxes and I want to populate the selections with 2 stored procedures that returns all the values via php. However, it seems like only the first select box is getting populated while the second one is not. I have tested both stored procedure and they work fine.

<div class="form-group row">
  <label class="col-sm-2 form-control-label">Name</label>
  <div class="col-sm-10">
    <select class="form-control" name="pokemonName" style="width:30%;">
      <?php          
        require_once '../database/connection.php';
        $sqlNames = "CALL sp_selectAllPokemonName";
        $names = $con->query($sqlNames);
        while ($name = $names->fetch_assoc()){
          echo "<option value= '".$name['pokemonName']."'>".$name['pokemonName']."</option>";
        }
      //  $names->close();
      //  $con->close();
      ?>
    </select>
  </div>
</div>

<div class="form-group row">
  <label class="col-sm-2 form-control-label">Hour</label>
  <div class="col-sm-10">
    <select class="form-control" name="hour" style="width:30%;">
      <?php 
        // query doesn't work here
        require_once '../database/connection.php'; 
        $sqlHours = "CALL sp_selectAllHour";
        $hours = $con->query($sqlHours);
        while ($hour = $hours->fetch_assoc()){
          echo "<option value= '".$hour['Id']."'>".$hour['Id']."</option>";
        }
      ?>
    </select>
  </div>
</div>

connection.php

<?php
    require 'credentials.php';
    include '../ChromePhp.php';
    // Create connection
    //$con = mysqli_connect($servername, $username, $password, $dbname); // Procedural
    $con = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($con->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        ChromePhp::warn('something went wrong!');
    }
?>

Upvotes: 0

Views: 731

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157864

This is an excerpt from my PDO tutorial, but the principle is applicable to any API:

There is one thing about stored procedures any programmer stumbles upon at first: every stored procedure always returns one extra result set: one (or many) results with actual data and one just empty. Which means if you try to call a procedure and then proceed to another query, then "Cannot execute queries while other unbuffered queries are active" error will occur, because you have to clear that extra empty result first.

So you have to advance to that extra result set, using mysqli_next_result

Just a call to $con->next_result() should do the trick. Note that you have to call it only after fetching all the data from result set.

Upvotes: 3

Related Questions