Drop down menu from a MySQL db...

Mr_Mako

Hi again...

I need to create a drop down menu populated by data from a MySQL database, and I've been having a difficult time finding information on how to do that. I've searched the forums here and gotten a few ideas, but there was really no concrete examples.

How exactly would one code something like that? Or is there another site with information relevant to this?

Thanks...

nnichols

You wouldn't have to search very far to find some examples, but here is one anyway -

$sql = "SELECT value, label FROM tbl_name";
$res = mysql_query($sql, $db)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

echo '<select name="blah">';

while (mysql_fetch_row($res)) {
echo '<option value="' . $row[0] . '">' . $row[1] . '</option>';
}

echo '</select>';

Hope this helps 😉

al042077

I'm just an amateur, but here's how I do it (I'm assuming this is for MySQL - other databases have slightly different syntax.

<select>
<?php
// If you don't already have a database connection, do this
$conn = mysql_connect("hostname", "username", "password");
$db = mysql_select_db("database");
// Run query to get data for your drop-down
$list = mysql_query("SELECT * FROM drop_down_list_table ORDER BY field1 ASC, field2 DESC");
while ($row = @mysql_fetch_assoc($list))
{
echo "<option value=\"$row[field1]\">$row[field2]</option>\n";
}
?>
</select>

This script creates a database connection, queries the database and sorts by field1 ascending and field2 descending. Then, it prints out the rows for the drop-down row by row using the field1 (presumably the primary key field) as the value and field2 (something like Name, City, or whatever) as the label.
Hope this works for you!

Mr_Mako

Well... I tried both examples shown here with no results. The 2nd example gives me a dropdown menu, but it's empty. The first example gives me an error message and I can't figure out exactly why... it's a SQL Query error of some sort. My code is as follows so far:

<?

$conn = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
$db = mysql_select_db('database', $conn) or die(mysql_error());

$sql = "SELECT username FROM phpbb_users";
$res = mysql_query($sql, $db) or die(mysql_error());

echo '<select name="blah">';

while (mysql_fetch_row($res)) {
	echo '<option value="' . $row[0] . '">' . $row[1] . '</option>';
}
echo '</select>';

?>

Have I missed something?

al042077

Run you page and bring up the page AS IS with the blank drop-down. Go into the source of the page (in IE, it's View->Source) and see if the options are there with values but no corresponding labels to display.

Your query has only one field returned. Therefore, when you call for $row[0], it returns that field, but $row[1] is null.

Let me know how this works.

kxm0331

I was seaching the web to find a solution to the exact same problem were having( the drop down menus). I was taking a look at your final code and noticed that $row is not being set to each row of the query. I have been able to get my code to work by making a similar change.
<?

$conn = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
$db = mysql_select_db('database', $conn) or die(mysql_error());

$sql = "SELECT username FROM phpbb_users";
$res = mysql_query($sql, $db) or die(mysql_error());

echo '<select name="blah">';

while ($row = mysql_fetch_row($res)) {

echo '<option value="' . $row[0] . '">' . $row[1] . '</option>';
}
echo '</select>';

My code not a bit different that this so if this does not work I can send you what I have