Hi,
Could you please help me to figure out what is wrong with the following database design:
Profession Table:
visitor_profession varchar(40)
user_id int
id int primary key
Prospects Table:
country varchar(25)
zipcode int
city varchar(25)
address varchar(25)
company varchar(25)
state varchar(25)
telephone varchar(12)
fax varchar(12)
email varchar(50)
id int primary key
user_id int
User Table:
lastname varchar(15)
firstname varchar(15)
id int primary key
I am trying to use the following code to get data from a form into this database:
<?php
if ($submit) {
$db = mysql_connect("mysql.hampsteadlighting.com", "hampstead", "hampstead2");
mysql_select_db("prospects",$db);
$sql = "INSERT INTO prospects (id,visitor_company,visitor_address,visitor_city,visitor_state_province,visitor_country,visitor_zipcode,visitor_telephone,visitor_fax,visitor_email)
VALUES ('$id','$visitor_company','$visitor_address','$visitor_city','$visitor_state_province','$visitor_country','$visitor_zipcode','$visitor_telephone','$visitor_fax','$visitor_email')";
$result = mysql_query($sql);
$sql = "INSERT INTO user (id,visitor_lastname,visitor_firstname)
VALUES ('$id','$visitor_lastname','$visitor_firstname')";
$result = mysql_query($sql);
$sql = "INSERT INTO profession (visitor_profession_specified)
VALUES ('$visitor_profession_specified')";
$result = mysql_query($sql);
$profession = array ("lighting showroom", "designer", "architect", "electrical
supply", "purchasing agent", "sales representative", "other");
while( list($profession_id, $value) = each($profession)) {
if(value == "on")
{
db_query("INSERT INTO profession".
"(user_id, profession_id) ".
"VALUES ($user_id, $profession_id)");
}
}
echo "Thank you for your interest in Hampstead Lighting's imported European light fixtures!\n Your request will be processed within 1-5 business days.";
} else{
// display form
?>
<form method="POST" action="<?php echo $PHP_SELF?>">
<p>LAST NAME:<input type="text" name="visitor_lastname" size="25">
FIRST NAME:<input type="text" name="visitor_firstname" size="25">
<p>COMPANY NAME:<input type="text" name="visitor_company" size="39">
<p>ADDRESS:<input type="text" name="visitor_address" size="48">
<p>CITY:<input type="text" name="visitor_city" size="20">
STATE/PROVINCE:<input type="text" name="visitor_state_province" size="20">
<p>COUNTRY:<input type="text" name="visitor_country" size="20">
ZIP CODE:<input type="text" name="visitor_zipcode" size="23">
<p>PHONE:<input type="text" name="visitor_telephone" size="23">
FAX:<input type="text" name="visitor_fax" size="29">
<p>E-MAIL:<input type="text" name="visitor_email" size="23">
<p><font face="arial" size="2"><br>
<b>PLEASE INDICATE THE FOLLOWING:</b></font></p>
<p><font face="arial" size="2"><b>Profession:</b></font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Lighting Showroom</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Designer</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Architect</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Electrical Supply Wholesaler</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Purchasing Agent</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Sales Representative</font></p>
<p><font face="arial" size="2"><input type="checkbox" name="profession[]" value="on">Other</font></p>
<b>Please Specify if Your Profession is not Listed</b>
<p><font face="arial" size="2">PROFESSION:<input type="text" name="visitor_profession_specified" size="48">
<p><font face="arial" color="red" size="2"><b>REQUESTS CAN
NOT BE PROCESSED WITHOUT TOTALLY COMPLETED FORM</b></font>
<p><input type="submit" name="submit" value="Submit" value="Reset">
</table>
</FORM>
<?php
}
?>
The fields that I am using to link the tables have apparently been setup wrong. I am expecting the user table id to be inputted in each table and that's not happening.
I hope this is not too confusing. Please help if you can!
My "supposed array" is also not being inputted in the profession table. I am under the assumption that an array automatically assigns its members numeric values and that value is $profession_id,apparently I am assuming wrong.
If you can help with my understanding of any part of this process please do.
Thank you,
Debra
