Hi folks!
I need help how to code or links to tutorials and examples.
What i want to do:
I have a temporary table with the following columns:
company_id
int(11) NOT NULL AUTO_INCREMENT,
company_name
varchar(30) NOT NULL,
company_address
varchar(50) NOT NULL,
company_zipcode
int(6) NOT NULL,
company_city
varchar(30) NOT NULL,
company_telephone
varchar(20) NOT NULL,
company_addressid
int(9) NOT NULL,
person_firstname
varchar(20) NOT NULL,
person_lastname
varchar(20) NOT NULL,
person_email
varchar(30) NOT NULL,
person_emailstatus
varchar(12) NOT NULL,
person_position
varchar(50) NOT NULL,
answer_1
varchar(50) NOT NULL,
answer_2
varchar(50) NOT NULL,
answer_3
varchar(50) NOT NULL
Then i have 3 tables:
company
company_id
int(11) NOT NULL AUTO_INCREMENT,
company_name
varchar(30) NOT NULL,
company_address
varchar(50) NOT NULL,
company_zipcode
int(6) NOT NULL,
company_city
varchar(30) NOT NULL,
company_telephone
varchar(20) NOT NULL,
company_addressid
int(9) NOT NULL
person
person_id
int(11) NOT NULL AUTO_INCREMENT,
person_firstname
varchar(20) NOT NULL,
person_lastname
varchar(20) NOT NULL,
person_email
varchar(30) NOT NULL,
person_emailstatus
varchar(12) NOT NULL,
person_position
varchar(50) NOT NULL
answer
answer_id
int(11) NOT NULL AUTO_INCREMENT,
answer_1
varchar(50) NOT NULL,
answer_2
varchar(50) NOT NULL,
answer_3
varchar(50) NOT NULL,
The problem:
I want to insert from the temporary table to this 3 tables were i need it to be relational so in the table company one company only can be one time ( the company_addressid is a unique number for that company ) so when i insert to that table it should match this company_addressid anf if exists it only should update the data
when insert to person it should be the same as company but in this case the person_email is the unique data to match from so if exists only update the record
when insert to answer it should be the same as person but in this case if the person has already one answer attached to him it should only update the record
Solution:
I think i have to have the following extra columns:
In person:
company_id
In answer:
person_id
Hope someone can help me in the right direction how to select from the temporary table and split it to this 3 tables and still avoid duplicates in the company, person, answer
If you have some example please post it