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

Question

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?

It seems that the entire prod_profiles table is unnecessary. If each user has only one profile, then most of the fields of the second table should be moved into the first table. And if each user can have several profiles, there is no need to store the pf_user_name and pf_comp_name fields; simply keep the pf_user_id field as a foreign key. — No'am Newman
– No'am Newman, Commented Aug 25, 2012 at 15:48
Thats the way it currently is. But then someone decided that it was 'good idea' to keep profile data away from the login data.:( — Norman
– Norman, Commented Aug 25, 2012 at 15:52

Michael Berkowski · Accepted Answer · 2012-08-25 15:54:41Z

1

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.

edited Aug 25, 2012 at 15:54

answered Aug 25, 2012 at 15:46

Michael Berkowski

271k47 gold badges451 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Norman Over a year ago

This is how we've decided to do it.

Ofir Baruch · Accepted Answer · 2012-08-25 15:51:36Z

1

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.

edited Aug 25, 2012 at 15:51

answered Aug 25, 2012 at 15:46

Ofir Baruch

10.3k2 gold badges29 silver badges39 bronze badges

1 Comment

Norman Over a year ago

Right now its all in one table. But this is how they want it :(

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related