0

so I want to link two tables with a common column "messageID". so first I insert into table 1 to get the Auto incremented id, then take that ID with LAST_INSERT_ID function and give that as the id for table 2:

$db->("INSERT INTO table_1 VALUES('','$message')");
$db->("INSERT INTO table_2 VALUES(LAST_INSERT_ID(),'$message');

but here's my concern, there might be two users running this script simultaneously, so in the few milliseconds between the two queries exicuting the LAST_INSERT_ID could have changed, so now the two id's are different. Is there any way I can prevent this possibility. I know it is not possible to insert into two tables with one query, which was my first thoughts. Any ideas much appreciated. Thank you

user2014429
  • 2,497
  • 10
  • 35
  • 49
  • 3
    Note that you're **NOT** inserting the last insert id in your second query. you're inserting a **STRING** that happens to say "last_insert_id". You want `... VALUES (last_insert_id(), '$message')` instead. not the lack of quotes and the addition of `()`. – Marc B Oct 16 '13 at 19:40
  • quite right, I forgot the parameters – user2014429 Oct 16 '13 at 22:06

2 Answers2

6

The LAST_INSERT_ID is local to the connection session, so it will not conflict with a different user making an insert.

Yevgeny Simkin
  • 27,946
  • 39
  • 137
  • 236
1

You could try using scope_identity, which would be something like this:

$db->("DECLARE @LAST_ID int
INSERT INTO table_1 VALUES('','$message')"
SET @LAST_ID = SCOPE_IDENTITY()
);

$arg = ... $myARR['LAST_ID'] ... //however you want to get the LAST_ID from your query to PHP here

$db->("INSERT INTO table_2 VALUES(@arg,'$message');

or

$db->("DECLARE @LAST_ID int
INSERT INTO table_1 VALUES('','$message')"
SET @LAST_ID = SCOPE_IDENTITY()
INSERT INTO table_2 VALUES(@LAST_ID,'$message')
);

LAST_ID would be the value of the Auto incremented id

rrtx2000
  • 626
  • 4
  • 13