Drop Down from Database Code Table

[jrotunda85]

I am setting up a database currently and I want frequently used values stored in a Code table on my database. The Code table is setup with the following columns: Type_CD" , "Code" , "Description". For example, if I wanted to pull a list of states, I would want all records that are "Type_CD" number 2, which would have a variety of codes ranging from 20001 to 20052. So, let's say the state of VA would be a Type_CD of "2", a Code of "20047", and a description of "VA".

With this in mind, how would I create a drop down menu in PHP that dynamically generates these values in a form? For example, let's say I wanted a registration form that grabbed all of Type_CD "2" from the Database and then populated these values in the "State" Field on the Form?

Thanks for the help!

[smantscheff]

First of all this looks like really bad database design, storing records of different types in the same table.
That said, go along the line (assuming mysql as your database):

$select = '<select>';
$result = mysql( "select * from code there type_cd = '2'" );
while ($row = mysql_fetch_object( $result )) {
  $select .= "<option value='row->id'>$row->description</option>";
}
$select .= '</select>';

and insert the $select variable in your form.

[jrotunda85]

First of all this looks like really bad database design, storing records of different types in the same table.
That said, go along the line (assuming mysql as your database):

$select = '<select>';
$result = mysql( "select * from code there type_cd = '2'" );
while ($row = mysql_fetch_object( $result )) {
  $select .= "<option value='row->id'>$row->description</option>";
}
$select .= '</select>';

and insert the $select variable in your form.

Thanks for the response. I've tried playing around with the code and for some reason I can't get it to display the dropdown, I'm not sure if I'm missing something here? I did; however, come up with some code this morning below that is working but it does not pass the value (the code) through on the form. So, for example, it displays everything and puts the correct number in "value" but when the form posts, the value is 0 instead of the actual value? Here's the code for the addition:

Form

Manufacturer: <select name="manu" value="manu">
<?php
include 'dbconnection.php';

$query = "Select Code,Description FROM Code WHERE Type_CD = 2";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
 
	echo("<option select value=\"0\">Select Manufacturer</option>");
 
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
			if($row[0]=='Baccarat')$dtext = "selected";
			else $dtext = "";
			print("<option value=\"$row[0]\">$row[1]</option>");
		 }
	}
	else { 
      echo "No rows found!"; 
	} 
	mysql_free_result($result);
?>
</select><br>

Processing Code

//Create Post Variables
$Rat_App = $_POST['Rat_App'];
$Rat_Draw = $_POST['Rat_Draw'];
$Rat_Flavor = $_POST['Rat_Flavor'];
$Rat_Burn = $_POST['Rat_Burn'];
$Rat_Finish = $_POST['Rat_Finish'];
$Rat_Overall = $_POST['Rat_Overall'];
$Manu = $_POST['manu'];
$UserID = $_SESSION['User_ID'];

//Create sql Statement Variable
$SQL = "INSERT INTO Cigar_Reviews VALUES ('NULL','$Manu','$UserID','$Rat_App','$Rat_Draw','$Rat_Flavor','$Rat_Burn','$Rat_Finish','$Rat_Overall')";

//Insert Entry
if (!mysql_query($SQL))
  {
  die('Error: ' . mysql_error());
  }
echo "Record added";
?>

Also, as far as the db design, the code table is being used mainly to encode/decode values. This allows the values to be stored as numbers in the database as opposed to text which, from what I understand, is a faster, more efficient way of storing and querying things. Is this not correct?

[jrotunda85]

Actually that code fixed it. I was accidentally using the _SESSION variable instead of the _POST variable. All working now! :icon_redface:

I'd try this -

For the select code:

Manufacturer: <select name="manu" value="manu">
<?php
include 'dbconnection.php';
 
$query = "Select Code,Description FROM Code WHERE Type_CD = 2";
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());
 
	echo "<option select value=\"0\">Select Manufacturer</option>";
 
	if (mysql_num_rows($result) > 0) { 
		 while ($row = mysql_fetch_row($result)) {
			$dtext="";
                        if($row[0]=='Baccarat')$dtext = ' selected= "selected"';
			echo "<option value=\"$row[0]\"{$dtext}>$row[1]</option>";
		 }
	}else{ 
                 echo "No rows found!"; 
	} 
	mysql_free_result($result);
?>
</select><br />

For the Processing code:

//Create Post Variables
$Rat_App = mysql_real_escape_string($_POST['Rat_App']);
$Rat_Draw = mysql_real_escape_string($_POST['Rat_Draw']);
$Rat_Flavor = mysql_real_escape_string($_POST['Rat_Flavor']);
$Rat_Burn = mysql_real_escape_string($_POST['Rat_Burn']);
$Rat_Finish = mysql_real_escape_string($_POST['Rat_Finish']);
$Rat_Overall = mysql_real_escape_string($_POST['Rat_Overall']);
$Manu = mysql_real_escape_string($_POST['manu']);
$UserID = mysql_real_escape_string($_SESSION['User_ID']);
 
//Create sql Statement Variable
$SQL = "INSERT INTO Cigar_Reviews SET manu = '$Manu',user_id='$UserID',app='$Rat_App',draw='$Rat_Draw',flavor='$Rat_Flavor',burn='$Rat_Burn',finish='$Rat_Finish',overall='$Rat_Overall')";
 
//you'll need to change the field names above, but you get the idea. This is just a different syntax to the VALUES method - which I've always found problematic with many fields.

//Insert Entry
if (!mysql_query($SQL)){
  die('Error: ' . mysql_error());
}else{
  echo "Record added";
}

Bugger, you posted while I was writing. Ok.

[jrotunda85]

Bugger, you posted while I was writing. Ok.

Sorry about that! But just out of curiosity, what's the benefit to using the "mysql_real_escape_string" ?

This sanitizes the string. You should never passs any data from a form directly into a DB - it should always be cleaned/sanitized. In addition, data should be validated - check for numbers/string/length/value range/ etc.

Forms can be spoofed and rubbish placed in the spoofed fields

e.g.

I could write a form on my own website and do this:

<form method="post" action"http://www.jrotunda.com/formhandler.php">
 <input name="manu" type="hidden" value="rubbish value" />
  ..other fields with silly content..

 <input type="submit" name="submit" value="send" />
</form>