Page 1 of 1

Multiple table insert question

Posted: Tue Feb 03, 2004 8:28 pm
by Stryks
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

Posted: Tue Feb 03, 2004 10:52 pm
by Stryks
I guess what I'm really asking is, if I make an entry into a table with:

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&#1111;'ERROR'] .= '<li>Database Error!</li>';
Do I really have to go:

Code: Select all

$sql = "SELECT ID FROM " . $STDB_USER . " WHERE Username='" . addslashes($_POST&#1111;email]) . "' AND Password='" . md5($_POST&#1111;password]) . "' LIMIT 1"; 
if(!($result = mysql_query($sql))) die(mysql_error());
... 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?

Posted: Wed Feb 04, 2004 4:25 am
by teniosoft
This is the way that I have always done it. If you use stored procedures in postgres and soon to be in mysql then that will alleviate the problem.

Posted: Wed Feb 04, 2004 7:48 am
by McGruff
[php_man]mysql_insert_id[/php_man]