I have a database with four tables: name, meaning, gender, origin, and i want to have the origin field automatically populated from the database rather than having to type the code for all the selections in the html.

Below is what i've been playing with but I can't make it work no matter where i put the code. I would appreciate any help - is the code right? and where do i place it? Thanks in advance.

$sql = 'select origin, originID from origin';
$rsOrigins= mysql_query($sql, $connection) or die(mysql_error()); 
$row_rsOrigins= mysql_fetch_assoc($rsOrigins);
?>
<select name="origin" id="origin">
                  <option value="any">Any</option>
<?php
do { ?>
                
                  <option value="<?php echo $row_rsOrigins['originID'] ?>"><?php echo $row_rsOrigins['origin'] ?></option>

<?php } while ($row_rsOrigins = mysql_fetch_assoc($rsOrigins)); ?>
                </select>

This is my html:

<html>
	<head>
		<title>Baby Names Database</title>
	</head>
	<body>
		<h1>Baby Names</h1>
		<h3>Please fill in the blanks below, and We'll return baby names for you to browse.</h3>

		
			<table border = "1" align = "center" style="font-family: verdana; font-size: small">
			<form method = "post" action = "practiceBaby5.php">
				<tr bgcolor="#cae1ff">
					<td align = "left">
						Gender:
					</td>
					<td>
<input type = "radio" name = "gender" value = "male"/>Boys Names
						<input type = "radio" name = "gender" value = "female"/>Girls Names
						<input type = "radio" name = "gender" value = ""/>Both
					</td>
				</tr>
				<tr bgcolor="#ffe1ff">
					<td align = "left">
						Meaning:
					</td>
					<td>
						<input type = "text" name = "meaning" value = ""/>
					</td>
				</tr>
				<tr bgcolor="#ffe1ff">
					<td align = "left">
						Name Begins With:
					</td>
					<td>
						<input type = "text" name = "name" value = ""/>
					</td>
				</tr>
				<tr bgcolor="#cae1ff">
					<td align = "left">
						Origin
					</td>
					<td>								
					
<select name = "origin" id = "origin">
							<option selected value = "">Any</option>
							<option value = "african">African</option>
							<option value = "anglo">Anglo</option>
							<option value = "arabian">Arabian</option>
							<option value = "arabic">Arabic</option>
							<option value = "aramaic">Aramaic</option>
							<option value = "armenian">Armenian</option>
							<option value = "arthurian">Arthurian</option>
							<option value = "basque">Basque</option>
							<option value = "celtic">Celtic</option>
							<option value = "chamoru">Chamoru</option>
						</select>	      
						
					</td>
				</tr>							

			</table><p /><center>
<input type = "submit" value = "Show me Names"/>
</center>	
			</form>
	</body>
</html>

This is my php:

<?php
// MySQL Connection Information
$server = "localhost";
$username = "";
$password = "";
$dbname = "baby_names";

// MySQL Connect String
$connection = mysql_connect($server,$username,$password) or die("Can't Connect to Mysql Server:

".mysql_error());

// Select the database
mysql_select_db($dbname) or die("Can't connect to database: ".mysql_error());


               
$gender = mysql_real_escape_string($_POST['gender']);
$meaning = mysql_real_escape_string($_POST['meaning']);
$name = mysql_real_escape_string($_POST['name']);
$origin = mysql_real_escape_string($_POST['origin']);
if($gender) {
$whereArr[] = "gender = '" . $gender . "' ";
}
if($name) {
$whereArr[] = "name LIKE '" . $name . "%'";
}
if($meaning) {
$whereArr[] = "meaning LIKE '%" . $meaning . "%'";
}
if($origin) {
$whereArr[] = "origin = '" . $origin . "' ";
}
if(count($whereArr)) {
$where = @implode(" AND ", $whereArr);
$where = ' WHERE '.$where;
}

$sql = "SELECT * FROM names $where";

/*
if ($gender == 'both') { // no specific gender
if ($origin == 'any') { // no specific origin
$sql = 'select * from names';
else { // an origin was specified
$sql = "select * from names where origin = '".$origin."'";
}

else { // a gender was specified

if ($origin == 'any') { // no specific origin
$sql = "select * from names where gender ='".$gender."'";
else { // an origin was also specified
$sql = "select * from names where origin = '".$origin."' and gender ='".$gender."'";
}
*/

    


//execute SQL query and get result
$sql_result = mysql_query($sql, $connection) or die(mysql_error());

?>
<table border="0" align="center" style="font-family: verdana; font-size: small">
<tr>
<th style="background: #ffd">Name</th>
<th style="background: #efe">Gender</th>
<th style="background: #fef">Origin</th>
<th style="background: #e3e4fa">Meaning</th>
</tr>

<?php
// Loop through the data set and extract each row into it's own variable set
while ($row = mysql_fetch_array($sql_result))
{
//extract($row);
?>
<tr>
<td style="background: #ffd"><?php echo $row['name'];?></td>
<td style="background: #efe"><?php echo $row['gender'];?></td>
<td style="background: #fef"><?php echo $row['origin'];?></td>
<td style="background: #e3e4fa"><?php echo $row['meaning'];?></td>
</tr>
<?php
}

?>

I didn't test it, but tis should work for you.

<?php
$sql = 'select origin, originID from origin';
$rsOrigins= mysql_query($sql, $connection) or die(mysql_error()); 

echo '<select name="origin" id="origin">\n'.
      '<option value="any">Any</option>\n';
while($row_rsOrigins = mysql_fetch_array($result))
{
    echo '<option value="'.$row_rsOrigins['originID'].'">'.$row_rsOrigins['origin'].'"</option>\n';
}
echo '</select>\n';
?>

Thank you for your reply - I am trying this everywhere but does your code replace the

<select name="origin" id="origin">
<option selected value="">Any</option>
<option value="african">African</option>
</selection>

part of the html file or does it get placed in the .php file. If in the php file, does it matter what order the code is placed?

I am expecting to have all the Origins in my database reflected in the <select> dropdown of the html - i don't mind coding all the <option>'s into the html but i understand php will do it for me :) i just wish i could understand php lol

thanks for any further help as it will save a good deal of work if i add more origins to the database and they can appear in the html dropdown automatically.

hi phpnewbie! did kireol's code work? it should..
you just have to fetch the data from the table then loop it inside the select tag. :)


-enim

This article has been dead for over six months. Start a new discussion instead.