While working on the Not So Extreme Makeover: Community Edition site, I came up with an algorithm that simplifies anything else I’ve ever written to deal with this condition. I’ll set the scenario, explain the algorithm, share how I implemented it in PHP, and provide a modification if the scenario is a bit more complicated.
Scenario - You have two parent tables, and a child table with a many-to-one relationship with both parent tables, used to map entries in the two parent tables to each other. For this example, we’ll use these three tables…
create table volunteer (
Algorithm - The three-step algorithm is as follows…
- Create a comma-delimited string of IDs for the child table.
- Delete the IDs from the child table that are not in the list.
- Insert the IDs into the child table that are not there already.
Implementation - In PHP, if you have an array, it’s easy to come up with comma-delimited list. To get an array of values back in a post, define your fields with “” after the name…
<input type="checkbox" name="area" id="chkArea1" value="1" />
Here’s the PHP code, using PHP Data Objects (PDO) as the database interface, behind a helper class that creates the statement, appends the parameters, and executes it. (The “quoting” escapes the statement to avoid potential SQL injection attacks - putting it in its own class would make the implementation here much cleaner.)
Modification - Suppose that now you accepted comments along with each of the checkboxes, so a simple two-integer insert/delete is no longer sufficient. You would still only need to break step 3 into two steps.
- Get a list of IDs to update.
- For each ID in the posted list
- If the ID exists in the update list, update it.
- Otherwise, insert it.
The implementation would then be able to use this list to make the decision without hitting the database every time.
// Assume this returns an associative array of IDs.
I think you’ll agree that this is much better than spinning through a loop, doing a count on each ID to see if it exists, then either doing an update or an insert based on the count. And, while the implementation here is PHP, it could easily be implemented in any language that supports arrays and database access.