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

Well, assuming every user is eligible to work on every project, on your form-processing page you want to do something like this:

$query = 'SELECT p.id AS project_id, u_p.id AS user_project_id '.
            'FROM projects AS p '.
                'LEFT JOIN user_projects AS u_p ON (u_p.project_id = p.id AND u_p.user_id = ?)';

By left joining, this will give you a list of all the projects in the system, and the second column will only have a value if the user that you're editing is already on that project. For the example data you gave, this is the result that you would get, querying on user_id 1:

+------------+-----------------+
| project_id | user_project_id |
+------------+-----------------+
|          1 |                 |
|          2 |               2 |
+------------+-----------------+

Then loop over the results from this query, and for each result, check if a value in $_POST was sent for the corresponding project_id. If a value was sent (the checkbox was checked) and the row's user_project_id is empty, insert a row to user_projects to add this link. If a value was not sent (checkbox was unchecked) and the row's user_project_id is set, delete that row from user_projects using user_project_id as the key. Otherwise do nothing, they didn't change from the way it was before.

Hope that's clear, let me know if you want me to go into more detail.

Answer 2

You can group the actions and reduce the script to 3 queries.

Retreive the current selected option using the LEFT JOIN from Chad Birch anwser.

Before the loop:

$delete_ids = array();
$add_ids = array();

Inside the loop:

// delete from database
$delete_ids[] = $project_id;

and

// add to database
$add_values[] = '('.$user_id.', '.$project_id.')';

And after the for loop:

if (count($delete_ids) > 0) {
  mysql_query('DELETE FROM user_projects WHERE user_id = '.$user_id.' AND project_id IN ('.implode(', ', $delete_ids).')'); 
}

if (count($add_ids) > 0) {
  mysql_query('INSERT INTO user_projects (user_id, project_id) VALUES '.implode($add_values)); 
}

Answer 3

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.