Welcome to WebmasterWorld Guest from 54.145.209.107

Forum Moderators: coopster & jatar k

PHP & MYSQL updating checkbox forms

many to many relationship between tables

   
10:37 pm on Sep 21, 2008 (gmt 0)

5+ Year Member



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]

3:07 pm on Sep 22, 2008 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



<input type="checkbox" name="name[]" value="1" />
<input type="checkbox" name="name[]" value="2" />

When you process, only checked boxes will be in the array. So, first you must check a box has been checked:

if (!empty($_POST['name'])) {
}

When you process, implode the array and do a single query:

mysql_query("SELECT * FROM user_projects WHERE user_id IN(".implode(",",$_POST['name']).")...

Hope that helps.

dc

3:07 pm on Dec 1, 2008 (gmt 0)

5+ Year Member



I'm having a somewhat related issue, although I think a lot more simple.

How can I update my DB with boxes that have been unchecked. I have it working so that the newly checked boxes get updated, but unchecking I can't seem to figure out.

Please help, this is urgent!

3:15 pm on Dec 1, 2008 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Using the above example:

foreach (range(1,2) AS $name) {
if (!isset($_POST['name'][$name])) {
// do something
}
}

dc

3:39 pm on Dec 1, 2008 (gmt 0)

5+ Year Member



Thank you very much for your quick reply! Unfortunately I'm not sure if this will work in my case, I should have given more info pertaining to my specific case. So here it is:

I have a DB table called photos, it has these columns: id, featured, path

Then I have a PHP script which loops through and creates an HTML table, each row containing the photo id, path, a checkbox which corresponds with weather or not it is featured, a link to delete the row. The featured value in the DB is either 0 or 1. If it's 1 the checkbox shows as checked.

The problem I foresee is that the IDs don't necessarily run in order from 1 - 10 since some may have been delated and new ones added.

So my checkboxes might look something more like:

<input type="checkbox" name="name[]" value="1" />
<input type="checkbox" name="name[]" value="6" />
<input type="checkbox" name="name[]" value="9" />
<input type="checkbox" name="name[]" value="58" />
<input type="checkbox" name="name[]" value="59" />
<input type="checkbox" name="name[]" value="70" />

I'm using the value as the ID of the row in the DB....does this make any sense?

3:49 pm on Dec 1, 2008 (gmt 0)

5+ Year Member



And this is what my current loop looks like which updates only the newly checked boxes:


foreach ($_POST['featured'] as $id) {

$query = sprintf("UPDATE photos SET featured='%u' WHERE id='%u'", 1, mysql_real_escape_string($id));
$result = mysql_query($query, $con);
if (!$result) {
echo 'could not update photos: ' . mysql_error($con);
exit;
}

}

And the table row:


echo "<tr>
<td>$row[id]</td>
<td><input type='checkbox' name='featured[]' value=$row[id]$feat /></td>
<td>$row[title]</td>
<td>$row[caption]</td>
<td><a href='$_SERVER[PHP_SELF]?action=delete&id=$row[id]'>delete</a> <a href='/gallery.php?photo=$row[id]'>view</a></td>
</tr>\n";
 

Featured Threads

Hot Threads This Week

Hot Threads This Month