How do I populate a "Select" box drop down menu in a form with Database fields?

[lazerbrains]

I am currently creating a form and I want to populate a drop down selection menu with data from two fields in a form. For example, I want it to pull the first and last name fields from a database to populate names in a drop down menu in a form. I want the form to submit to the email address of the person selected in the drop down. Is this possible to do? The email is already a field in the record of the person in the database.

 

Can anyone give me some pointers or advice on how I should go about setting up the "Select" box drop down? I am not sure how to code it to do what I am wanting. Any links to relevant help would be appreciated too.

 

Thanks in advance!

[Pikachu2000]

pseudo-code:

$query = "SELECT `value`, `label` FROM `table` WHERE `field` = 'parameter'";
$result = mysql_query($query);
echo '<select name="name_value">';
while( $array = mysql_fetch_assoc($result) ) {
     echo "<option value=\"{$array['value']}\">{$array['label']}</option>\n";
}
echo '</select>';

[lazerbrains]

Ok. But how would it work if the first name and last name fields are separate? And how is the email address being passed to the submit button?

[Pikachu2000]

Use the record's primary key as the value, and CONCAT_WS(' ', `first_name`, `last_name`) AS label, and then use the value to execute a SELECT query to retrieve the email address from the database after the form is submitted. If that doesn't make sense, or you need more help, just say so . . .

[lazerbrains]

Could you give me a little more info on what this line of code is doing, and how it is working?

 

CONCAT_WS(' ', `first_name`, `last_name`) AS label,

 

I am fairly new to PHP. Not a complete NOOB, but this is the first time with this sort of thing.

 

Thanks again for your input and advice.

 

 

[Pikachu2000]

That is actually part of the query string using MySQL's CONCAT_WS() function. It will take the value of the first_name and last_name fields in the database (you need to change the names to whatever your fields are named) and concatenate them with a space between them. The AS label part simply gives the alias label to the result. So assuming you had two records in the database:

first_name  |  lastname

Bob              Smith

Jane              Doe

 

then this code:

$query = "SELECT CONCAT_WS(' ', `first_name`, `last_name`) AS `label`";
$result = mysql_query($query);
while( $array = mysql_fetch_assoc($result) ) {
     echo $array['label'] . '<br>';
}

 

Would return

Bob Smith

Jane Doe

[lazerbrains]

OK, so here is what I have so far....

<?php 
echo '<select name= "First_Name" , "Last_Name">';
while( $array = mysql_fetch_assoc($result) ) {
     echo "<option value=\"{$array['value']}\">{$array['First_Name']}</option>\n";
}
echo '</select>';?>

</p>

This is currently working, and is pulling the 'First_Name' field correctly from the database and into the "select" dropdown menu. Now I am trying to get it to pull the 'Last_Name' as well, but when I do what you suggested for that, it is just a blank dropdown.

 

What do I need to add to get the last name to pull as well?

[Pikachu2000]

Post your database query string also, please.

[lazerbrains]

Currently I am just pulling everything from the DB.

 

$result = mysql_query("SELECT * FROM 2011members")

or die(mysql_error());

 

But all I need is the "First_Name', 'Last_Name', and 'Email' fields.

 

That is how they are named as well.

 

[lazerbrains]

Ok. I got it working by doing it this way.:

<?php 
echo '<select name= "First_Name" , "Last_Name">';
while( $array = mysql_fetch_assoc($result) ) {
$text_for_select = $array["First_Name"] . " " . $array["Last_Name"].;
echo "<option value=\"$text_for_select\">$text_for_select</option>\n";
}
echo '</select>';?>

 

Works great! It is pulling the First and Last name fields from the Database.

 

Now the last thing I want to do is to have the form send to the email of the persons name they selected. All email records have associated email addresses. how can I have the $to variable from the 'mail' command = the associated email address?

 

 

 

[BlueSkyIS]

just one thing: this will create an invalid select:

 

echo '<select name= "First_Name" , "Last_Name">';

 

The name value for a form element should be a single, quoted string immediately following the equal sign. so something like this would work:

 

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

 

If you want to send an email to the selected person, you should user their record id as the value for each <option>, then when the form is submitted you can get the posted id and look up the email address using that id.