MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A
- by Chez
Hi All,
Have been searching high and low for a simple solution to a mysql insert problem. The problem is as follows:
I am putting together an organisational database consisting of departments and desks. A department may or may not have n number of desks.
Both departments and desks have their own table linked by a foreign key in desks to the relevant record in departments (i.e. the pk). I have a temporary table which I use to place all new department data (n records long)...In this table n number of desk records for a department follow the department record directly below. In the TEMP table, if a column department_name has a value,it is a department, if it doesn't it will have a value for the column desk and therefore will be a desk which is related to the above department. As I said there maybe several desk records until you get to the next department record.
Ok, so what I want to do is the following:
Insert the departments into the departments table and its desks into the desks table , generating a foreign key in the desk record to the relevant departments id.
In pseudo-ish code:
for each record in TEMP table
if Department
INSERT the record into Departments
get the id of the newly created Department record and store it somewhere
else if Desk
INSERT the desk into the desks table with the relevant departments id as the foreignkey
note once again that all departments desks directly follow the department in the TEMP Table
Many Thanks
