15

I have to get last insert id from a specific inserted table?. Lets say i have this code:

INSERT INTO blahblah (test1, test 2) VALUES ('test1', 'test2');
INSERT INTO blahblah2 (test1, test 2) VALUES ('test1', 'test2');
INSERT INTO blahblah3 (test1, test 2, lastid) VALUES ('test1', 'test2', last id of blahblah);

How do i get the insert id of table blahblah in table blahblah3? LAST_INSERT_ID() only gives you the last insert id

Regards, Simon :)

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
simon
  • 2,235
  • 6
  • 33
  • 53

6 Answers6

15

You can use LAST_INSERT_ID() function. Try this: 

INSERT INTO blahblah (test1, test2) VALUES ('test1', 'test2');

SELECT LAST_INSERT_ID() INTO @blahblah;

INSERT INTO blahblah2 (test1, test2) VALUES ('test1', 'test2');

INSERT INTO blahblah3 (test1, test2, lastid) VALUES ('test1', 'test2', @blahblah);
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
14

Is this what you are looking for?

SELECT id FROM blahblah ORDER BY id DESC LIMIT 1
x4rf41
  • 5,184
  • 2
  • 22
  • 33
9

If you want to do it in a single statement use:

INSERT INTO blahblah3 (test1, test2, lastid)
VALUES ('test1', 'test2', (select MAX(id) FROM blahblah));

This way you don't need to save any variables beforehand which assures you'll get the latest ID at that exact moment.

flu
  • 14,307
  • 8
  • 74
  • 71
  • I tried your example in this way, but it does not work: $mysqli->query("INSERT INTO products SET product_title='test'"); $mysqli->query("INSERT INTO tags SET tag_values='test'"); $mysqli->query("INSERT INTO iamges (images_path, product_id) VALUES ('blah', SELECT MAX(product_id) FROM products)'"); – simon Jan 10 '13 at 10:37
  • You forgot the brackets around the select, that's important. Use `$mysqli->query("INSERT INTO iamges (images_path, product_id) VALUES ('blah', (SELECT MAX(product_id) FROM products))");`. Also at the end you wrote `[...] products)'");`, I think it should be `[...] products)");` (remove the `'`). – flu Jan 10 '13 at 10:44
  • 1
    Sorry to bring this back up, but can you tell me what should be outputted? I've got `MAX(ID)` and in PHPMyAdmin it brings up the required result (`2`) but I cannot get this in a script. Can you give some code to show how I could print the result (`2`)? Thanks alot :) – Mattios550 Aug 15 '13 at 14:00
  • @Mattios550 Could you please be more specific? Why do you want to print the result? This code is for insertion :) If it's a matter of the scripting language you should think about asking it in the sections for that particular language. – flu Aug 19 '13 at 11:50
  • @flu my aim is to simply get the largest entry in a column - in this case, 'id' - obviously, these are sequential. I know there are other ways, but I'd like to use `MAX(ID)` to get the highest ID then use `SELECT... WHERE id='$id'` when `$id` is `MAX(ID)` to get the latest entry in a table. Sorry if this is confusing & thanks for your reply :) – Mattios550 Aug 19 '13 at 16:20
  • @Mattios550 If I got you right, then you're looking for `SELECT * FROM blahblah WHERE ID=(SELECT MAX(id) FROM blahblah)`. – flu Aug 20 '13 at 08:41
  • Ahhhhhh - I had been trying `SELECT * FROM blahblah WHERE id=MAX(id)` - I don't think that works. Thanks alot for your help :) – Mattios550 Aug 20 '13 at 09:42
  • @Mattios550 You're welcome! Vote me up, If my answer was helpful ;-) – flu Aug 20 '13 at 09:50
  • @flu (select MAX(id) FROM blahblah), solved my problem .. good answer – AAEM Sep 10 '19 at 17:49
2

You can use mysql_insert_id(); function to get a quick answer.

But if you are using a heavy traffic site, chances of in accurate results exist.

Vishnu R
  • 1,859
  • 3
  • 26
  • 45
1

You can use LAST_INSERT_ID() function.

INSERT INTO blahblah (test1, test 2) VALUES ('test1', 'test2');
    //this query will return id. Save it in one variable

 select LAST_INSERT_ID()

In short, save the last insert id in one variable and then use it in blahblah3

Bhavik Shah
  • 2,300
  • 1
  • 17
  • 32
0

you can also find last id by query in codeigniter As

$this->db->order_by('column name',"desc");
   $this->db->limit(1);
 $id=  $this->db->get('table name');
Nazir Ali
  • 1