Populate a Drop down box from a mySQL table in PHP

Question

I am trying to populate a Drop down box from results of a mySQL Query, in Php. I've looked up examples online and I've tried them on my webpage, but for some reason they just don't populate my drop down box at all. I've tried to debug the code, but on the websites I looked at it wasn't really explained, and I couldn't figure out what each line of code. Any help would be great :)

Here's my Query: Select PcID from PC;

Answer 1

You will need to make sure that if you're using a test environment like WAMP set your username as root. Here is an example which connects to a MySQL database, issues your query, and outputs <option> tags for a <select> box from each row in the table.

<?php

mysql_connect('hostname', 'username', 'password');
mysql_select_db('database-name');

$sql = "SELECT PcID FROM PC";
$result = mysql_query($sql);

echo "<select name='PcID'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['PcID'] . "'>" . $row['PcID'] . "</option>";
}
echo "</select>";

?>

Answer 2

Below is the code for drop down using MySql and PHP:

<?
$sql="Select PcID from PC"
$q=mysql_query($sql)
echo "<select name=\"pcid\">"; 
echo "<option size =30 ></option>";
while($row = mysql_fetch_array($q)) 
{        
echo "<option value='".$row['PcID']."'>".$row['PcID']."</option>"; 
}
echo "</select>";
?>

Answer 3

Since mysql_connect has been deprecated, connect and query instead with mysqli:

$mysqli = new mysqli("hostname","username","password","database_name");
$sqlSelect="SELECT your_fieldname FROM your_table";
$result = $mysqli -> query ($sqlSelect);

And then, if you have more than one option list with the same values on the same page, put the values in an array:

while ($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

And then you can loop the array multiple times on the same page:

foreach ($rows as $row) {
    print "<option value='" . $row['your_fieldname'] . "'>" . $row['your_fieldname'] . "</option>";
}

Answer 4

No need to do this:

while ($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

You can directly do this:

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

Answer 5

After a while of research and disappointments....I was able to make this up

     <?php $conn = new mysqli('hostname', 'username', 'password','dbname') or die ('Cannot connect to db') $result = $conn->query("select * from table");?>

//insert the below code in the body


    <table id="myTable"> <tr class="header"> <th style="width:20%;">Name</th>
    <th style="width:20%;">Email</th>
       <th style="width:10%;">City/ Region</th>
        <th style="width:30%;">Details</th>
  </tr>
  <?php
   while ($row = mysqli_fetch_array($result)) {

               echo "<tr>";
               echo "<td>".$row['username']."</td>";
               echo "<td>".$row['city']."</td>";
                echo "<td>".$row['details']."</td>";
               echo "</tr>";
           }

     ?>
</table>

Trust me it works :)

Answer 6

At the top first set up database connection as follow:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database") or die($this->mysqli->error);
$query= $mysqli->query("SELECT PcID from PC");
?> 

Then include the following code in HTML inside form

<select name="selected_pcid" id='selected_pcid'>

            <?php 

             while ($rows = $query->fetch_array(MYSQLI_ASSOC)) {
                        $value= $rows['id'];
                ?>
                 <option value="<?= $value?>"><?= $value?></option>
                <?php } ?>
             </select>

However, if you are using materialize css or any other out of the box css, make sure that select field is not hidden or disabled.

Answer 7

What if you want to use both id and name in the dropdown? Here is the code for that:

$mysqli = new mysqli($servername, $username, $password, $dbname);
$sqlSelect = "SELECT BrandID, BrandName FROM BrandMaster";
$result = $mysqli -> query ($sqlSelect);

echo "<select id='brandId' name='brandName'>";

while ($row = mysqli_fetch_array($result)) {
   unset($id, $name);
   $id = $row['BrandID'];
   $name = $row['BrandName']; 
   echo '<option value="'.$id.'">'.$name.'</option>';
 }
 echo "</select>";