Inserting into tables

[ainoy31]

Hello-

 

I have a form that gets filled out and the data is stored in a DB.  I have a question regarding the insert part.  Here is the structure of some of my tables:

 

table: agent

agent_id int(16) NOT NULL auto_increment,

lname varchar(32) NOT NULL,

fname varchar(32) NOT NULL,

middle_initial varchar(32) NULL,

email varchar(32) NOT NULL,

phone varchar(12) NOT NULL,

alt_phone varchar(12) NULL,

PRIMARY KEY (agent_id)

 

table: username_password

user_id int(16) NOT NULL auto_increment,

reg_id int(16) NOT NULL,

agent_id int(16) NOT NULL,

username varchar(32) NOT NULL,

password varchar(32) NOT NULL,

level varchar(32) NULL,

 

PRIMARY KEY (user_id),

FOREIGN KEY (reg_id) REFERENCES register_user (reg_id),

FOREIGN KEY (agent_id) REFERENCES agent (agent_id)

 

table: address

address_id int(16) auto_increment,

agent_id int(16) NOT NULL,

reg_id int(16) NOT NULL,

listing_id int(16) NOT NULL,

street varchar(50) NOT NULL,

city varchar(32) NOT NULL,

state varchar(32) NOT NULL,

zip varchar(16) NOT NULL,

 

PRIMARY KEY (address_id),

FOREIGN KEY (agent_id) REFERENCES agent (agent_id),

FOREIGN KEY (reg_id) REFERENCES register_user (reg_id),

FOREIGN KEY (listing_id) REFERENCES listing (listing_id)

 

I am stuck on the insert logic.  Say I have a form that collects info on a new agent.  I want to make sure that the info gets inserted to the agent table and the address table and finally the username_password table with the agent_id points to the same person on all three tables.  Hope this is clear enough.  Much appreciation.

 

AM

[Barand]

After inserting "agent" record call mysql_insert_id() to get the new id

 

Use that value for "agent_id" when you insert the other records

[ainoy31]

thanks man.