Best way to insert into two mysql tables

Hi guys,

Im looking for a good solution.

Currently i have one message table and one association table.

When i insert a new message i insert first into the message table then i have to connect to message table again and grab the msg ID and then insert into association table.

							$timeNow = time();


								mysql_db_query($forum_Database, "
								INSERT INTO `user_privmsgs` (
									`msg_id` ,
									`root_level` ,
									`author_id` ,
									`icon_id` ,
									`author_ip` ,
									`message_time` ,
									`enable_bbcode` ,
									`enable_smilies` ,
									`enable_magic_url` ,
									`enable_sig` ,
									`message_subject` ,
									`message_text` ,
									`message_edit_reason` ,
									`message_edit_user` ,
									`message_attachment` ,
									`bbcode_bitfield` ,
									`bbcode_uid` ,
									`message_edit_time` ,
									`message_edit_count` ,
									`to_address` ,
									`bcc_address` 
								)
								VALUES (
									NULL, 
									'$messageID', 
									'$userID', 
									'0', 
									'$ip', 
									'$timeNow', 
									'1', 
									'1', 
									'1', 
									'1', 
									'$messageSub', 
									'$msgContent', 
									'', 
									'0', 
									'0', 
									'', 
									'', 
									'0', 
									'0', 
									'', 
									''
								);");


								echo "SELECT `msg_id` FROM `user_privmsgs` WHERE `message_time` = $timeNow AND `author_id` = $userID";
								$sendmessageData = mysql_db_query($forum_Database, "SELECT `msg_id` FROM `user_privmsgs` WHERE `message_time` = $timeNow AND `author_id` = $userID");
								$num_rows = mysql_num_rows($sendmessageData);
								if($num_rows == 0) {
									echo "<p>ERROR</p>";
								}

								if($row = mysql_fetch_array($sendmessageData))
								{
									$msgid = $row['msg_id'];
								}
						
								mysql_db_query($forum_Database, "
								INSERT INTO `user_privmsgs_to` (
									`msg_id` ,
									`user_id` ,
									`author_id` ,
									`pm_deleted` ,
									`pm_new` ,
									`pm_unread` ,
									`pm_replied` ,
									`pm_marked` ,
									`pm_forwarded` ,
									`folder_id` 
								)
								VALUES (
									'$msgid', 
									'$messageAuthID', 
									'$userID', 
									'0', 
									'1', 
									'1', 
									'0', 
									'0', 
									'0', 
									'0'
								);");

PHP:

But this obvious feels dumb? anyone have a better solution?


Best Regards

 

depends on MySQL version: use LAST_INSERT_ID

 

Im using mysql 5, Is it possible for another query to jump in betwen the queries and it then grabs the wrong id?

 

everytinh is possible on this planet
make one string of it
"SELECT allyourcommands; INSERT theothercommandswith_LAST_INSERT_ID)"