CODEWITHSUNDEEP
phpmysql
Faizan Anwer Ali
Faizan Anwer Ali

Reputation: 671

How to use auto-increment Id in another column in same INSERT QUERY in MYSQL?

How to use auto-increment Id in another column in INSERT QUERY in MYSQL?

I want to add user with code. like if user_id after insert is 1 the code will be US1 similarly if user_id after insert is 954 the code will be US954

This is what I'm currently doing

"INSERT INTO `users` (`user_id`, `user`, `code`) VALUES (NULL, 'Alice', NULL);"

and then retrieve last insert id in PHP (Codeigniter)

$insert_id = $this->db->insert_id();

and then update code with auto increment id

"UPDATE `users` SET `code` = CONCAT('US', '" . $insert_id . "') WHERE `user_id` = " . $insert_id . ";";

Is there anyway you can do it in a single INSERT QUERY in MYSQL?

EDIT: This is not same as this Insert/ update at the same time in a MySql table?. Because this one asking on concurrent operation of two users simultaneously which is not my question

Upvotes: 0

Views: 3036

Answers (3)

Komal K.
Komal K.

Reputation: 470

Get Newly user_id with MAX(user_id)+1 and then concat it with your string

INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users

Upvotes: 0

Darshak Shah
Darshak Shah

Reputation: 159

Yes, it's possible. You can do it using Store Procedure(SP) in MySQL. You have to call SP one time for each registration instead of twice query(which you did).

It will save lot execution time.

    https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure    
    https://www.w3resource.com/mysql/mysql-procedure.php
    https://www.sitepoint.com/stored-procedures-mysql-php/
    http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

Please use above links for more information.

I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.

For your basic example. I've shared similar SP code with you.

BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID(); 
//update code
END

Upvotes: 3

Istiyak Amin
Istiyak Amin

Reputation: 281

when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented

for the create fresh create new table query

CREATE TABLE `users` (
    ID int NOT NULL AUTO_INCREMENT,
    user_id varchar(255) NOT NULL AUTO_INCREMENT,
    user varchar(255),
    code text,
    PRIMARY KEY (ID)
);

that is the inserting query

"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"

Upvotes: -1

Related Questions