How to update phone numbers for an employee in a database using PHP

Question

I've got a form that allows the user to edit an employees information: name, email, technician level, etc. Well this is not hard in itself, I'm trying to find a good method for updating phone numbers for each employee. I have two tables (that are relevant): technicians and phones. My form allows the user to add, remove and edit any amount of phone numbers for the employee they are currently editing.

I'm not quite sure how to balance the updating of the phones table between PHP and JS (note this app is AJAX intensive).

I'm thinking I can either:

1. Use JS to track which phone numbers need to be updated, which need to be added and which need to be removed and then either send that data to a single PHP script or call three different PHP scripts.
2. Let PHP figure it out. Send a script the list of the phone numbers the technician should now have. I could then either: delete the technicians old phone numbers and insert the new set, or query the database for the old numbers and compare them one by one to the new list.

What would a common method of accomplishing this kind of task be (I'm sure this problem doesn't apply to only my situation). After typing it out, it seem to me the simplest way would be to just delete all the old rows for that technician and insert the new ones.

Answer 1

Deleting all the old rows and creating new ones might seem like a quick solution, but don't go there - you'll end up tying yourself in knots as soon as anything else tries to associate to the same phone numbers in a future version of the app.

Working out which numbers to add and which to delete is pretty simple to achieve, so I'd suggest doing the work on your server.

Answer 2

Idea #2 is the best one. Do it all in PHP. Comparing is expensive and slow. You have to query, perform a set difference and then generate some SQL.

I recommend deleting all existing phone numbers and adding all known good phone numbers.

DELETE FROM phones WHERE technician_id = 123; INSERT INTO phones (technician_id, phone_number) VALUES (123, '401-555-1212'), (123, '402-555-1212');