Update two SQL tables from one form (PHP)

I have two tables i would like to update from the results of 1 form that is posted.

Table: Rescue Patients

Patient ID	Name	Status
1	Bodger	Released
2	Badger	Captive

Table: Admissions

Admission ID	Patient ID	Disposition
1	2	Died
2	1	Released

The form contains a look-up field with 6 options in a select, only a couple apply to "rescue patients" and the remainder are extras for the "admissions" table. these extras can be simplified to one word for the "patients" table. for example "died – euthanised" would just need to update the patients table as "deceased".

My first port of call was to attempt to get the "patient status" set correctly from what was posted:

// figure out the patient's status from the posted disposition
  if ($disp_disposition == 'Released') {
    $pat_status="Released";
  } elseif ($disp_disposition == 'Transferred to another rescue') {
   $pat_status="Transferred";
  } elseif ($disp_disposition == 'Died - Euthanised') {
    $pat_status="Deceased";
  } elseif ($disp_disposition == 'Died - within 48 hours') {
   $pat_status="Deceased";
 } elseif ($disp_disposition == 'Died - after 48 hours') {
   $pat_status="Deceased";
        } elseif ($disp_disposition == 'Died - on admission') {
    $pat_status="Deceased";
  }

And with this try and update to both tables. I initially approached this by "stacking" the statements i was already using:

try {
                                             
$statement = $conn->prepare('INSERT INTO rescue_admissions
            (admission_id, 
            disposition)
            
            VALUES (:admission_id, 
            :disposition) 
            
        ON DUPLICATE KEY UPDATE
        disposition = :disposition
        ');

        $statement->execute([
            'admission_id' => $disp_admission_id,
            'disposition' => $disp_disposition

       ]);

try {

$statement = $conn->prepare('INSERT INTO rescue_patients
            (patient_id, 
            status)
            
            VALUES (:patient_id, 
            :status) 
            
        ON DUPLICATE KEY UPDATE
        status = :status
            ');

           $statement->execute([
            'patient_id' => $pat_patient_id,
            'status' => $pat_status

        ]);

When this didn’t work (the modal wouldnt load) I attempted to join them and do a update query

$statement = $conn->prepare("UPDATE rescue_admissions
 JOIN rescue_patients ON rescue_admissions.patient_id = rescue_patients.patient.id
    SET rescue_admissions.disposition = {$disp_disposition},
        rescue_patients.status = {$pat_status},
 WHERE rescue_admissions.admission_id = {$disp_admission_id} AND rescue_patients.patient_id = {$pat_patient_id}");

And this also failed to load the modal. I’ve been playing with it for the most part of the day and still can’t figure out which would be the right way to go with this. Any guidance is really helpful, thank you.

Attempted to stack my statements and use a SQL join and update query. Was expecting the modal to load and be able to complete the form and it update the tables in the right places with the correct data.

You need to sign in to view this answers

Related Post