p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Faulty Database Design


Message #1 by "Debra Scott" <hlawebservice@h...> on Tue, 26 Feb 2002 22:32:02
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,vis

itor_country,visitor_zipcode,visitor_telephone,visitor_fax,visitor_email) 

 

 VALUES 

('$id','$visitor_company','$visitor_address','$visitor_city','$visitor_stat

e_province','$visitor_country','$visitor_zipcode','$visitor_telephone','$vi

sitor_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!\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

Message #2 by David Cameron <dcameron@i...> on Wed, 27 Feb 2002 10:51:57 +1100
This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C1BF20.93196BE0

Content-Type: text/plain



Assuming I am reading your structure properly.



One comment on the db design, I would have a FK (Foreign Key) profession_id

in the users table rather than a FK in the profession table. If you think

about it the profession is a property of the user. At the moment with they

way you have it the user is a property of the profession.



Assuming you make that change, there are then problems with referential

integrity with your current methods. The links on IDs seem to be a little

messed up.



Recommended code order for the insertion (in pseudo code):



//check for profession

SELECT * FROM Profession WHERE visitor_profession 

'$visitor_profession_specified'



if rows returned 

	$ProfID = value from database

else

	INSERT profession

	$ProfID = get value using LAST_INSERT_ID()

// for ref on LAST_INSERT_ID() see:

// http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html



// assuming that don't want to check whether the user already exists

// insert this using the value for $ProfID for the FK that you got from

above

INSERT user

$UserID = get value using LAST_INSERT_ID()



// insert this using the value for $UserID for the FK

INSERT prospects



If this doesn't make complete sense I can expand on it. But this does mean

that you can run a query like this:



SELECT *

FROM Prospects

    INNER JOIN User ON

    User.id = Prospects.user_id

    INNER JOIN Profession ON

    Profession.id = User.profession_id



the query above would return all the data from the 3 tables. BTW sing SELECT

* is slower than specifying each of the fields, but can't be bothered to

write them all out.







	



	



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Debra Scott [mailto:hlawebservice@h...]

Sent: Wednesday, 27 February 2002 9:32 AM

To: beginning php

Subject: [beginning_php] Faulty Database Design





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,vis

itor_country,visitor_zipcode,visitor_telephone,visitor_fax,visitor_email) 

 

 VALUES 

('$id','$visitor_company','$visitor_address','$visitor_city','$visitor_stat

e_province','$visitor_country','$visitor_zipcode','$visitor_telephone','$vi

sitor_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!\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






$subst('Email.Unsub').





  Return to Index