Using an insert id from first of three queries in php

Question

I want to store the data from several curl calls to an initial table, but last insert id is inserting wrong info

Query 1 inserts data into table

table1

   id name      email      valuereturn
   1   val     val@email.com       0

I then post data 3 times to my system and log it

table2

    id  name    system  valuereturn
    1   val      5       0
    2   val      0       0
    3   val      0       0

the max value returned from my system i want to update table 1

update table1 
set valuereturn = '5' 
where id = LAST_INSERT_ID()

does not work because last insert id is 3 from table2, how can I use something like last_insert_id(Table1)?

i want to update my

Well, you can't. You have to retrieve and remember it in a PHP variable. — AndreKR, Jan 04 '13 at 05:44
@AndreKR can you reply with that as the answer because that is what i did. idk why i didnt think of it, its one class so i just called a mysql_query("SELECT LAST_INSERT_ID()") after my insert and saved it to global variable!! ty — Val, Jan 04 '13 at 06:20
I did, but apparently I was only half right, it doesn't have to be a *PHP* variable. — AndreKR, Jan 04 '13 at 06:23

score 1 · Answer 1 · answered Jan 04 '13 at 05:46

1

Store that LAST_INSERT_ID() of table1 in a variable and than use that variable in update query.

INSERT INTO table1(name) values ('Saharsh');
SELECT LAST_INSERT_ID() INTO @table1Id;

INSERT INTO table2(name, table1id) values ('Saharsh', @table1Id);
UPDATE table1 SET valuereturn = '5' WHERE id = @table1Id;

answered Jan 04 '13 at 05:46

Saharsh Shah

28,687
8
48
83

will this work on one mysql connection? or will it merge if i have parrallell inserts to both tables? – Val Jan 04 '13 at 05:49
It will work for one mysql session. You can also create procedure to do the same. – Saharsh Shah Jan 04 '13 at 05:55

score 1 · Accepted Answer · answered Jan 04 '13 at 06:23

1

Well, you can't. You have to retrieve and remember it in a PHP variable.

Or go for Saharsh's solution and remember it in a MySQL variable.

answered Jan 04 '13 at 06:23

AndreKR

32,613
18
106
168

score 1 · Answer 3 · answered Jan 04 '13 at 06:27

In PHP I assume you use the following code:

<?php
  connect_db();
  insert_first_query_to_table1();
  insert_second_query_to_table2();
  update_query_setting(last_insert_id());
?>

If that's the case, I suggest you to use a temp variable to store the `last_insert_id`.

<?php
  connect_db();
  insert_first_query_to_table1();
  $setVal = last_insert_id();
  insert_second_query_to_table2();
  update_query_setting($setVal);
?>

Hope this helps.

PS: This is a pseudo code!

this is what i did, but it was more oop than that – Val Jan 04 '13 at 08:52 — Val, Jan 04 '13 at 08:52

Using an insert id from first of three queries in php

3 Answers3

In PHP I assume you use the following code:

If that's the case, I suggest you to use a temp variable to store the last_insert_id.

If that's the case, I suggest you to use a temp variable to store the `last_insert_id`.