[Resolved] showing db info in a drop down list

k994519

Hi guys

I would like to know what is the best way of creating a drop down list which shows infor stored in db (mysql).

So the user can select it from said list and use it to fill out a form. The info in the list, in turn can be used to update their personal details. ( this is done as part of joining site).

any help would be much appreicated......

PurpleMonkey

Hello,

Luckily I have also wondered this, and used this script:

<?php
$conn = @mysql_connect("localhost","","")
or die("Sorry could not connect to mySQL");
$tb = "<form><select name=\"db\">";
$rs= mysql_list_dbs($conn);
for($row=0; $row < mysql_num_rows($rs); $row++)
{
 $db_list .= "<option>".mysql_tablename($rs, $row)."</option>";
}
$tb2 ="</select></form>";
?>
<html><head><title>Listing databases</title></head>
<body><h3><?php
echo($tb);
echo($db_list); 
echo($tb2);

?> </h3></body></html>

Hope this helps.

k994519

Hi,

thanks for your scipt, I am having a little trouble understanding it,

I would greatly appreicate if you could explain briefly what it does thanks.....

maxxd

Could be a bit more easily understood as the following code - it does exactly what PurpleMonkey's code does, just intersperses the php in the html...

<?php
mysql_select_db($dbName, $conName);
$qry="SELECT field FROM table WHERE condition";
$row=mysql_query($qry, $conName);
?>
<select name="whatever">
<?php
while($rs=mysql_fetch_assoc($row){
?><option>
<?php echo $rs['field']; ?>
</option><?php } ?>

Basically, you use php to select your db and retrieve the pertinent dataset, then use it to cause a loop as long there is data in the set, wrap the data between HTML <option> tags for the drop down, and you're good to go.

k994519

How can I use the script you have given me to input a select from the drop down list,( taken from a table field “preference descry”) to be selected by user and then Inserted as field in another table…..

e.g

USER table
user_id
name
preferance desc

preference table
preference id
preference descr

any help would be much appreicated

bunner_bob

I might do something like this - though note I don't have a field to input user_name. Assumes register_globals is off, but will work fine if it's on.

Your spelling is wandering a bit on the name of this field - preferance desc, preference descr, preference descry - but I'm assuming you're referring to the same field name.

Not tested:

<?php
echo "<form method=\"post\" action=\"page2.php\">";
echo "<select name=\"preference_desc\" size=\"1\">";
$query = "SELECT * FROM preference_table";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    echo "<option value=\"" . 
    $row['preference_desc'] . "\">" . 
    $row['preference_desc'] . "</option>";
}
echo "</select>";
echo "<input type=\"submit\" name=\"submit\" value=\"submit\">";
echo "</form>";
?>



//page2.php

<?php
if ($_POST['submit']) {
    mysql_query("INSERT INTO user_table (user_name, preference_desc) VALUES ('" . $_POST['user_name'] . "','" . $_POST['preference_desc'] . "')");
}
?>

k994519

hi bunner bob,

thanks for your help....... Ive tried to use ur code how ever it does not show any information in the drop down from the table preference, (yes there is data there)

any further tips would be greatly apprecated.....

bunner_bob

First I would check to make sure you are retrieving preference_desc from the database. Change the "while" loop to say:

while ($row = mysql_fetch_array($result)) {
echo $row['preference_desc'] . "<br>";
}

This should produce a list of items, with a <br> tag between each.

When in doubt, always always always do a "View source" in your browser. It will show you what making it that far, even if it's not being displayed properly.

In your case, we first need to know that $row['preference_desc'] actually contains something. If not, it's either the query, the database table or you're not getting a connection to the database. By the way, my previous code obviously contains no database connection string - I am assuming you have that in your script already - do you?

You can check whether your sql query is producing a result by writing:

if (mysql_num_rows($result) > 0) {
echo "there is a result";
}

A few bits to get you started anyway.