0

I need to use the following insert statement, and the primary key field is auto increment. I need to return the new primary key value after the insert. Preferably using one statement to do the insert and return primary key value.

$query = $database->connection->prepare("INSERT INTO accounts (created, priviledge, password, token, idle) VALUE ('$created', '1', :password, '$token_encrypt', '$idle')");    
$query->bindParam(":password", $password_hash);    
$query->execute();
Musa
  • 96,336
  • 17
  • 118
  • 137
Mr. Cockadookie
  • 13
  • 1
  • 3
  • 2
    `$database->connection->lastInsertId()` assuming you use PDO – zerkms Oct 16 '14 at 19:50
  • Works great, will this return the value from the existing $query only? In other words, what if another instance of the script is running, is there any chance it would return the incorrect value from the other instance? – Mr. Cockadookie Oct 16 '14 at 19:55
  • no. lastinsertid applies only to **YOUR** script, and **ONLY** that particular DB connection. other users running this or other code in parallel will not affect your ID, other than maybe using up other ids so THIS script's id sequence may not be sequential. – Marc B Oct 16 '14 at 19:56

4 Answers4

2

As soon as you're using PDO you need to use

$database->connection->lastInsertId()

And it guarantees you to return the value from the current session. So answering to your question from comments - there is no chance you get the wrong value from a concurrent session.

zerkms
  • 249,484
  • 69
  • 436
  • 539
0

Use mysql_insert_id(); to get the ID generated in the last query.

It retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

Ashish
  • 743
  • 4
  • 9
0

assume your primary field name is id then try this code

SELECT max(id) FROM tableName

or try this

LAST_INSERT_ID();
Farhad
  • 1,873
  • 17
  • 28
0
DELIMITER $$
DROP TRIGGER IF EXISTS `TR_AI_mytable`;
CREATE TRIGGER `TR_AI_mytable` AFTER INSERT 
    ON `mytable` FOR EACH ROW 
BEGIN
    SET @mylastPK = new.PKField;
END;
$$
Alejandro Laorden
  • 1
  • 4