homepage Welcome to WebmasterWorld Guest from 54.197.147.90
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP & MYSQL updating checkbox forms
many to many relationship between tables
master w bates III




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

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]

 

dreamcatcher




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

<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

bernk




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

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!

dreamcatcher




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

Using the above example:

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

dc

bernk




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

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?

bernk




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

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";

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved