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.
[pre]
users table
________________________
[b]id ¦ username ¦ whole bunch of other columns[/b]
1 ¦ user 1 ¦
2 ¦ user 2 ¦
projects table
________________________
[b]id | title ¦ whole bunch of other columns [/b]
1 ¦ project 1¦
2 ¦ project 2¦
user_projects table
this table relates the two above tables based on their id
__________________________
[b]id ¦ project_id ¦ user_id [/b]
1 ¦ 1 ¦ 2
2 ¦ 2 ¦ 1
[/pre]
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?
[pre]
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
}
}
}
}
[/pre]
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.
