CODEWITHSUNDEEP
phpmysql
Parvez Aalam
Parvez Aalam

Reputation: 17

how to enter values in multiple tables using same id

i have 3 tables
emp_ref_table with fields
1.emp_id(pk,ai)
2.emp_name
3.gender
4.emp_address

emp_login_table
1.id(ai,pk)
2.emp_id(fk)
3.Password(default : password)

emp_value_table
1.emp_value_id(pk)
2.emp_id(fk)
3.emp_photo
4.emp_resume

I have to insert values in all 3 tables at single time. Suppose first, i insert value in emp_ref_table the emp_id will be generated automatically
after generation of emp_id i have to store that last emp_id in login_table also
after login table same emp_id also have to be inserted in emp_value_table

i have tried this and its working fine,now get stuck on emp_value_table both queries working absolutely fine now help me in storing the emp_id in emp_value_table 

insert into emp_ref_table(emp_name,gender,emp_address) values ('xyz','d','abc');
insert into `login_table`(emp_id) values(LAST_INSERT_ID());

PS: i'm using stored procedures

Upvotes: 0

Views: 107

Answers (1)

Shadow
Shadow

Reputation: 34232

In your stored procedure declare a variable that will hold the new employee id. After inserting a record into employee reference table, assign the new id to the varible. Use this variable to assign value to employee id fields in the subsequent 2 inserts.

...
declare empid int;
insert into emp_ref_table(emp_name,gender,emp_address) values ('xyz','d','abc');
set empid=last_insert_id();
insert into `login_table`(emp_id) values(empid);
...

Upvotes: 1

Related Questions