Populate Drop Box from Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ethoemmes
Forum Commoner
Posts: 26
Joined: Thu Aug 18, 2005 4:11 pm

Populate Drop Box from Database

Post by ethoemmes »

Hi

Could someone help me with the following?

I have a form which is used to collect user information, one of the fields I require is Country. I have set up a table in my database with details of all countries and a country ID.

I would like to populate a drop box with these countries and then when I load form.php which appends this data to the the database for the script to append the Country ID to the database.

I have included the HTML form I am using and form.php.

HTML FORM

Code: Select all

<HTML>
<HEAD>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<meta http-equiv="Content-Language" content="en-us">
<TITLE>Registration Form</TITLE>
</HEAD>
<BODY>
<FORM ACTION="form.php" METHOD="post">
Please provide the following contact information:</P>
<BLOCKQUOTE>
<TABLE>
<TR>
<TD ALIGN="right">
<EM>Title</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="Title" SIZE=35>
</TD>
</TR>
<TR>
<TD ALIGN="right">
<EM>First Name</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="FirstName" SIZE=35>
</TD>
</TR>
<TR>
<TD ALIGN="right">
<EM>Middle Name</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="MiddleName" SIZE=35>
</TD>
</TR>
<TR>
<TD ALIGN="right">
<EM>Last Name</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="LastName" SIZE=35>
</TD>
</TR>
<TR>
<TD ALIGN="right">
<EM>E-mail</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="Email" SIZE=25>
</TD>
</TR>
<TR>
</TR>
<TR>
<TD ALIGN="right">
<EM>Organization</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="Organization" SIZE=36></TD>
</TR>
<TR>
<TD ALIGN="right">
<em>Address</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="Address1" SIZE=35></TD>
</TR>
<TR>
<TD ALIGN="right">
<em>Address</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="Address2" SIZE=35></TD>
</TR>
<tr>
<TD ALIGN="right">
<em>City</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="City" SIZE=35></TD>
</tr>
<tr>
<TD ALIGN="right">
<em>State/County</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="County" SIZE=35></TD>
</tr>
<TR>
<TD ALIGN="right">
<em>Post Code</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="PostCode" SIZE=35></TD>
</TR>
<TR>
<TD ALIGN="right">
<em>Country</em></TD>
<TD>
<INPUT TYPE=TEXT NAME="Country" SIZE=35></TD>
</TR>
<TR>
<TD ALIGN="right">
<EM>Work Phone</EM></TD>
<TD>
<INPUT TYPE=TEXT NAME="Phone" SIZE=25 MAXLENGTH=25>&nbsp;
</TD>
</TR>
</TABLE>
</BLOCKQUOTE>
<INPUT TYPE=SUBMIT VALUE="Submit Form">
<INPUT TYPE=RESET VALUE="Reset Form">
</FORM>
</BODY>
</HTML>
PHP SCRIPT

The country ID should be included where I have inserted the word COUNTRYID.

Code: Select all

<?php

include "config.php";

$table = "tblCustomer";	// database table

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

$insert = mysql_query("insert into $table values ('', 0,'Web','".$_POST['Title']."', '".$_POST['FirstName']."', '".$_POST['MiddleName']."', '".$_POST['LastName']."', '".$_POST['Organisation']."','".$_POST['Address1']."', '".$_POST['Address2']."', 'Address3', '".$_POST['City']."', '".$_POST['PostCode']."', '".$_POST['State']."', 'COUNTRYID', '".$_POST['Email']."', '".$_POST['Phone']."','')", $link)
or die("Could not insert data because ".mysql_error());

mysql_close();

echo "Thank you for registering";

?>
TIA
taldos
Forum Commoner
Posts: 39
Joined: Mon Aug 23, 2004 8:47 am
Location: Philadelphia

Post by taldos »

I'm not sure if this is what you are trying to do, but it sounds like you want to create a select menu populated from data in your database.

Code: Select all

$dropdown = "";
$my_query = mysql_query("SELECT select_name, select_id FROM table") or die("failed");

while($row=mysql_fetch_array($my_query))
{
  $dropdown .= "<option value=".$row['select_id'].">".$row['select_name'];
}
then all you have to do is place $dropdown into the template file. That will give you a drown down popluated by your database. Make sure to add it after the select menu declaration and closing

Code: Select all

<select name="country">
{DB_LIST}
</select>
Though I have been told that I am sometimes completly wrong. But that's the way I do it.

Cheers.
BruceT
Forum Newbie
Posts: 14
Joined: Sat Aug 27, 2005 10:23 am

Post by BruceT »

It's a good idea to use proper HTML as well:

Code: Select all

$dropdown .= "<option value=".$row['select_id'].">".$row['select_name'];
should be

Code: Select all

$dropdown .= "<option value=\"".$row['select_id']."\">".$row['select_name']."</option>\n";
or even

Code: Select all

$dropdown .= sprintf("<option value=\"%s\">%s</option>\n", $row['select_id'], $row['select_name']);
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Post by William »

Also when coding tables it is somtimes easier to use tabs so when you go back through its easy to understand. For instance like this:

Code: Select all

<html>
<head>
	<title>Registration Form</title>
</head>
<body>

<form action='form.php' method='post'>

<table>
	<tr>
		<td colspan='2'>
			<p>Please provide the following contact information:</p>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Title</em>
		</td>
		<td>
			<input type='text' name='Title' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>First Name</em>
		</td>
		<td>
			<input type='text' name='FirstName' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Middle Name</em>
		</td>
		<td>
			<input type='text' name='MiddleName' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Last Name</em>
		</td>
		<td>
			<input type='text' name='Last Name' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>E-mail</em>
		</td>
		<td>
			<input type='text' name='Email' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Organization</em>
		</td>
		<td>
			<input type='text' name='Organization' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Address</em>
		</td>
		<td>
			<input type='text' name='Address1' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Address2</em>
		</td>
		<td>
			<input type='text' name='Address2' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>City</em>
		</td>
		<td>
			<input type='text' name='City' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>State/County</em>
		</td>
		<td>
			<input type='text' name='County' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Post Code</em>
		</td>
		<td>
			<input type='text' name='PostCode' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Country</em>
		</td>
		<td>
			<input type='text' name='Country' size='35'>
		</td>
	</tr>
	<tr>
		<td align='right'>
			<em>Work Phone</em>
		</td>
		<td>
			<input type='text' name='Phone' size='35'>
		</td>
	</tr>
	<tr>
		<td align='center' colspan='2'>
			<input type='submit' value='Submit Form'>
			<input type='reset' value='Reset Form'>
		</td>
	</tr>
</table>

</form>

</body>
</html>
Now coders code diffrently so that could be harder to understand.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

less confusing this might be:

Code: Select all

$my_query = mysql_query("SELECT select_name, select_id FROM table") or die("failed"); 

echo "<select name=\"countryid\">";
while($row=mysql_fetch_assoc($my_query)) 
{ 
  echo "<option value=\"".$row['select_id']."\">".$row['select_name']."</option>";
} 
echo "</select>";
Post Reply