1

In my PHP/MySQL application I have the following query to insert data in a table:

INSERT INTO `my_table` VALUES ('','$value1','$value2').

Where the first value is an auto incremented primary key.

Now, what I want to do is that immediately after this insert operation, I want to enter some data in another table my_table2 where the primary key of the entry in my_table serves as foreign key.

Should I use a trigger or is there some way to get the primary key of last entry of a table. Please answer with required code.

I am using mysqli and would like to keep this particular processing on database side.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
kmdhrm
  • 505
  • 1
  • 6
  • 17
  • possible duplicate of [PHP: how to get last inserted ID of a table?](http://stackoverflow.com/questions/1685860/php-how-to-get-last-inserted-id-of-a-table) – mvp Oct 19 '13 at 07:51

3 Answers3

1

You can try:

 SELECT LAST_INSERT_ID();
Jhanvi
  • 5,069
  • 8
  • 32
  • 41
0

After the insert statement you can fire another query:

SELECT LAST_INSERT_ID();

and this will return one row with one column containing the id.

Docs: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

mysql> SELECT LAST_INSERT_ID();
        -> 195

This works per connection so there is no problem if another thread writes into the table. Your SELECT needs to be executed 'RIGHT AFTER'/'As the next query' after the insert query ran

An example:

$dbConnection = MyMagic::getMeTheDatabase("please");
$oSomeFunkyCode->createThatOneRowInTheDatabase($dbConnection);

$result = $dbConnection->query("SELECT LAST_INSERT_ID();");
// ... fetch that one value and you are good to go
Alexander Vasilenko
  • 706
  • 1
  • 11
  • 24
-1

If you want it specifically for a table you can use following query:

SELECT AUTO_INCREMENT -1 as CurrentId FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'YOURDATABASE' AND TABLE_NAME = 'YOURTABLE'
Mally
  • 19
  • 4