Inserting same info into two tables...

[elmas156]

I've got multiple tables I'm using for an internal messaging system that I'm creating.  I'm trying to keep the same message id ('messid') for messages regardless of what table they are saved in (inbox, saved, deleted...).  To do this, I've created a table named "allmessages" where every message sent is inserted and the 'messid' is generated using auto increment.  This allows me to keep all the information on all sent messages in one place rather than having to pull certain information from different tables.  From there, the message will keep the 'messid' when inserted into "allmessages".  Here's the problem I'm having: at the same time that I insert the information into the "allmessages" table, I need to insert some of the information into another table as well, including the 'messid' generated from the AI in "allmessages".  BUT as of now, I have no way to pull just that specific message because 'messid' is the only unique field that I have to use when querying the database.  Basically, if I use any other field (Like: "SELECT `messid`,`staffid`,`message`FROM `allmessages` WHERE `anything other than messid` = '$whatever'")  I would get multiple results because 'messid' is the only unique field AND it's part of the information I need to pull from the database.

 

I hope I'm making sense here... basically, I need to figure out a way to either use the 'messid' generated when inserted into "allmessages" and insert the same 'messid' into another table at the same time... OR use the 'staffid' field and pull all information from the last entry into "allmessages" and get the 'messid' to insert into a different table.

 

I would greatly appreciate any help.  I'm stumped on this one.

[mikosiko]

without see your tables definition that could lead me to a different suggestion I will say... implement a database TRIGGER over the table "allmessages" ... it could take care of the insert in the other table automatically.

 

a quick google search should lead you to several examples regarding how to implement a Trigger over a table.

[elmas156]

thanks... I'll check out triggers... if I have any trouble with that, I'll try some other ideas that I thought of since posting this thread.  Thanks again for your help!