Hi Webmaster world, I have run into a new problem with my exploration of php and mysql. This time around involves a many to many table structure and updating checkbox forms.

The goal is to related 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.

Here are examples of the three tables.
users table
________________________
id_¦_username__¦ whole bunch of other columns
1 _¦ user 1_____¦
2 _¦ user 2_____¦
projects table
________________________
id_¦__title___¦ whole bunch of other columns
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 querries 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 querry for a match on the users table for project_id and user_id?

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
____________}
____________elseif() {
________________//delete from database
____________}
________}
________if ($count == 0) {
____________if ($box == checked){ 
________________//add to database
____________}
____________elseif() {
________________//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.

Thanks for any help guys!

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.

[edited by: master_w_bates_III at 10:49 pm (utc) on Sep. 21, 2008]