PHP - Mutli Delete - Not Working

Hey Guys,

Here is my issue. I have a MySQL database with a table of products. I use this table to generate a mutliselect form element that is used int a larger form. The larger form contains other information relevant to an issue that we are having with that product (such as an outage). Anyway, the form works fine and the form validation works fine and I write it to the database fine. The issue I'm having is how to save the mutliselect options most efficiently.

This is my current process:

1. User creates a new entry from the form and clicks save
2. The form is validated
3. All entries are written to the database in a table called "incidents" (content inclues, timestamps, description of the problem, the impact, who to contact, etc)
4. Because the products list can vary from 1 product to all (~30) products I broke it out into a separate table called "impacted_products". I then loop through each of the selected products and write one row for each product using the id from the row in the "incidents" table to define the tables relationship.

Example of DB results:

"incidents"

+-----------------+------+----------+---------------------+---------+---------+
| notification_id | what | impact   | time_start          | summary | contact |
+-----------------+------+----------+---------------------+---------+---------+
| 235235          | Test | None.... | 2011-02-08 13:41:00 | Test    |       3 |
+-----------------+------+----------+---------------------+---------+---------+

"impacted_products"

+-----+-----------------+------------+---------------------+---------+
| id  | notification_id | product_id | timestamp           | deleted |
+-----+-----------------+------------+---------------------+---------+
| 202 | 235235          |          7 | 2011-02-10 14:30:42 |       0 |
| 203 | 235235          |         37 | 2011-02-10 14:30:42 |       0 |
| 204 | 235235          |         23 | 2011-02-10 14:30:42 |       0 |
+-----+-----------------+------------+---------------------+---------+

5. Now the users wants to go back and make some updates.
6. They click the edit button from the menu on screen and the form is brought back up and all options/fields are filled out from that which is stored in the database.
7. They make their edits and save again.
8. Now this time it simple updates the "incidents" table but for the products table I do a:

"UPDATE impacted_products SET deleted=1 WHERE notification_id='$ID'"

and "delete" all the old impacted_products for this particular incident and then loop through all the products that the user still had selected and write them to the database again.
9. And repeat

So as you can see this could end up with a lot of "useless" entries in the database since my scripts only pay attention to those "impacted_products" whose deleted value is set to 0.

Example:

+-----+-----------------+------------+---------------------+---------+
| id  | notification_id | product_id | timestamp           | deleted |
+-----+-----------------+------------+---------------------+---------+
| 176 | 235235          |         37 | 2011-02-08 15:26:25 |       1 |
| 177 | 235235          |         43 | 2011-02-08 15:26:25 |       1 |
| 178 | 235235          |         37 | 2011-02-08 15:37:58 |       1 |
| 179 | 235235          |         43 | 2011-02-08 15:37:58 |       1 |
| 180 | 235235          |          1 | 2011-02-08 15:39:49 |       1 |
| 181 | 235235          |          7 | 2011-02-08 15:39:49 |       1 |
| 182 | 235235          |         37 | 2011-02-08 15:39:49 |       1 |
| 183 | 235235          |         43 | 2011-02-08 15:39:49 |       1 |
| 184 | 235235          |          1 | 2011-02-08 15:40:53 |       1 |
| 185 | 235235          |          7 | 2011-02-08 15:40:53 |       1 |
| 186 | 235235          |         37 | 2011-02-08 15:40:53 |       1 |
| 187 | 235235          |         43 | 2011-02-08 15:40:53 |       1 |
| 188 | 235235          |         37 | 2011-02-10 10:00:47 |       1 |
| 189 | 235235          |          1 | 2011-02-10 12:17:05 |       1 |
| 190 | 235235          |          7 | 2011-02-10 12:17:05 |       1 |
| 191 | 235235          |         13 | 2011-02-10 12:17:05 |       1 |
| 192 | 235235          |         37 | 2011-02-10 12:17:05 |       1 |
| 193 | 235235          |         23 | 2011-02-10 12:17:05 |       1 |
| 194 | 235235          |          1 | 2011-02-10 12:21:52 |       1 |
| 195 | 235235          |          7 | 2011-02-10 12:21:52 |       1 |
| 196 | 235235          |         13 | 2011-02-10 12:21:52 |       1 |
| 197 | 235235          |         37 | 2011-02-10 12:21:52 |       1 |
| 198 | 235235          |         23 | 2011-02-10 12:21:52 |       1 |
| 199 | 235235          |          7 | 2011-02-10 12:22:26 |       1 |
| 200 | 235235          |         37 | 2011-02-10 12:22:26 |       1 |
| 201 | 235235          |         23 | 2011-02-10 12:22:26 |       1 |
| 202 | 235235          |          7 | 2011-02-10 14:30:42 |       0 |
| 203 | 235235          |         37 | 2011-02-10 14:30:42 |       0 |
| 204 | 235235          |         23 | 2011-02-10 14:30:42 |       0 |
+-----+-----------------+------------+---------------------+---------+

I did it this way beacuase it seems the easiest and fastest way. Otherwise I would have to lookup all the impacted_products from the table that match that notification_id and check

1) Was there a new product selected? If so, add it.
2) Was a product that was selected no longer selected? If so, delete it.
3) Was a product that was selected before still selected now? If so, leave it alone.

This seemed like a lot of extra looping and a lot of extra DB queries to essentially end up at the same place.

However, I feel that there has still got to be a more efficient way of doing this where I won't have all the extra entries in the impacted_products table. 

Any ideas?

