CODEWITHSUNDEEP
mysql
Norman
Norman

Reputation: 6365

Inserting a value into one table based on insert from another table

I have this table for users that stores their usernames and other data, thats done like this (stripped down):

 CREATE TABLE `prod_users` (
        `p_user_id` INT(11) NOT NULL AUTO_INCREMENT,
        `p_user_name` VARCHAR(200) NOT NULL DEFAULT ''
            `p_comp_name` VARCHAR(300) NOT NULL DEFAULT '',
        PRIMARY KEY (`p_user_id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=MyISAM

Each time a user signs up, he'll provide a company name as well.

There's another table called prod_profiles, which stores profile details like phone nos. fax nos. etc.

CREATE TABLE `prod_profiles` (
        `pf_gen_id` INT(11) NOT NULL AUTO_INCREMENT,
            `pf_user_id` INT(11) NOT NULL DEFAULT '0',
        `pf_user_name` VARCHAR(200) NOT NULL DEFAULT ''
            `pf_comp_name` VARCHAR(300) NOT NULL DEFAULT '',
        PRIMARY KEY (`pf_gen_id`)
    )
    COLLATE='utf8_general_ci'
    ENGINE=MyISAM

When a new user signs up and his details are added to prod_users, is it possible to automatically add his new user_id, user_name and comp_name details to prod_profile using MySql itself? Since each user will have a new p_user_id and we wont know it, it'll be difficult using php. Can this be achieved inside MySql itself without any problems?

Upvotes: 1

Views: 1418

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270609

It isn't difficult using PHP, since you have the LAST_INSERT_ID() available for use, be it via mysql_insert_id() or mysqli::$insert_id, PDO::lastInsertId() or whatever your API provides. As long as you call the two INSERT statements in immediate succession on the same script (it is connection dependent), MySQL will supply the correct p_user_id.

However, you can use an AFTER INSERT trigger to force MySQL to create the new row automatically:

CREATE TRIGGER build_profile AFTER INSERT ON prod_users
FOR EACH ROW
BEGIN
  INSERT INTO prod_profiles 
    (pf_user_id, pf_user_name, pf_comp_name) 
    VALUES (NEW.p_user_id, NEW.p_user_name, NEW.p_comp_name)
END

Review the MySQL CREATE TRIGGER syntax reference for full details and options.

Upvotes: 1

Ofir Baruch
Ofir Baruch

Reputation: 10346

You can use the next mysql function: LAST_INSERT_ID(); which returns the last auto increased id.

Therefore , add a user and then add a prod_profile , while pf_user_id value will be the returned value of last_insert_id().

INSERT INTO `prod_users`(`p_user_name`,`p_comp_name`) VALUES('Dan' , 'Stackover')
INSERT INTO `prod_profiles`(`pf_user_id`,`pf_user_name`,`pf_comp_name`) VALUES(LAST_INSERT_ID(),'Dan','Stackover')

Please notice: I have to say , that storing the username and company_name twice for the same user in two different tables is a reall waste...

Consider re-thinking about your DB structre and logic.

Upvotes: 1

Related Questions