SQL to INSERT-DELETE disjoint rows into MySQL?

Question

I have a food_table and a person_table. Then I have a third table fav_food_table that stores the relation between food and person using food_id and person_id.

When the person goes to account info and updates his favourite food, the input data is passed to the PHP (HTTP) script as an array of selected food_id. A person can have multiple fav_food, the relation is one-to-many.

The naïve way to update fav_food_table is to delete from fav_food_table all that belongs to person_id then re-insert all the rows again. Thus, using 2 statements.

Is there a single statement that can do the same thing?

PSUEDO CODE:

CREATE TABLE food_table (food_id, food_name);
CREATE TABLE person_table (person_id, person_name);
CREATE TABLE fav_food (person_id, food_id);

Answer 1

You have a m:n relationship between person_table and food_table. This means you have multiple records in your relationship table related to the same person. When a person updates their favourite foods, any combination of four independent cases can occur:

1. Food A was favourite before, but is not anymore (DELETE FROM fav_food_table)
2. Food B was favourite before and still is! (do nothing)
3. Food C was no favourite before, but now is a favourite (INSERT INTO fav_food_table)
4. Food D was no favourite before, and still is no favourite! (do nothing)

To correctly keep your database up to date, you have to handle all four cases. Cases 2 and 4 are covered easily. Just don't do anything :)

That means, you have to do at least two steps to keep your database up to date: 1 and 3.

Your goal seems to be to reduce the number of sql statements, that have to be executed.

Deleting all favourites for one person from the table can always be done with a single statement:

DELETE
FROM fav_food_table
WHERE person_id = ?;

To delete selected favourites for one person, only an AND food_id IN (?,?,?) has to be added to the WHERE clause.

Inserting into the table can also be done with a single statement:

INSERT INTO fav_food_table (person_id,food_id)
VALUES (?,?),
       (?,?),
       (?,?),
       .....;

Summary as of right now: No matter, whether we delete all old records for this person and then insert all new records, or whether we only delete selected records and insert new ones: We can do it with two statements!

In the second case (the "smart" case), however, we need to know not only the new state of the relation, but also the old state to compute the difference between the two. This will result in either one more SELECT statement or needs some smart "client" (PHP) logic.

Your two step process doesn't seem to be naive, but easy and effective to me. There is no way to reduce this process to less than two statements.

If you want to reduce the number of times you have to effectively send commands from PHP to the server, you can either look into mysqli_multi_query() or into creating a stored procedure which holds both your DELETE and INSERT statement. But bottom line, this will be the same thing as executing the two queries on its own. You can also look into MySQL Transactions to implement a safer process and be able to rollback your DELETE command should an error occur later on.

Answer 2

May I suggest something different. Instead of deleting, updating?

Deleting data in common is something that should be thought off. Deleting data cannot be retrieved. So check this out.

We are adjusting your code a bit.

CREATE TABLE food_table (food_id, food_name);
CREATE TABLE person_table (person_id, person_name);
CREATE TABLE fav_food (person_id, food_id, fav_food_active); 
/* see the last column i added. It should be a bit type and can only hold the values 0 and 1. */

Now you technically can update this everytime. No need for deleting the values. This statement is

/* deletion */
UPDATE Fav_Food
SET fav_food_active = 0
WHERE food_id = (your food_id)
AND person_id = (your person_id)

/* activating it again */
UPDATE Fav_Food
SET fav_food_active = 1
WHERE food_id = (your food_id)
AND person_id = (your person_id)

So now you switch between those 2 for activating and deleting it, without having the consequences of deleting hard data. Overal, you can just call it like this in your code

SELECT *
FROM fav_food
WHERE (here your where clause on which you wanna search for)
AND fav_food_active = 1

Remember when you enter something in the database, you should always add it as 1. You can do that in PHP myadmin as auto value, or hard code it in your INSERT statement.

I am not sure what your backend code is (okay taking that back, its PHP, i read your post) looking at your question you shooting everything through an array, but try to work in some checks that foreach fav_food entry, check first in the db if the query excists. If it does, update it, if not, insert it. and let that run in a loop.

So something like

foreach ($food_id as $value){
    // check here the overal statement if it excists in your db
    // give back count
    if ($count == 1){
        // update query to delete it.
    }else{
        // create  your insert query here
    }
}

Hope this helps. Happy coding!