HTML dropdowns populate using PHP and MySQL

Question

I'm returning here after a couple of years... life has taken me on a different path, but I'm returning to this and STILL trying to get something that works.

I'm not a pro front-end developer, am in the process of learning, but in the interest of "code-first, learn-second" I'm trying to get a page to load using multiple dropdowns fed by MySQL stored procedures. While I'm always interested in the "best way to do this" (e.g. "why aren't you doing OOP?" or "why are you trying to do such-and-such THAT way?), I'll simply say I'm learning, but for right now, I'm just trying to figure out "Why won't this work?" I've watched many videos on YouTube - scoured articles here - and still can't figure out what is wrong with this. This seems like it should be a lot simpler than it is, and can't figure out why I can't get this to work, even IF it's not the best practice. The videos I watch inevitably show how to get A (single) procedure to populate a dropdown. I can get A (single) dropdown to populate; I just can't get two or more on the same page.

The tables in my DB are overly simple, and again, not really the point. I have a "year" table which basically (for simplicity's sake) has an "id" which is the year and a column (which is also the year). So:

year_id  year
2017     2017
2018     2018

Don't ask my "why" - it's simple just to try to prove the concept (to get it to work).

My "teams" table has a seq and a name

team_seq  team_name
1         joe_smith
2         tom_jefferson

I have two procedures, one called ref_sp_Year_Ref, which is a simple select on Year, and another procedure called ref_sp_Team_Ref, which is also a simple select.

The procedures run fine in MySQL; no problem.

If I take the SELECT statements out of the MySQL procedure, and type them as a "Select" into a variable in my PHP code, it works fine and the dropdowns populate. i.e. If I have:

    $qry1 = "Select ... <the rest of the Team query"
    $qry2 = "Select ... <the rest of the Year query"

Then the dropdowns work.

If I use $qry1 = "Call ref_sp_Team_Ref" and $qry2 = "Call ref_sp_Year_Ref" then I can get the FIRST dropdown to populate, but the second one won't.

Below is my current code. Again, I'm SURE there are better ways - the answers to my original question two years ago have already suggested that. But I want to understand WHY THIS won't work. Why does it work with "Selects" in the PHP but not with Stored procs?

Running this results in the "Teams" dropdown populating correctly, but then an error message appears where the "Year" dropdown would be, and says: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, book given in: and it references this line: if (mysqli_num_rows($years) > 0) The second error is essentially the same, referencing this line: mysqli_free_result($years);

In both cases, it seems evident to me that the second proc either isn't being called or isn't running, and it came back empty. I don't "get" why...

I will say - in response to the original answers - that previously I was using "multi_query" when clearly it wasn't a multi query. At the time, I had just typed that out of a book and didn't understand what that was about. Presently though, since the top procedure (ref_sp_team_ref) DOES return results, my guess is that my problem isn't the call itself or the syntax of the call. I just don't know why ref_sp_year_ref won't also run and load

Additionally I might add: In my PHP code, if I have a SELECT query in my first call (Teams), and a procedure call in my second call (Years), it works. Both dropdowns populate. THAT is what I don't "get". Why will two "Selects" work, or one "select" and one procedure call, but two procedure calls won't work?

Thanks in advance for any assistance.

<form method="post" action="">

<?php
  require_once('../../../mysqlconnect_mysqli.php'); 

  /* TEAM DROPDOWN */

    $qry1 = "CALL ref_sp_Team_Ref()";

    $teams = mysqli_query($conn,$qry1);

    if (mysqli_num_rows($teams) > 0)
    {
      echo "<label for='teams'>Teams: </label>";
      echo "<select name='teams' size='1' required>";   
      echo "<option>Team Names</option>";

      while($row = mysqli_fetch_array($teams))
        {
          echo "<option value=\"{$row['team_seq']}\">{$row['team']}</option>";         
        }

      echo "</select>";
      echo "<br /><br />";

    }
    else
    {
      echo "Why is this empty?";
    }

    mysqli_free_result($teams);


  /* YEAR DROPDOWN */
    $qry2 = "CALL ref_sp_Year_Ref()";

    $years = mysqli_query($conn,$qry2);

    if (mysqli_num_rows($years) > 0)
    {
      echo "<label for='years'>Years: </label>";
      echo "<select name='years' size='1' required>";   
      echo "<option>Seasons</option>"; 

      while($row = mysqli_fetch_array($years))
        {
          echo "<option value=\"{$row['year_id']}\">{$row['year']}</option>";         
        }

      echo "</select>";
      echo "<br /><br />";

    }
    else
    {
      echo "Why is this empty?";
    }    

    mysqli_free_result($years);

  echo " <input type='submit' name='submit1' value='Get Results'/>";
  echo "</form>";

?>

My earlier post (from 2 years ago) is below.

I've read a number of posts on this topic, but still can't seem to locate my problem. I would consider myself a beginner when it comes to HTML Forms and PHP. I have extensive experience using stored procedures (T-SQL, Oracle, and new to MySQL procedures). My belief is that my problem here is in PHP or HTML - the MySQL stored procedures seem ok when executed.

I'm trying to create multiple dropdowns in the HTML page populated from the Procs. I'm not doing anything fancy yet, e.g. dynamic selection of 1st value sent to proc of second... I'm just trying to do basic "load" of all dropdowns.

The following code causes the first dropdown ("Years") to populate fine, but the second dropdown ("Teams") is empty. If I switch the order of the blocks, "Teams" will populate fine, but "Years" will be empty.

I'm sure I'm just missing something very simple... but help would be greatly appreciated.

My code is:

<form method="post" action="">

<p>
Years: 
    <select name="years">
    <?php 
    #the following returns the connection variable $dbh
    require_once('../../../mysqlconnect_mysqli.php');   
    $sql = "call ref_sp_Year_Ref";

    if ($mysqli->multi_query($sql))
    {
       #Get first data set from Procedure - do nothing with it
       $result = $mysqli->store_result();  


       while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
       {
       echo     
          "<option value='" . $row['year_id'] . "'>" . $row['year'] . "</option>";  
       }
       mysqli_free_result($result);

    }         
    ?>
</select>       
</p>

<p>
Teams: 
    <select name="teams">
    <?php 
    #the following returns the connection variable $dbh
    require_once('../../../mysqlconnect_mysqli.php');       
    $sql = "call ref_sp_Team_Ref";      

    if ($mysqli->multi_query($sql))
    {
       #Get first data set from Procedure - do nothing with it
       $result = $mysqli->store_result();  


       while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
       {
       echo     
          "<option value='" . $row['team_id'] . "'>" . $row['team'] . "</option>";
       }
       mysqli_free_result($result);

    }         
    ?>
</select>       
</p>

<br>
<input type="submit" name="submit1" value="Get Results"/>
</form>

I am going to try to help. I think I see where things are going wrong. Re-writing to make it clear. I hope what I produce can help. Is there an error message, or just nothing (no drop down)? — Anthony Rutledge
– Anthony Rutledge, Commented Mar 10, 2017 at 23:46
@user2059532 Even though I was operating blindly, I think I gave you enough information to solve your problem. — Anthony Rutledge
– Anthony Rutledge, Commented Mar 11, 2017 at 3:25
I did what I could to help., It seems strange to me to need a multi-query to create a dynamic select list, though. — Anthony Rutledge
– Anthony Rutledge, Commented Mar 11, 2017 at 4:02
Have you tried to var_dump($mysql) for the second condition? Also, the second require_once() is not going to do anything since it is require_once(). — Elin
– Elin, Commented Mar 11, 2017 at 15:51

Community · Accepted Answer · 2017-05-23 12:09:30Z

Note: Because I use object oriented programming, I would never solve this problem the way I do here in my projects. But, if you are going to go the procedural route, ok, let me see what I can do to help.

This would be the long way, but if you follow the link to my answer in the following question, you'll see how I modularized (with functions) creating dynamic PHP with SQL (MySQLi etc ...).

Modularizing Procedural PHP with SQL for dynamic HTML.

In this case, the programmer wanted to make a dynamic <table>, but I am sure you can pick out some ideas for your particular situation, if you want or need them. Be sure to get all the way down to the bottom of my answer.

For your question, I changed up the way the query was being executed. MySQLi::query instead of MySQLi::multi_query. Also, I change the way the results (mysqli_result) were dealt with.

I see that you are using MySQLi::multi_query. Is your stored procedure a multi query? If that is the case, you may also need to add a small algorithm (while loop) using MySQi::more_results (check for more results, returns bool), MySQi::next_result, and MySQi::store_result.

From the PHP Manual: mysqli::multi_query

Example #1 (dealing with mutli-queries)

if ($mysqli->multi_query($sql)) {  // Run the multi-query
    do {
        if ($result = $mysqli->store_result()) {    // Access the result.
            while ($row = $result->fetch_assoc()) { // Use the result.
               //Your code here
            }
            $result->free();
        }
    } while ($mysqli->next_result());        //Get the next result.
}

Your specific solution (hopefully).

<form method="post" action="">
    <label>Years: 
        <select name="years">
        <?php 
            require_once('../../../mysqlconnect_mysqli.php');   
            $sql = 'CALL ref_sp_Year_Ref()';
            $result = $dbh->query($sql);

            if($result === false) {
                throw new UnexpectedValueException("The stored proceedure ref_sp_Year_Ref() did not return a mysqli_result object.");
            }

            while ($row = $result->fetch_assoc($result,MYSQLI_ASSOC)) {
                echo "<option value=\"{$row['year_id']}\">{$row['year']}</option>";  
            }

            $result->free();
        ?>
        </select>       
    </label>

    <label>Teams: 
        <select name="teams">
        <?php     
            $sql = 'CALL ref_sp_Team_Ref()';      
            $result = $dbh->query($sql);

            if($result === false) {
                throw new UnexpectedValueException("The stored proceedure ref_sp_Team_Ref() did not return a mysqli_result object.");
            }

            while ($row = $result->fetch_assoc()) {
                echo "<option value=\"{$row['team_id']}\">{$row['team']}</option>";
            }

            $result->free();
        ?>
        </select>       
    </label>
    <br>
    <input type="submit" name="submit1" value="Get Results"/>
</form>

MySQLi

Standard Queries.

These two functions are for instantiating a MySQLi object and returning a simple mysqli_result object.

/**
 * Returns a mysqli_result object, or throws an `UnexpectedValueException`.
 * You can reuse this for other SELECT, SHOW, DESCRIBE or EXPLAIN queries.
 */
function getMySQLiResult(MySQLi $db, $sql)
{
    if (!is_string($sql)) {
        throw new InvalidArgumentException("The second argument to the function getMySQLiResult must be a string (SQL query)!");
    }

    $result = $db->query($sql);

    if (!($result instanceof mysqli_result)) {
        throw new UnexpectedValueException("<p>MySQLi error no {$db->errno} : {$db->error}</p>");
    } 

    return $result;
}

/**
 * Returns a MySQLI object, or throws an `UnexpectedValueException`.
 */
function getMySQLi()
{
    require_once 'dbCreds.php'; //Choose your own file name. Do not put in public directory.

    $db = new mysqli($host, $username, $passwd, $dbname); //$port would be next.

    if (!($db instanceof MySQLi)) {
        throw new UnexpectedValueException("A MySQLi object was not returned during your connection attempt.");
    }

    if (isset($db->connect_error)) {
        throw new UnexpectedValueException("The database connection was not established. {$db->connect_errno} : {$db->connect_error}");
    }

    return $db
}

Multi-queries.

These two functions are a way to deal with multi-queries.

/**
 * Returns null, or an array of mysqli_result objects.
 */
function gatherMQResults(MySQLi $db, $sql)
{
    $results = []; //PHP 5.4+

    if ($db->multi_query($sql)) {  // Run the multi-query
        do {
               $result = $db->store_result();  //Store result.

               if ($result !== false) { //Test result
                   $results[] = $result //Be careful when result set is large!!
               }
        } while ($db->next_result()); //Get the next result.
    }

    return (empty($results)) ? null : $results;
}

/**
 * Uses an anonymous function to use the query result.
 */
function useMySQLiResult(Callable $fn, mysqli_result $result)
{
    $value = $fn($result->fetch_all());  //PHP 5.3+, Where $fn is an anonymous function
    $result->free();
    return $value;
}

Examples

You might use these two function like this. Check them out, you may get some ideas.

/* Define an anonymous function */
$makeOptions = function(array $data) {
    $options = [];

    foreach ($data as $key => $value) {
        $options[] = "<option value=\"{$key}\">{$value}</option>"
    }

    return implode("\n", $options) . "\n"
};

function drawYearOptions(MySQLi $db, Callable $fn)
{
    $sql     = "someAmazingMultiQuery";     // The multi-query.
    $results = gatherMQResults($db, $sql);  // All the results.

    if (!isset($results)) {
        throw new Exception("Where the heck are the year results? None found.");
    }

    return useMySQLiResult($fn, results[0])  // Index depends on query.
}

function drawTeamOptions(MySQLi $db, Callable $fn)
{
    $sql     = "anotherAmazingMultiQuery"; // The multi-query.
    $results = gatherMQResults($db, $sql); // All the results.

    if (!isset($results)) {
        throw new Exception("Where the heck are the team results? None found.");
    }

    return useMySQLiResult($fn, results[0]) // Index depends on query.
}

Use the final functions above inside of a selects: <select>.

<select name="foo">
    <?= drawYearOptions($mysqli, $makeOptions); ?>
</select>

<select name="bar">
    <?= drawTeamOptions($mysqli, $makeOptions); ?>
</select>

Now, when you are ready, this can be cleared up with some handy, dandy object instances and methods.

<select name="foo">
    <?= $view->drawYearOptions(); ?>
</select>

<select name="bar">
    <?= $view->drawTeamOptions(); ?>
</select>

Which, when your system is refined enough, becomes this (no pun intended).

<select name="foo">
    <?= $this->drawYearOptions(); ?>
</select>

<select name="bar">
    <?= $this->drawTeamOptions(); ?>
</select>

Collectives™ on Stack Overflow

HTML dropdowns populate using PHP and MySQL

My earlier post (from 2 years ago) is below.

1 Answer 1

Your specific solution (hopefully).

MySQLi

Examples

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My earlier post (from 2 years ago) is below.

1 Answer 1

Your specific solution (hopefully).

MySQLi

Examples

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related