Add, Delete rows in database using checkboxes

[svgmx5]

Hey everyone i having some trouble updating records in my databse using checkbox from a form.

 

I can update the records, but i can't figure out how to remove records at the same time.

 

I've posted the code that i'm using to update below

foreach($_POST['tag'] as $k=>$l){
$tag = mysql_real_escape_string($_POST['tag'][$k]);

$check_tags = mysql_query("SELECT * FROM file_cats WHERE category='$tag'") or die(mysql_error());
$num_tags = mysql_num_rows($check_tags);

if($num_tags==0){
	$add_tag = mysql_query("INSERT into file_cats (fileID, category) VALUES('$fileID', '$tag')") or die(mysql_error());	
}
}

 

This code inserts new records based on what the user checked. If it exist it won't add a new row, if it doesn't exist then it will add a new row. but like i said how can remove a row if the box is no longer checked?

 

I've tried using

foreach(empty($_POST['tag'] as $k => $l){
       $tag = empty($_POST['tag']['$k'];
}

 

But that didn't work basically nothing was deleted.

 

Has anyone done something like this before? if so i would appreciate any help you could give

[DavidAM]

Yeah, HTTP does not post checkboxes that are not checked. I guess the easiest thing to do is to delete the entries that are not in your posted array:

 

$allTags = array();
foreach($_POST['tag'] as $k=>$l){
$tag = mysql_real_escape_string($_POST['tag'][$k]);
// Collect all the tags - escaped and quoted - in an array
// 	this just simplifies the implode() later
$allTags[] = "'$tag'";

$check_tags = mysql_query("SELECT * FROM file_cats WHERE category='$tag'") or die(mysql_error());
$num_tags = mysql_num_rows($check_tags);


if($num_tags==0){
	$add_tag = mysql_query("INSERT into file_cats (fileID, category) VALUES('$fileID', '$tag')") or die(mysql_error());

}
}
// Delete all tags EXCEPT the ones we just added
if (! empty($allTags)) {
$sql = "DELETE FROM file_cats WHERE fileID = $fileID 
AND category NOT IN (" . implode(','$allTags) . ")";
mysql_query($sql);
}

 

NOTE: Your SELECT query seems to be missing AND fileID = $fileID - maybe you were just typing fast for the post, or maybe your code is not checking what  you think it is checking.

[svgmx5]

I guess that explains why nothing was removed.

 

yea i needed that fileid='$fileID' i just forgot to type it in

 

Anyway i'll try it out, and i think i can take it from here...thanks again, i'll post if i have any problems or if it works

 

 

 

[svgmx5]

So now i'm getting the following error:

 

Parse error: syntax error, unexpected T_VARIABLE in on line 67

 

Line 67 is this:

$remove_tags = mysql_query("DELETE FROM file_cats WHERE fileID='$fileID' AND category NOT IN(". implode(',' $allTags) .")") or die(mysql_error());	

 

I've looked in or around line 67 to see if i forgot to close a tag or semicolon, but i'm pretty sure it has to do with the

 

AND CATEGORY NOT IN (".implode(',' $allTags).")

 

Because i removed it and the page displayed..

[svgmx5]

Okay so i got that error fixed...the coma was in the wrong place...

 

However when deleting its looking for a column named after the category rather than just looking in the category column

 

this is the error i get....

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tag1,tag2,tag3)' at line 1

[Pikachu2000]

When you implode() an array in an IN() like that, don't forget you have to account for quoting the values in it.

$query = "DELETE FROM file_cats WHERE fileID='$fileID' AND category NOT IN('" . implode("', '", $allTags) . "')";
$remove_tags = mysql_query($query) or die(mysql_error());

[svgmx5]

thanks, that fixed that issue!

 

looks like i got it fixed now thanks everyone!