PHP & Mysql updating many to many relationship with checkbox form

Question

I have a many to many table structure and updating checkbox forms.

The goal is to relate the users table to the projects table with the users_project table. This way there can be many users per project and many projects per user.

The form would on each user edit page would look something like this

<form action="#" method="post">
    <div>   
        <input type="checkbox" name="project_id[]" id="1" value="1">
        <label for="1">project 1</label>
        <br>

        <input type="checkbox" name="project_id[]" id="2" value="2">
        <label for="2">project 2</label>
        <br>
        <input type="hidden" name="editing">
        <input type="submit" id="submit" value="submit">
    </div>
</form>

Here are examples of the three tables.

users table

+----+-----------+
| id ¦ username  ¦
+----+-----------+
| 1  ¦ user 1    ¦
| 2  ¦ user 2    ¦
+----+-----------+

projects table

+----+-----------+  
¦ id ¦ title     ¦
+----+-----------+  
| 1  ¦ project 1 ¦
| 2  ¦ project 2 ¦
+----+-----------+    

user_projects table

this table relates the two above tables based on their id

+----+-------------+---------+
| id ¦ project_id  ¦ user_id |
+----+-------------+---------+
| 1  ¦           1 ¦       2 |
| 2  ¦           2 ¦       1 |
+----+-------------+---------+

I have made a checkbox form to add and edit these values. On each user page it displays all of the projects in the projects table. Then queries the user_projects table and finds a list of matches to add checks to the checkboxes.

But how do I edit these values to the database? How will I know if a user has unchecked a previously checked box or checked an empty box and update to the database without looping a query for a match on the users table for project_id and user_id?

Here is a rough concept of what I would like the end result to achieve.

if ($_POST['editing']) { 
    $totalprojects = $_POST['editing']; 
    $query = " 
        SELECT *  
        FROM user_projects  
        WHERE user_id = user_id 
        AND project_id = project_id 
    "; 
    $result = $mysqli->query($query); 
    $count = $mysqli->affected_rows; 
    for($i=0; $i < $totalprojects; $i++) {  
        if ($count == 1) { 
            if ($box == checked){  
                //do nothing 
            } 
            else { 
                //delete from database 
            } 
        } 
        if ($count == 0) { 
            if ($box == checked){  
                //add to database 
            } 
            else { 
                //do nothing 
            } 
        } 
    } 
} 

This just doesn't seem like a good idea at all since I would have to query the database at least once for every project in the project table. There must be a better solution for what I imagine to be a common problem. I know I am just thinking about this the wrong way.

NOTE: I've thought about just serializing an array and sticking it in the user column, but this is not acceptable since I would not be able to relate project to user only user to project and defeat the purpose.

I would like this to be implemented without any javascript trickery.

Answer 1

Since your number of projects is small enough to display in one table, I'm totally in favour of rewriting the relevant part of the user_projects table on submit:

BEGIN TRANSACTION;
DELETE FROM user_projects WHERE user_id = $uid;
INSERT INTO user_projects (project_id, user_id)
    VALUES ($proj1, $uid), ($proj2, $uid), ...;
COMMIT TRANSACTION;

Note the use of the extended INSERT syntax to write the association in one statement.

You can also drop the user_projects.id if you don't need it, saving a third of the space in the table.