insert into two tables, foreign key and primary key

[xjermx]

I am trying to implement an email style messaging feature into a PHP application.

 

I have a 'users' table with a primary key that auto increments, and my plan is to do two mail tables,

mail1 has the fields mail1_id, message_text, from_user, timestamp (mail1_id is the primary key and autoincrements, from_user is a foreign key that will relate back to the users table)

mail2 has the fields mail2_id, to_user, link_to_mail1 (mail2_id is primary key and autoincrements, link_to_mail1 is intended to provide relationship between the table records)

 

I'm a newbie, so I'm assuming this is a descent setup.  I've done it this way so that if a user sends a message to multiple other users, it will minimize repetition in the table, especially of the message_text field.

 

My question is, how do I insert data into this arrangement from a PHP application?  I'm familiar with basic INSERT, but obviously I need to know what mail1_id ends up being, in order to populate the link_to_mail1 field.

 

I could probably use an INSERT, QUERY, INSERT scheme to do it, but I can't help but feel like there is a better way of doing this.

 

Any suggestions?

 

Thanks!

 

(edit:  I'm assuming that this should be here in MySQL instead of in PHP?)

 

[fenway]

Sound like you need LAST_INSERT_ID() -- there's a php function for this in mysqli, I believe.

[xjermx]

Thanks!  that took care of it for me!