Thanks in advance!

<?php
$db = new PDO('mysql:host=localhost;dbname=wordpress', 'root','');
//---------prepare
$delete3 = $db->prepare("DELETE FROM wp_term_relationships WHERE object_id=:id");
$delete2 = $db->prepare("DELETE FROM wp_posts WHERE ID=:id");
$delete = $db->prepare("DELETE FROM wp_postmeta WHERE post_id=:id");
$select = $db->prepare("SELECT post_id FROM reference WHERE x_id=?");
$delete->bindValue(':id', $id, PDO::PARAM_STR);
$delete2->bindValue(':id', $id, PDO::PARAM_STR);
$delete3->bindValue(':id', $id, PDO::PARAM_STR);
//----------
echo 'conected-----';
{
$delfeed = '';}
$array = explode(',',$delfeed);
foreach($array as $deadman){
$select->execute(array($deadman));
$row = $select->Fetch(PDO::FETCH_ASSOC);
$id = $row['post_id'];
if ($id == null){}
else {
echo "$id"."\n";
$delete->execute();
$delete2->execute();
$delete3->execute();
}
$id++;
}
echo 'done!';
?>

its a simple delete script but it doesnt delete, it does print the right $id's witch means is working till there but delete goes bananas,double checked table names ,colums... tryied working with question mark place holders insted of bind parameter but nothing

I'm working on an existing project, it's a mess but for the moment I just have to get the delete function working, but keep getting an error and I'm not sure what I'm missing.

The query string is a mess, but that's a headache for another day. The target shouldn't ge part of the query string, but that's how someone else had set it up so for now I'm stuck trying to work with it as it is currently and just have to get some basic CRUD functionality going. The other minor snag is inconsistencies with the name of the primary key between tables. It's not consistently called 'ID" so I need to get both the key and value for the where part of the DELETE statement. The query that I currently have is not working. Error 500, can't even get a simple var_dump to work.

 

Query string as it is in its present state:

Quote

somewbsite.com/endpoints/delete.php?target=TABLENAME&primaryKey=7

<?php

include('includes/config.php');
$table = $_GET['target'];

    parse_str($_SERVER['QUERY_STRING'], $data);
    array_shift($data);

    $cols = array_keys($data);
    $vals =  array_values($data);
    $idType = $cols[0];
    $id = $vals[0];

    $stmt = $pdo->prepare("DELETE FROM SOME_DATABASE.$table WHERE $idType = '$id';");
    $status = $stmt->execute();
    if($status)
    {
		echo "Success";
    }
    else
    {
        echo "Fail";
    }
?>

 

Hi all,

this is probably the easiest thing and I may have just been staring at the screen for too long but one of my DELETE FROM queries isn't working. I created a cart and in the admin area when an admin deletes the product it is supposed to delete it from the 'Products' table and also all the sizes from the 'Sizes_List' table. Right now it is only being deleted from the Products table and not the Sizes_List table.. here is the snippet.. any help would be greatly appreciated.

Code: [Select]
if (isset($_GET['yesdelete'])) {
// remove item from system and delete its picture
// delete from database
$id_to_delete = $_GET['yesdelete'];
$deletefrom_sizes = mysql_query("DELETE FROM Sizes_List WHERE product_id = '$id_to_delete'");
$sql = mysql_query("DELETE FROM Products WHERE product_id='$id_to_delete' LIMIT 1");
// unlink the image from server
// Remove The Pic -------------------------------------------
    $pictodelete = ("../user/rent/inventory_images/$id_to_delete.jpg");
    if (file_exists($pictodelete)) {
            unlink($pictodelete);
    }



header("location: addproduct.php");
    exit();
}

Hi
I am struggling with my last page for a project it is a delete page that retrieves data from a list, asks the user if he wants to delete that record if 'yes' delete it otherwise redirect to another page. My page looks like it should, but the function is not working. my code is:
Code: [Select]

<?php

  //check to see if user is logged on
  session_start();
  if (!(isset($_SESSION['login']) && $_SESSION['login'] != "")) {
  header ("Location:login.php");
  }
  
  include('connect.php'); //connection details to database in a connect.php page 

  //if form was submitted
  if (isset($_REQUEST['yes'])){
  
  $product_id = $_GET['product_id'];
  $productname = $_POST['pname'];
  $range = $_POST['prange'];
  $price1 = $_POST['pprice'];
  $price = (int)$price1;
  
  $query = "DELETE FROM products WHERE product_id = '$product_id'";
  $result = mysql_query($query);
 print "<br> $productname from range $range with a price of R$price has been deleted!</br>";
  } 
  }
  
  if (isset($_GET['product_id'])){   
    $product_id = $_GET['product_id'];
    $query = "SELECT * FROM products WHERE product_id = '$product_id'";
    $result = mysql_query($query);
  
  echo "<form method = \"post\" action = \"delete.php\">";
    
  while($row = mysql_fetch_array($result)){
  echo "<table>";
  echo "<tr>";
  echo "<td><input name = 'product_id' type = 'hidden' value ='{$row['product_id']}'></td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td>Do you really want to delete {$row['product_name']} from range {$row['product_range']} with a price of R{$row['product_price']} from the database?</td>";
  echo "</tr>";
  echo "<tr>";
  echo "<td<input type = 'submit' name = 'yes' value = 'Yes'><input type = 'submit' name = 'no' value = 'NO'></td>";
  echo "</tr>";
  echo "</table>";
  }
  echo "</form>";
  }   
  ?>
Any help please?