Insert into multiple tables linked by foreign keys (PHP, MySQL)

[endouken]

Hi all,

 

New to php/mysql.  I've managed to get (in my opinion) quite far surfing forums for any problem I encounter, but now I’ve hit a wall and can't find the solution anywhere.  As most of the previous answered I found were on this site, I hope you don't mind me asking my question!

 

The problem:  I have two tables: customerdetail (with primary key custid) and customeraddress (with primary key addressid and foreign key custid).

 

My insert statement was working perfectly inserting into both tables from one form.  As soon as I create the foreign key for customeraddress (in phpmyadmin) my insert statement only working for customerdetail (i.e the table without the foreign key), and nothing gets inserted into customeraddress.

 

I suspect this is because I’m inserting into both tables using one php form and therefore customeraddress can't pull the foreign key from customerdetail as it’s not been created yet...but i have no clue how to solve this or if it really is the problem.

 

Appreciate any suggestions, musings or code/ideas!

 

Thanks so much,

 

Tom.

 

PHP Code:

 

<?

$localhost="xxx";

$username="xxx";

$password="xxx";

$database="xxx";

 

$firstname=$_POST['firstname'];

$surname=$_POST['surname'];

$dob="{$_POST['dobyear']}-{$_POST['dobmonth']}-{$_POST['dobday']}";

 

if (isset ($_POST['permissionnewsletter']) || (!empty ($_POST['permissionnewsletter'])))

{ $permissionnewsletter = "Yes"; }

else

{ $permissionnewsletter = "No"; }

 

$email=$_POST['email'];

$userpassword=$_POST['userpassword'];

$telephone=$_POST['telephone'];

 

