I am unsure of the best process to use when inserting related data into separate tables. For example, I have a registration form which puts a users login details in one table, their profile information in another, and then some assorted validation data in another.
All the tables have been set up with primary keys, however it is important that all data can be retrieved with one key, ie. a select query with joins.
So, if I write the username and password to one table, how do I put that records primary key into the next table, preferably without making a lookup field?
Perhaps I should remove the primary keys of the other two tables and just push the primary key of the first table in there, but then arises the issue of how to get the primary key back out again.
Any ideas on this one? Thanks
Multiple table insert question
Moderator: General Moderators
I guess what I'm really asking is, if I make an entry into a table with:
Do I really have to go:
... and then read the returned results to get access to the primary key that was auto-generated so that I can match the subsequent table entries to the first?
Code: Select all
$sql = "INSERT INTO `member_auth` (`ID`, `Username`, `Password`) VALUES ('', '" . $_POSTїemail] ."', '" . md5($_POSTїpassword]) . "')";
if(!($result = mysql_query($sql))) die(mysql_error());
if (mysql_affected_rows() < 1) $_SESSIONї'ERROR'] .= '<li>Database Error!</li>';Code: Select all
$sql = "SELECT ID FROM " . $STDB_USER . " WHERE Username='" . addslashes($_POSTїemail]) . "' AND Password='" . md5($_POSTїpassword]) . "' LIMIT 1";
if(!($result = mysql_query($sql))) die(mysql_error());