Fat-Free-Framework: How to add/modify/delete records of related table using DB\SQL\Mapper

Question

Hi everyone I'm currently working on a form that includes rows from a related table and processing the form has presented a challenge: In F3, what is the best method to handle a form that may result in rows in a related table being added, modified or deleted?

DB Schema

practitioner
------------------------------
id         | int(10)
first_name | varchar(255)
last_name  | varchar(255)
abn        | char(11)
mobile     | varchar(20)
email      | varchar(255)

practice_site
------------------------------
id         | int(10)
name       | varchar(255)

provider_number
------------------------------
id               | int(10)
practice_site_id | int(10)
practitioner_id  | int(10)
provider_number  | varchar(20)

The Form

• Provider numbers are added dynamically, form names are amended with _n, e.g. provider_number_3
• The delete button removes the row altogether, meaning there may be provider_number_1 and provider_number_3 but no provider_number_2

Form Output

The provider numbers are transformed into something a bit more manageable than *_n.

Other fields, id, first_name, etc are easily updated thanks to copyfrom().

Array
(
    [id] => 1
    [first_name] => Jon
    [last_name] => Doctor
    [abn] => 12345678902
    [mobile] => 0491729472
    [email] => john.doctor@email.com
    [provider_numbers] => Array
        (
            [0] => Array
                (
                    [provider_number] => ASBDF24
                    [practice_site_id] => 2
                )

            [1] => Array
                (
                    [provider_number] => 1249FBK
                    [practice_site_id] => 2
                )

        )

)

The Problem

A few conditions about provider numbers can occur when the form is submitted:

• A new provider number is added
• A provider number is changed
• A practice site is changed
• A provider number is removed

How would be the best way to go about this? (Preferably using Fat-Free-Framework's DB\SQL\Mapper class).

Possible solution:

• Do a $db->exec() to get an array of existing provider_number table where provider_id = POST.id
• Perform an array_diff() to find any records in database that aren't in form then do a DELETE statement on those records. Truncate the original array.
• Traverse through array looking whether the provider_numbers match. If they do, but practice_site_id is different, update. If practice_site_id is the same, do nothing.
• How to accurately determine if a provider_number has changed for a particular practice_site_id?

Another possibility:

• Change the form to include provider_number.id, when a new row is added, grab the newest available ID from the database

Answer 1

After the suggestion from @xfra35 in my question, I went with the second possibility - Adding provider_number.id to the form itself and going from there. If you have any further suggestions or advice, please feel free to share!

The form was modified so that values were inserted into provider_numbers[provider_number][] and provider_numbers[practice_site_id][] instead of practice_site_1, practice_site_2, etc. This has made the code for dynamically inserting the rows much easier as *_n no longer needs to be tracked.

<div class="delete-array"> was placed inside the form and whenever the delete button was clicked, the following was inserted: <input type="hidden" name="provider_numbers[delete][]" value="' + id + '" />.

The new button fetches the next AUTO_INCREMENT value from the database and has some basic logic if more than one provider number is being added at a time. (Caveat: Will break if multiple users manipulating this function at the same time)

var new_id;
$('.btn-add-provider-number').click(function() {

    if (typeof(new_id) === "undefined") {
        $.getJSON({url:'{{ @BASE }}/provider_numbers/new_id.json', async:false}).done(function(data) {
            new_id = data;
        });
    } else {
        new_id++;
    }

    var newRow = ' .... ';
    $('.provider-numbers').append(newRow);
}

A form submit now looks something like:

Array
(
    [id] => 1
    [first_name] => Amy
    [last_name] => Doctor
    [abn] => 66627819264
    [mobile] => 0472888798
    [email] => amy.doctor@email.com
    [provider_numbers] => Array
        (
            [delete] => Array
                (
                    [0] => 6
                )

            [provider_number] => Array
                (
                    [0] => 64872HJF
                    [1] => 182947KD
                )

            [practice_site_id] => Array
                (
                    [0] => 1
                    [1] => 2
                )

            [id] => Array
                (
                    [0] => 1
                    [1] => 7
                )
        )
)

Then is handled with the following code:

$updated = [];
$provider_numbers = [];
$provider_numbers_delete = [];

foreach ($f3->get('POST') as $key => $val) {
    if ($key == 'provider_numbers') {
        foreach ($val as $pn_key => $pn_val) {
            if ($pn_key !== 'delete') {
                foreach ($pn_val as $pnk_key => $pnk_val) {
                    $provider_numbers[$pnk_key][$pn_key] = $pnk_val;
                }
            } else {
                $provider_numbers_delete = $pn_val;
            }
        }
    } else if (is_scalar($val) && $practitioner->exists($key)) {
        $practitioner->set($key, $val);
        $updated[] = $key;
    }
}

if (!empty($provider_numbers)) {
    foreach($provider_numbers as $pn) {
        $provider_number->load(['id = ?', $pn['id']]);
        if ($provider_number->dry()) {
            $provider_number->set('practitioner_id', $f3->get('POST.id'));
        }
        $provider_number->copyfrom($pn);
        $provider_number->save();   
        $updated['provider_numbers'][] = $pn;
    }
}

if (!empty($provider_numbers_delete)) {
    foreach ($provider_numbers_delete as $pnd) {
        $provider_number->load(['id = ?', $pnd]);
        if (!$provider_number->dry()) {
            $provider_number->delete(); 
        }       
    }
}