mysql_connect($localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

 

$insertintocustomerdetail = "INSERT INTO customerdetail VALUES ('custid','$firstname','$surname','$dob','$permissionnewsletter','$email','$userpassword','$telephone',now())";

mysql_query($insertintocustomerdetail);

 

$addressline1=$_POST['addressline1'];

$addressline2=$_POST['addressline2'];

$cityortown=$_POST['cityortown'];

$county=$_POST['county'];

$postcode=$_POST['postcode']=strtoupper(@$_REQUEST['postcode']);

 

$insertintoaddresstable = "INSERT INTO addresstable VALUES ('addressid','custid','$addressline1','$addressline2','$cityortown','$county','$postcode',now())";

mysql_query($insertintoaddresstable);

 

mysql_close();

?>

 

My MYSQL (exported from phpmyadmin) looks like this:

 

CREATE TABLE `addresstable` (

  `addressid` int(11) unsigned zerofill NOT NULL auto_increment,

  `custid` int(11) unsigned zerofill default NULL,

  `addressline1` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `addressline2` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `cityortown` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `county` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,

  `postcode` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,

  `creationdateaddresstable` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`addressid`),

  KEY `custid` (`custid`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

CREATE TABLE `customerdetail` (

  `custid` int(11) unsigned zerofill NOT NULL auto_increment,

  `firstname` varchar(50) default NULL,

  `surname` varchar(50) default NULL,

  `dob` date default NULL,

  `permissionnewsletter` varchar(50) default NULL,

  `email` varchar(50) default NULL,

  `userpassword` varchar(50) default NULL,

  `telephone` varchar(50) default NULL,

  `creationdatecustomerdetail` timestamp NOT NULL default CURRENT_TIMESTAMP,

  PRIMARY KEY  (`custid`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75 ;

 

[Pikachu2000]

You need to insert into the table from which you will need the id number from, and that table must have a primary key that is auto_increment. Then you get that id using mysql_insert_id(), which you can in turn use as the relating key in the other table.

 

pseudo-code

$error = FALSE;
$query = "INSERT INTO table (pk_id, field1, field2) VALUES ('', 'value1', 'value2')";
if( $result = mysql_query($query) ) {
     if( mysql_affected_rows() > 0 ) {
          $id = mysql_insert_id();
     } else {
          // no record inserted, therefore error condition exists
          echo "No record inserted. Cannot proceed.";
          $error = TRUE;
     }
} else {
     // query failed to execute
     echo "Query Failed";
     $error = TRUE;
}
if( $error !== TRUE ) {
     $query = "INSERT INTO table2 ( fk_id, field1, field2 ) VALUES ( $id, 'value1', 'value2' )"; // Where $id holds the value of the primary key from the previous query . . . 
     // etcetera
}

[redarrow]

 

 

php has foreign keys but from 5.0

 

 

more info link.

http://www.techrepublic.com/article/an-introduction-to-foreign-keys-and-referential-integrity-in-mysql/6035435

 

what been said and posted is better thu

[endouken]

 

 

php has foreign keys but from 5.0

 

 

more info link.

http://www.techrepublic.com/article/an-introduction-to-foreign-keys-and-referential-integrity-in-mysql/6035435

 

what been said and posted is better thu

 

Do you mean php version 5.0 onwards?  I'm using 5.2.6...

 

@pickahu2000 - thanks buddy, i'll give this a try 

 

 

[redarrow]

Yes mate from 5.0 or above.

 

Lets both work this out ...

 

If you use auto increment for a table, you get a automatic number that true.

 

The number you get is the first number on mysql, they all call that the primarry_key,

 

now if i set a colum to a varchar,

 

i can add loads of more numbers,

 

if i make a rand number, of 6 numbers long insert it into that varchar we just made,  we got another unique key for that user.

 

we can play all day like this we can even use letters and unusauall names.

 

select users from accounts where id="000001" AND secuity_number="000012";

 

were getting a person with the id of 000001 and secuity number of 000012

 

this is better then all them foregn keys lol

 

only my opionion

 

i use this method on all my servers that link from one server to another, all over the place as my project on the web grows.

 

i also SALT and md5 my numbers away from prying eyes.

[redarrow]

This will create 6 numbers for a user for there secuity id.

<?php

/**
* @author 
* @copyright 2011
*/

$number=rand(000000,999999);

echo $number;


?>

 

You can make this more secure.

 

 

[endouken]

This will create 6 numbers for a user for there secuity id.

<?php

/**
* @author 
* @copyright 2011
*/

$number=rand(000000,999999);

echo $number;


?>

 

You can make this more secure.

 

I get ya, and then insert this into both tables so they have a common number which can be used to identify which address belongs to which customer?

 

Wouldn't this have the (very small at first) potential though to create a duplicate id, as we're creating the id randomly?  If we made the fields unique, the more rows we have the more chance there is that a randomly created number will match an existing one.  We could then code it so it simply generates another id but then the pit fall is the potential to get caught in a loop where the id being generated is (coincidentally) already in existence multiple times in a row.  Not an issue for the customer table, but if we were doing this for a forum like phpfreaks where millions of posts are made it could be a realistic problem.

 

It's certainly a work around, and one i may well take, so thanks alot bud for the idea and taking the time to post - I appreciate it.  I'm sure this isn't best practice though so if you (or anyone else) has any thoughts why a foreign key would cause the table no longer to accept a php insert (even when set to null) i'd love to hear from you.

 

Cheers,

 

Tom.

[redarrow]

you got to inprove it yes maybe a duplication but i dont think so really,

 

but there loads you can do, all i done was open your abiltys, to look deeper and think more.

 

you can get the dob date of birth use two last digets, and home adress ,loads think think think, im going mad lol

[DavidAM]

Do NOT duplicate data to use as a primary key, that is just a WASTE of storage and processing.

 

Do NOT use random values for the primary key, as you said, you will eventually run into duplicate issues.

 

Do NOT use VARCHARS for the primary key, unless you have a very compelling reason to do so.

 

Use the auto_increment for the primary key unless the data has some unique component that will work (i.e. phone number) and even then, you would have to have a compelling reason - what do you do if someone does NOT have a phone, or they change their number?

 

In your original code, you need to look at a few things. Why are you specifying the string "custid" for the custid? It is an integer and a string is invalid. mySql seems to just use the column's default when assigned an invalid value (a stupendously bad idea IMO). Also, you were supplying a string for the custid in the second table. Since you have defined this column as allowing NULLs, the server is using the default - which is NULL. Since this field is a foreign key, it should NOT allow NULL unless it is possible to have a customer address without a customer. Finally, use the LAST_INSERT_ID() function to get the custid when inserting the address. The two queries you want to execute are:

 

$insertintocustomerdetail = "INSERT INTO customerdetail VALUES (NULL,'$firstname','$surname','$dob','$permissionnewsletter','$email','$userpassword','$telephone',now())";
mysql_query($insertintocustomerdetail);

$addressline1=$_POST['addressline1'];
$addressline2=$_POST['addressline2'];
$cityortown=$_POST['cityortown'];
$county=$_POST['county'];
$postcode=$_POST['postcode']=strtoupper(@$_REQUEST['postcode']);

$insertintoaddresstable = "INSERT INTO addresstable VALUES (NULL,LAST_INSERT_ID(),'$addressline1','$addressline2','$cityortown','$county','$postcode',now())";
mysql_query($insertintoaddresstable);

 

By the way, you need to sanitize those inputs. You are leaving yourself open to SQL injection.

 

 

[endouken]

Do NOT duplicate data to use as a primary key, that is just a WASTE of storage and processing.

 

Do NOT use random values for the primary key, as you said, you will eventually run into duplicate issues.

 

Do NOT use VARCHARS for the primary key, unless you have a very compelling reason to do so.

 

Use the auto_increment for the primary key unless the data has some unique component that will work (i.e. phone number) and even then, you would have to have a compelling reason - what do you do if someone does NOT have a phone, or they change their number?

 

In your original code, you need to look at a few things. Why are you specifying the string "custid" for the custid? It is an integer and a string is invalid. mySql seems to just use the column's default when assigned an invalid value (a stupendously bad idea IMO). Also, you were supplying a string for the custid in the second table. Since you have defined this column as allowing NULLs, the server is using the default - which is NULL. Since this field is a foreign key, it should NOT allow NULL unless it is possible to have a customer address without a customer. Finally, use the LAST_INSERT_ID() function to get the custid when inserting the address. The two queries you want to execute are:

 

$insertintocustomerdetail = "INSERT INTO customerdetail VALUES (NULL,'$firstname','$surname','$dob','$permissionnewsletter','$email','$userpassword','$telephone',now())";
mysql_query($insertintocustomerdetail);

$addressline1=$_POST['addressline1'];
$addressline2=$_POST['addressline2'];
$cityortown=$_POST['cityortown'];
$county=$_POST['county'];
$postcode=$_POST['postcode']=strtoupper(@$_REQUEST['postcode']);

$insertintoaddresstable = "INSERT INTO addresstable VALUES (NULL,LAST_INSERT_ID(),'$addressline1','$addressline2','$cityortown','$county','$postcode',now())";
mysql_query($insertintoaddresstable);

 

By the way, you need to sanitize those inputs. You are leaving yourself open to SQL injection.

 

I would totally hug you right now.  Sorry for the delay in sending some thanks/love.  Exactly what i wanted, and you editing the code like you did really helps me learn rather than trial and error - so i honestly appreciate the time you've taken to do that. 

 

RE injection, i'm gunna get the code working first - then secure it. 

 

Tom.