Populating multiple dropdowns with a single mysqli_fetch_array() query to MySQL in PHP

Question

I am having trouble populating multiple drop-downs .I need to do it with a single query to db and is it possible to do it within a single while loop? Here is the code.Only the first dropdown(Player) is getting populated.The database table(playerDB) has 2 columns-Player and Game. and the respective dropdowns need to be populated

 <form name="form1" action="" method="post">
    <fieldset>
      <legend>Selecting report</legend>
       <p>
          <?php
             $connect = mysqli_connect('localhost','root','','mydatabase');

             if(mysqli_connect_errno($connect))
             {
                echo 'Failed to connect';
             }
            else{
              echo '';
            }

            if (!$res=mysqli_query($connect, "SELECT * from playerDB")){
                  echo ("Error description: "  .mysqli_error($connect));
            }
         ?>

         <label>Select Player</label>
         <select>
           <?php
             while($row=mysqli_fetch_array($res))
             {
           ?>
            <option> <?php echo $row["Player"]; ?> </option           
           <?php
             }
          ?>
        </select>

          <label>Select Game</label>
          <select id = "myGameList">          
             <?php
             while($row=mysqli_fetch_array($res))
             {
           ?>
            <option> <?php echo $row["Game"]; ?> </option>                
           <?php
             }
           ?>
          </select>

Any help would be appreciated,Thanks!

The reason why only one of your dropdowns is getting populated is because mysqli_fetch_array always moves the result pointer of ($res) forward and it doesn't get reset automatically. Take a look at the code I've answered below. I've added comments accordingly hoping that it helps clarify your doubts. — DeadLock
– DeadLock, Commented Nov 2, 2017 at 9:43

Death-is-the-real-truth · Accepted Answer · 2017-11-02 11:56:01Z

1. Instead of using while() to populate select-boxes,try to save records in array and then use that array as many time as you want.

2. Also try to fetch only those columns what you actually needed further. It will make query lighter as well as records array lighter too.

Do like below:-

<?php
     $connect = mysqli_connect('localhost','root','','mydatabase');

     if(mysqli_connect_errno($connect))
     {
        echo 'Failed to connect';
     }
    else{
        if (!$res=mysqli_query($connect, "SELECT Player,Game from playerDB")){ // get that much column only which you want not all
              echo ("Error description: "  .mysqli_error($connect));
        }
    }
    $data = []; //create array 
    while($row=mysqli_fetch_array($res))
    {
        $data['Player'][] = $row["Player"]; //assign values to array
        $data['Game'][] = $row["Game"];//assign values to array
    }
?>


<form name="form1" action="" method="post">
    <fieldset>
        <legend>Selecting report</legend>
        <label>Select Player</label>
        <select>
        <?php
            foreach ($data['Player'] as $player){//use array as many times you want 
        ?>
            <option> <?php echo $player; ?> </option           
        <?php
            }
        ?>
        </select>
        <label>Select Game</label>
        <select id = "myGameList">          
        <?php
            foreach ($data['Game'] as $game){//use array as many times you want 
        ?>
            <option> <?php echo $game; ?> </option>                
        <?php
            }
        ?>
</select>

Anatoliy Babushka · Accepted Answer · 2017-11-02 07:34:31Z

1

You can use the mysqli_fetch_all function.

$connect = mysqli_connect('localhost','root','','mydatabase');

if(mysqli_connect_errno($connect))
{
    echo 'Failed to connect';
}

if (!$res=mysqli_query($connect, "SELECT * from playerDB")){
    echo ("Error description: "  .mysqli_error($connect));
}

$rows = mysqli_fetch_all($res);

Now you can use the $rows in your foreach

foreach ($rows as $row) {
    echo $row['Player'];
}

foreach ($rows as $row) {
    echo $row['Game'];
}

answered Nov 2, 2017 at 7:34

Anatoliy Babushka

361 bronze badge

Comments

Death-is-the-real-truth · Accepted Answer · 2017-11-02 15:03:33Z

You are already using a single query (which is good). The reason why only one of your drop-downs is getting populated is because mysqli_fetch_array always moves the result pointer of ($res) forward and it doesn't get reset automatically. Take a look at the code below. This should solve your problem and also clarify some of your doubts.

<form name="form1" action="" method="post">
    <fieldset>
        <legend>Selecting report</legend>
        <p>
        <?php
            $connect = mysqli_connect('localhost','root','','mydatabase');

            if(mysqli_connect_errno($connect))
            {
                echo 'Failed to connect';
            }
            else{
                echo '';
            }

            // Single DB query. This is good. 
            // You could also use "SELECT Player, Game FROM playerDB"
            // This is generally recommended over * because in real-life databases the number of columns in the table can be huge.
            // You should only pick the columns that you need.
            if (!$res=mysqli_query($connect, "SELECT * from playerDB")){
                echo ("Error description: "  .mysqli_error($connect));
            }

            $player_options = "";
            $game_options = "";

            // Single loop per row. Update the HTML into PHP variables here and then latur re-use these option variables to generate your dropdown(s)
            while($row=mysqli_fetch_array($res))
            {
                $player_options .= "<option>".$row["Player"]."</option>";
                $game_options .= "<option>".$row["Game"]."</option>";
            }
         ?>

            <label>Select Player</label>
            <select>
                <?php echo $player_options; ?>
            </select>

            <label>Select Game</label>
            <select id = "myGameList">          
                <?php echo $game_options; ?>
            </select>

Collectives™ on Stack Overflow

Populating multiple dropdowns with a single mysqli_fetch_array() query to MySQL in PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related