CODEWITHSUNDEEP
phpmysqlprimary-keyauto-increment
Eduardo Ponce de Leon
Eduardo Ponce de Leon

Reputation: 9696

How to obtain with PHP the last ID inserted if my primary key does not autoincrement

I have a table in MySQL that for compatibility issues we assigned specific Primary Keys, therefore they can not be auto incremented.

Every time we insert a new topple (in PHP) we need to get back the latest ID inserted. It might sound stupid. but we do not generate the ID from PHP, it comes from an generic AJAX function, therefore we don't know the name of the variable containing the Primary Key, it always changes.

Is there a way, using PHP or MYSQL, to obtain the latest ID inserted for a primary key that does not auto-increment?

Unfortunately mysql_insert_id() and last_insert_id() do not work without auto_increment.

Thanks!

Upvotes: 0

Views: 1426

Answers (2)

Arman P.
Arman P.

Reputation: 4394

Following SQL statement will get last inserted id of PK. Do it after your insert statement...

SELECT LAST_INSERT_ID() FROM tblName;

True, I forgot that you mentioned that your PK is not auto incremented. Then see the answer with timestamp solution. It's the only way I know that you can do this. But it is preferable to have auto increment field. Maybe you need to review your database scheme.

Upvotes: 2

Zanrok
Zanrok

Reputation: 297

Head down to the comments section on this page. http://php.net/manual/en/function.mysql-insert-id.php And you should see some methods that might work for you.

I would try inserting the specific time_stamp and then calling that time_stamp insert again.

Dave does a good job of explaining it in this comment. Quoting him..

There is a lot of incorrect info here on "don't use AI" and "using max is equivalent". Then you have people improperly advising you to use psuedo-random numbers

If you're really worried about the AI field not returning because of the inherent race conditions, just do a select statement based on the vars you just input. If your vars are not unique, DON'T use these psuedo-random numbers. When you have enough iterations, the probability that the one of these randoms becomes a duplicate gets pretty high.

Instead, just use the unix timestamp. However, don't use UNIX_TIMESTAMP() in your query, because if you do, when you run your select statement right after, there's a possibility that you'll end up with a different timestamp.

Since date() continues to count up during the execution of the script, simply store date(U) into a variable or definition. Then insert and select based on that. Assuming you're not using mysql procedures:

<?php

define(UNIX_TIMESTAMP, date('U'));

$db->query("insert into table values('', 'a', 'b', 'c', 'd', '".UNIX_TIMESTAMP."'");
$res = $db->query("select id from table where a_col = 'a' and b_col = 'b' and c_col = 'c' and d_col = 'd' and temp_id = 'UNIX_TIMESTAMP'");

//...

?>

Upvotes: 3

Related Questions