Auto generate id, stud_id for table

Question

I have a database table with id, stud_id, stud_info I know how to auto increment id but i also want stud_id to be auto incremented with something like STU000001 Is it possible to do using only php and then insert it in database? Stud_id should be equals id with STU and 6 digit including id.

Possible duplicate of [How to make MySQL table primary key auto increment with some prefix](http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) — showdev, Mar 27 '17 at 16:11

score 0 · Answer 1 · answered Mar 27 '17 at 05:46

0

You can do it using trigger

    DELIMITER $$
    CREATE TRIGGER trigger_name_insert
    BEFORE INSERT ON table_name
    FOR EACH ROW
    BEGIN
      INSERT INTO table_name VALUES (NULL);
      SET NEW.id = CONCAT('STU', LPAD(LAST_INSERT_ID(), 5, '0'));
    END$$
    DELIMITER ;

answered Mar 27 '17 at 05:46

Nishant Nair

1,999
1
13
18

have you modified the table name ? – Nishant Nair Mar 27 '17 at 06:04
I have a table called std_info i used your code like this – lazyme114 Mar 27 '17 at 06:11
DELIMITER $$ CREATE TRIGGER trg_std_insert BEFORE INSERT ON std_info FOR EACH ROW BEGIN INSERT INTO std_info VALUES (NULL); SET NEW.id = CONCAT('STU', LPAD(LAST_INSERT_ID(), 5, '0')); END$$ DELIMITER ; – lazyme114 Mar 27 '17 at 06:12

score 0 · Accepted Answer · answered Mar 27 '17 at 06:56

you have two choices:

1) first(simple one), generate two columns, one for saving numbers that must be an auto increment field like your primary id key and one column for your pre word that is STU here ( or use just one column and concat it with STU in your code)

2) (mysql solution) second way is using stored procedures so You can generate custom auto-increment values like STU000001 STU000002 STU000003 ... you can do it like below:

http://en.latindevelopers.com/ivancp/2012/custom-auto-increment-values/

Auto generate id, stud_id for table

2 Answers2