How to populate dropdown menu from sql table in PHP

Question

I tried inserting this into my code to generate a dropdown menu through an sql table but it responds with nothing in the dropdown. When I execute the sql query it doesn't show any error as well. Please be kind enough to fix my error. Thank you

 <?php
            $db = mysqli_connect('localhost', 'root', '', 'registration');
            $sql = "Select (unitid) from unit where (unitname)=
('$unitname')";
            mysqli_query($db, $sql);

            echo "<select name='unitid'>";
            while ($row = mysql_fetch_array($sql)) {
               echo "<option value='" .$row['unitid']."'> ".$row['unitname'] . "</option>"; 
            }
            echo "</select>";
            ?>

jeprubio · Accepted Answer · 2017-12-04T14:02:40.080

Try saving the result of mysqli_query:

$result = mysqli_query($db, $sql);

And then using it in the while condition:

while ($row = mysqli_fetch_array($result)) {

The select should be also "Select unitid, unitname ..." to return also the unitname used in the options:

$sql = "SELECT unitid, unitname FROM unit WHERE unitname = '$unitname'";

And you should use prepared statements if you want to prevent it from SQL injection attacks.

If you want all the units to be shown on the combo change the select to:

$sql = "SELECT unitid, unitname FROM unit";

So, the code should be now:

 <?php
        $db = mysqli_connect('localhost', 'root', '', 'registration');
        $sql = "Select unitid, unitname from unit";
        $result = mysqli_query($db, $sql);

        echo "<select name='unitid'>";
        while ($row = mysqli_fetch_array($result)) {
           echo "<option value='" .$row['unitid']."'> ".$row['unitname'] . "</option>"; 
        }
        echo "</select>";
 ?>

check your select, as having unitname = '$unitname' will only return one result. If you want all the units to be shown in the dropdown, as I suppose, just delete the where. — jeprubio, Dec 04 '17 at 13:51

score 0 · Answer 2 · answered Dec 04 '17 at 15:03

        <?php
         $db = mysqli_connect('localhost', 'root', '', 'registration');
          $sql = "Select unitid, unitname from unit where unitname=\"".$unitname."\"";

        $rows = array();
        $return = mysqli_query($db, $sql);
        while($row = mysqli_fetch_array($return, MYSQLI_ASSOC))
            $rows [] = $row;
        echo "<select name='unitid'>";
        for($i=0; $i<count($rows); $i++)
           echo "<option value='".$rows['unitid']."'> ".$rows['unitname']."</option>"; 

        echo "</select>";
        ?>

you didn't fetch the array correctly, so your query basically returned an empty array. You also wrote a wrong query. This solution should work fine

How to populate dropdown menu from sql table in PHP

2 Answers2