How to populate HTML dropdown list with values from database

Question

as part of a HTML form I am creating I would like to have a dropdown list which will list all the usernames in my database.

I thought the following code would do the trick but the dropdown list is empty - could someone assist me in what i'm doing wrong? Thanks.

<tr>
<td>Owner</td>
<td>
<select name="owner">
<?php 

$sql = mysqli_query($connection, "SELECT username FROM users");

while ($row = $sql->fetch_assoc()){

?>
<option value="owner1"><?php echo $row['username']; ?></option>

<?php
// close while loop 
}
?>
</td>
</tr>

score 34 · Accepted Answer · edited May 12 '23 at 10:36

My guess is that you have a problem since you don't close your select-tag after the loop. Consider separating your database-related code from the markup, it will help you to spot such mistakes easily

<?php
...
// SQL part
$sql = mysqli_query($connection, "SELECT username FROM users");
$data = $sql->fetch_all(MYSQLI_ASSOC);

// HTML part
?>
<tr>
  <td>Owner</td>
  <td>
    <select name="owner">
      <option value=""></option>
      <?php (foreach $data as $row): ?>
      <option value="<?= htmlspecialchars($row['id']) ?>">
        <?= htmlspecialchars($row['username']) ?>
      </option>
      <?php endforeach ?>
    </select>
  </td>
</tr>

Note that you have to either give the each value attribute a unique value or omit this attribute altogether. I added the row id as such value.

score 15 · Answer 2 · edited May 12 '23 at 10:21

15

Below code is nice.. It was given by somebody else named aaronbd in this forum

<?php

$conn = new mysqli('localhost', 'username', 'password', 'database');
$result = $conn->query("select id, name from table");
    
echo "<html>";
echo "<body>";
echo "<select name='id'>";

while ($row = $result->fetch_assoc()) {
  $id = $row['id'];
  $name = $row['name']; 
  echo '<option value="'.htmlspecialchars($id).'">'.htmlspecialchars($name).'</option>';
}
echo "</select>";
echo "</body>";
echo "</html>";

edited May 12 '23 at 10:21

Your Common Sense

156,878
40
214
345

answered Oct 31 '12 at 09:55

Imon

3,905
4
25
25

can you tell me why ">" used in option ? and how it works – Smit Saraiya Sep 20 '16 at 09:41
4

@SmitSaraiya ">" is used in option as standard html syntax. – Imon Sep 24 '16 at 08:49

score 5 · Answer 3 · edited May 12 '23 at 10:23

I'd suggest following a few debugging steps.

First run the query directly against the DB. Confirm it is bringing results back. Even with something as simple as this you can find you've made a mistake, or the table is empty, or somesuch oddity.

If the above is ok, then try looping and echoing out the contents of $row just directly into the HTML to see what you've getting back in the mysql_query - see if it matches what you got directly in the DB.

If your data is output onto the page, then look at what's going wrong in your HTML formatting.

However, if nothing is output from $row, then figure out why the mysql_query isn't working e.g. does the user have permission to query that DB, do you have an open DB connection, can the webserver connect to the DB etc [something on these lines can often be a gotcha]

Changing your query slightly to

$sql = mysql_query("SELECT username FROM users") or trigger_error(mysql_error());

may help to highlight any errors: php manual

tim3in · Answer 4 · 2019-04-17T09:01:54.877

Use OOP concept instead. Create a class with function

class MyClass {

...

function getData($query) {
    $result = mysqli_query($this->conn, $query);
    while($row=mysqli_fetch_assoc($result)) {
        $resultset[] = $row;
    }       
    if(!empty($resultset))
        return $resultset;
} }

and then use the class object to call function in your code

<?php 

    $obj = new MyClass();
    $row = $obj->getData("select city_name from city"); 
?>
<select>
    <?php foreach($row as $row){ ?>
        <option><?php echo $row['city_name'] ?></option>

<?php  } ?>
</select>

Full code and description can be found here

score 1 · Answer 5 · answered Feb 04 '14 at 13:55

1

<select name="owner">
<?php 
$sql = mysql_query("SELECT username FROM users");
while ($row = mysql_fetch_array($sql)){
echo "<option value=\"owner1\">" . $row['username'] . "</option>";
}
?>
</select>

answered Feb 04 '14 at 13:55

user2806221

101
1
11

It wouldn't harm if you added some lines explaining what are you doing, or what's the difference between yours and user's solution. – nKn Feb 04 '14 at 14:12

score 1 · Answer 6 · answered Aug 24 '17 at 10:10

<?php
 $query = "select username from users";
 $res = mysqli_query($connection, $query);   
?>


<form>
  <select>
     <?php
       while ($row = $res->fetch_assoc()) 
       {
         echo '<option value=" '.$row['id'].' "> '.$row['name'].' </option>';
       }
    ?>
  </select>
</form>

How to populate HTML dropdown list with values from database

6 Answers6

Linked