Entry

How do you add User A to table A with ID A, then select table A and use the ID to add the ID of table A to table B all at once?

Jan 6th, 2003 18:03
Jean-Marc Molina, Eagle Eyes,

You want to insert the last inserted User id of TableA in TableB ?
Let's say that TableA is a Users table, a user is defined by an id, a 
name (first and last). TableB is a Comments table, it contains all the 
comments posted by a user from TableA (Users).
CREATE TABLE Users (
  Id int(11) NOT NULL auto_increment,
  FirstName varchar(100) NOT NULL default '',
  LastName varchar(100) NOT NULL default '',
  PRIMARY KEY  (Id)
) TYPE=MyISAM;
CREATE TABLE Comments (
  UserId int(11) NOT NULL default '0',
  Comment varchar(100) NOT NULL default ''
) TYPE=MyISAM;
Comments.UsersUserId is a foreign key, the comment from Comments was 
posted by a user from Users.
So let's say that a new user posts a comment. That user doesn't already 
exist in Users (it's NEW user):
@mysql_query ("INSERT INTO Users (FirstName, LastName) VALUES 
('$FirstName', '$LastName')") or die ("can't insert user in Users");
If the user was successfully inserted in Users, mysql_insert_id 
returns its Id, Users.Id:
$NewUserId = mysql_insert_id ();
Then you can use it to insert/post its comment:
@mysql_query ("INSERT INTO Comments (UserId, Comment) VALUES 
($NewUserId, '$Comment')") or die ("can't insert/post user comment");
Foreign keys are VERY VERY useful, they avoid duplicates in Databases 
and Tables. Only the "key", the Id is duplicated, not the contents 
(User names...).