syntax and duplicates

[turpentyne]

I'm figuring out a code that runs through several checkboxes, then checks the mysql table to enter any checked boxes if they don't already exist, and to delete any entries that might exist matching the unchecked boxes.

 

This is as far as I've gotten. I had it working, but it chokes on the table key. I want to skip over existing entries...

 

if (isset($_POST['submit'])){

require ('db.php');

$id1 = $_POST['plant_id'];
$imploder=implode(',', $_POST['option2']); 
$exploder = explode(',', $imploder);

foreach($exploder as $value) {
$add = "INSERT INTO table(plant_id, edible_id) VALUES ($id1, $value) WHERE plant_id = $id AND edible_id != $value";
$delete = "DELETE FROM table WHERE plant_id = $id1 and edible_id NOT IN $exploder";
};

$result2 = mysql_query($add) or die(mysql_error());
if (mysql_affected_rows() > 0) {
   echo '<div style="position:relative; left:16px; top:216px;"><font color="red">updated!</font></div>';
  }
}
?>

[trq]

A few things wrong here:

 

You execute your query outside of the loop. This means it will only ever execute the last query.

 

Where exactly are $id and $value defined?

 

It's not real clear what your actually trying to do.

[Pikachu2000]

One other thing, INSERT queries don't use WHERE clauses.

[turpentyne]

oops! the execute mistake was just from copying/pasting into this forum.. The where clause is what I was trying to use to see if it would work...

 

Everything works, but I don't know how to overlook duplicates. I put a key on the table, and that's what's halting the query.

 

It's a page that has several checkboxes to loop through and a hidden field that generates $id. I'm not sure if I understand the question on $value... I guess I thought that was being created by the " foreach($exploder as $value) " part.

 

here's what I have now. it works, but it's giving me the notice that there is a " Duplicate entry '24-1' for key 'plant_id' ". I'm not sure where to go from here.

if (isset($_POST['submit'])){


require ('db');

$id1 = $_POST['plant_id'];

$imploder=implode(',', $_POST['option2']); 
//echo $imploder."<br>";
$exploder = explode(',', $imploder);

//print_r($exploder);

//$add_plants = array_diff($cats,$mysql_cats);
//$delete_plants = array_diff($mysql_cats,$cats); 

foreach($exploder as $value) {

$add = "INSERT INTO table(plant_id, edible_id) VALUES ($id1, $value)";

$delete = "DELETE FROM table WHERE plant_id = $id1 and edible_id NOT IN $exploder";

$result2 = mysql_query($add) or die(mysql_error());
if (mysql_affected_rows() > 0) {
   echo '<div style="position:relative; left:16px; top:216px;"><font color="red">your plant has been updated</font></div>';
  }
};

[trq]

You will need to execute a SELECT query first that checks to see that the record doesn't already exist. Alternatively, take a look at http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html