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>

Dmitri Sologoubenko · Accepted Answer · 2024-05-08 15:31:14Z

34

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.

Edit: move "(" before the 'foreach' command to after.

edited May 8, 2024 at 15:31

Dmitri Sologoubenko

2,9191 gold badge24 silver badges28 bronze badges

answered Nov 5, 2011 at 18:40

Christofer Eliasson

34k7 gold badges77 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Your Common Sense · Accepted Answer · 2023-05-12 10:21:46Z

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, 2023 at 10:21

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Oct 31, 2012 at 9:55

Imon

3,9154 gold badges27 silver badges25 bronze badges

2 Comments

Smit Saraiya Over a year ago

can you tell me why ">" used in option ? and how it works

Imon Over a year ago

@SmitSaraiya ">" is used in option as standard html syntax.

Your Common Sense · Accepted Answer · 2023-05-12 10:23:40Z

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 · Accepted Answer · 2019-04-17 09:01:54Z

2

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

edited Apr 17, 2019 at 9:01

answered Apr 17, 2019 at 5:57

tim3in

13910 bronze badges

Comments

user2806221 · Accepted Answer · 2014-02-04 13:55:12Z

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 4, 2014 at 13:55

user2806221

1011 silver badge11 bronze badges

1 Comment

nKn Over a year ago

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.

rashedcs · Accepted Answer · 2017-08-24 10:10:05Z

1

<?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>

answered Aug 24, 2017 at 10:10

rashedcs

3,7632 gold badges43 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to populate HTML dropdown list with values from database

6 Answers 6

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related