Multiple table insert question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Multiple table insert question

Post 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
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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?
teniosoft
Forum Newbie
Posts: 15
Joined: Tue Feb 03, 2004 1:01 am
Location: Portland, Or

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

[php_man]mysql_insert_id[/php_man]
Post Reply