CODEWITHSUNDEEP
phpsqlwhile-loop
Bernard
Bernard

Reputation: 1229

How to populate HTML dropdown list with values from database

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>

Upvotes: 30

Views: 241647

Answers (6)

Christofer Eliasson
Christofer Eliasson

Reputation: 33865

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.

Upvotes: 34

Kris C
Kris C

Reputation: 2848

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

Upvotes: 5

Imon
Imon

Reputation: 3915

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>";

Upvotes: 15

tim3in
tim3in

Reputation: 139

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

Upvotes: 2

rashedcs
rashedcs

Reputation: 3725

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

Upvotes: 1

user2806221
user2806221

Reputation: 101

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

Upvotes: 1

Related Questions