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>
This question is related to
php
sql
while-loop
Below code is nice.. It was given by somebody else named aaronbd in this forum
<?php
$conn = new mysqli('localhost', 'username', 'password', 'database')
or die ('Cannot connect to db');
$result = $conn->query("select id, name from table");
echo "<html>";
echo "<body>";
echo "<select name='id'>";
while ($row = $result->fetch_assoc()) {
unset($id, $name);
$id = $row['id'];
$name = $row['name'];
echo '<option value="'.$id.'">'.$name.'</option>';
}
echo "</select>";
echo "</body>";
echo "</html>";
?>
<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>
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>
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 die(mysql_error());
may help to highlight any errors: php manual
<?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>
Source: Stackoverflow.com