How Use Auto Increment value in two columns

Question

i have a users complain table in which two columns specifies id of each complain row

`complaint_id` MEDIUMINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`complaint_code` VARCHAR(20) NOT NULL ,

i want if user provide complain_code then it will be inserted normally and complaint_id have its AUTO_INCREMENTED value, but if user skip to fill complain_code field than it also filled by complain_id's AUTO_INCREMENTED value same as complaint_id.

I can do it with Insert then Update statement but i want to use only single insert statement.

i don't know how to do it, please help. i am using mysql, PDO, php

Answer 1

This isn't too hard if you make use of a MySQL trigger.

The trigger can be set to execute automatically as soon as an INSERT is done in your table, and it will check if the comaplain_code is blank. If it is blank, will use the next autoincrement value of complain_id for the comaplain_code as well:

Create a trigger by running this:

DELIMITER $$
CREATE TRIGGER update_complain_code
BEFORE INSERT ON `user_complaints` FOR EACH ROW
begin
    DECLARE next_id INT;
    SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='user_complaints');

    IF NEW.complaint_code is NULL OR NEW.complaint_code = ''
    THEN
        SET NEW.complaint_code=next_id;
    END IF;
END;
$$
DELIMITER ;