Updating Multiple Tables

[eluser]Konvurt[/eluser]
All,

I am a newbie to CI and PHP. I am currently building an application @ work that manages student applicants, students, faculty, and staff.

So far, I have build a RUD system. I understand how to read/update/delete records using joins. Additionally I can successfully create new records in for user types that relate to one table.

However, when it comes to updating data in multiple tables I am quite confused. I have read other forum posts that relate to my dilema but I am still not sure how to apply those suggestions to
my project.

Here is more clear description of my current system. I have listed the main tables and few of their fields:

student_applicants (applicant_id, fname, lname, etc.)
master_people (user_id, role, status, etc)
dept_people (dp_userid, fname, lname, etc)

Student Applicants have their own table where we collect basic info and assign them an auto-incremented id. If a student applicant is accepted to the program the following things need to happen:

1. The Applicant record is updated to so that the status is flagged as "Enrolled".

2. A record for the applicant turned student needs to be created in master_people, their status, role and userid will be assigned

3. A record for the applicant turned student needs to be created in dept_people, the dp_userid should be set to the user_id created in master_people and related information such as fname, lname needs to be pulled from student applicant record and inserted in this new dept_people record.

I realize that the master_people and the dept_people tables could be combined to simplify this however, this database design was requested by stakeholders.

Some thoughts on doing this would be greatly appreciated.

Thanks.

[eluser]Aleazus[/eluser]

I realize that the master_people and the dept_people tables could be combined to simplify this however, this database design was requested by stakeholders.
Some thoughts on doing this would be greatly appreciated.
Thanks.

I'm unsure why you would need to combine these two tables. If you really did want to, you can just create more field options within the table to accomodate additional field entries needed. Then possibly add a status as to whether it is a master_people or dept_people.

[eluser]Konvurt[/eluser]
Aleazus,

Thanks for the reply. I think I may have confused you in the post.

Basically I have three tables which share information. Grad_Applicant, Master_People, and Dept_People.

I am trying to create a function that will be called once to create a new dept_people entry. However, the function needs to do three distinct things.

1. Create an entry in the Master_people table (mp_id, mp_status, mp_role)
2. Create an dept_people entry which uses the mp_id previously created

I hope that is a little clearer.

Thanks

[eluser]Joseph Wensley[/eluser]
I don't think you could do this all in one query but something like this might work.

Code:

$this->db->query("UPDATE student_applicants SET `status` = 'enrolled' WHERE `applicant_id` = '$applicant_id'");
$this->db->query("INSERT INTO master_people SET `status` = 'status', `role` = 'role'");
$user_id = $this->db->insert_id()
$this->db->query("INSERT INTO dept_people (`dp_userid`,`fname`,`lname`) SELECT '$user_id',`fname`,`lname` FROM student_applicants WHERE `applicant_id` = '$applicant_id'");

If the user_id in master_people isn't an autoincrement then obviously you would set it to something and use that instead of the insert_id for the 2nd insert.