Mysql get last id of specific table

Question

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 :)

Deceit

In your example simply switching the INSERT INTO blahblah2 and the INSERT INTO blahblah3 line would do the trick. – Clitoris

I can't do that in my coding, sry :/ – Deceit

Answer 1

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);

Adaurd

Answer 2

Is this what you are looking for?

SELECT id FROM blahblah ORDER BY id DESC LIMIT 1

Vada

Answer 3

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.

Toad

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)'"); – Deceit

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 '). – Toad

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 :) – Disaffirm

@Disaffirm 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. – Toad

@Toad 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 :) – Disaffirm

@Disaffirm If I got you right, then you're looking for SELECT * FROM blahblah WHERE ID=(SELECT MAX(id) FROM blahblah). – Toad

Ahhhhhh - I had been trying SELECT * FROM blahblah WHERE id=MAX(id) - I don't think that works. Thanks alot for your help :) – Disaffirm

@Disaffirm You're welcome! Vote me up, If my answer was helpful ;-) – Toad

@Toad (select MAX(id) FROM blahblah), solved my problem .. good answer – Barcroft

Answer 4

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.

Onym

Answer 5

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

Elasticize

How do i do that? I doubt it :/ – Deceit

i was trying to tell you exactly what Saharsh Shah has mentioned in his answer. The only difference is that he has presented his answer in a better way. – Elasticize

Answer 6

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');

Cold