Get the Unique ID for the Last Inserted Row

insert, mysql, stored-procedures

Solution

Your `mysqli_insert_id` is the problem, you're writing a MySQL stored procedure, not PHP. You want to use the `last_insert_id()` function:

`LAST_INSERT_ID()` (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an `AUTO_INCREMENT` column as a result of the most recently executed `INSERT` statement.

You also need to fix your assignment syntax. Something more like this:

DELIMITER //
CREATE PROCEDURE addNewUsers(IN userName varchar(128),IN password varchar(128), IN addedBy INT)
BEGIN
  DECLARE id int default 0;

  insert into  `system_users`( `username`,`password`) values (userName ,md5(password));
  set id = last_insert_id();
  if id <> 0 then
    -- ...

Problem

I want to get the Unique ID for the Last Inserted Row inside stored procedure, I make like this ``` DELIMITER // CREATE PROCEDURE addNewUsers(IN userName varchar(128),IN password varchar(128), IN addedBy INT) BEGIN DECLARE id int default 0; id = mysqli_insert_id (insert into `system_users`( `username`,`password`) values (userName ,md5(password)) ); IF id <> 0 THEN insert into `user_profile`( `full_name`,`Date_time_ added`,`added_by`) values (userName ,CURRENT_TIMESTAMP(),addedBy ) where `user_id`=id ; END IF END // DELIMITER ; ``` This error occur ``` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= mysqli_insert_id (insert into `system_users`( `username`,`password`) values (' at line 7 ``` I doubt it's from `mysqli_insert_id `, what should I do ?

Original source