Delete script not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

Delete script not working

Post by KevinCB »

I have a script that creates a page with a combobox that lists all of the items in the table, you select one and use a submit button to delete the record, simple enough. Anyway, this script did work before, but for some reason it isn't deleting the record now at all. It displays the message that is meant to come up after the record is deleted e.g. Record successfully deleted, but it's not been deleted at all.

Could someone have a look, here's part of the script.

Code: Select all

<?php

if ($_POST['op'] != "delete") {
   //haven't seen the form, so show it
   $display_block = "<h1>Select a CD</h1>";
   //get parts of records
   $get_list = "SELECT cat_id, concat_ws(', ', item_name) AS display_name FROM store_items
        RIGHT JOIN store_categories ON store_items.cat_id = store_categories.id
         WHERE store_categories.id = 2";
   $get_list_res = mysql_query($get_list)  or die(mysql_error());

   if (mysql_num_rows($get_list_res) < 1) {
   //no records
   $display_block .= "<p><em>Sorry, no records to select!</em></p>";

   } else {
       //has records, so get results and print in a form
       $display_block .= "
       <form method=\"post\" action=\"$_SERVER[PHP_SELF]\">
       <p><strong>Select a Record to Delete:</strong></p><br>
       <select name=\"sel_id\">
       <option value=\"\">-- Select One --</option>";

       while ($recs = mysql_fetch_array($get_list_res)) {
          $cat_id = $recs['cat_id'];
          $display_name = stripslashes($recs['display_name']);

          $display_block .= "<option value=\"$cat_id\">
               $display_name</option>";
       }
       $display_block .= "
       </select>
       <input type=\"hidden\" name=\"op\" value=\"delete\">

       <p><input type=\"submit\" name=\"submit\" value=\"Delete Selected Entry\"></p>
       </form>";
   }

} else if ($_POST['op'] == "delete") {

    //check for required fields
      if ($_POST['sel_id'] == "") {
        header("Location: delcd.php");
        exit;
    }

    //issue queries

    $del_items = 'DELETE FROM store_items WHERE id = '.$_POST['sel_id'];
    mysql_query($del_items)  or die(mysql_error());

    $del_books = 'DELETE FROM store_cds WHERE item_id = '.$_POST['sel_id'];
    mysql_query($del_books)  or die(mysql_error());

    $flush_items = 'FLUSH TABLE `store_items`';
    mysql_query($flush_items)  or die(mysql_error());

    $flush_books = 'FLUSH TABLE `store_cds`';
    mysql_query($flush_books)  or die(mysql_error());

    $display_block = "<h1>Record(s) Deleted</h1>
    <p>Would you like to
    <a href=\"$_SERVER[PHP_SELF]\">delete another</a></p>";
}
?>
Thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

try echoing $del_items and $del_books and see what they look like.

maybe it's not getting the id for some reason.

if the queries do indeed look good, try copying and pasting them into phpMyAdmin or the like to ensure they can delete from there.

From what I see, things look in order, but I'm guessing it's just not getting the correct ID
KevinCB
Forum Commoner
Posts: 32
Joined: Tue Mar 01, 2005 6:00 am

Post by KevinCB »

echoed the variables and it came back with the correct id:
DELETE FROM store_items WHERE id = 2 DELETE FROM store_cds WHERE item_id = 2
Just realised that it's doing something I don't want it to do.
I want it to delete a single item with the cat_id of 2 in the store_items table, but what it has done is deleted the item with the id of 2.

But if I change it to cat_id instead of id it's going to delete all of the items with the cat_id of 2, but I only want it to delete the one I select.

How can I do this?, I think it worked before because I only had one record in that table using the same id's.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

why don't you pass the row id from your form then instead of the cat_id? You're going to have to tie it to something unique on the table to only delete a single row and the best way is to use the primary key (usually "id").

what I'd do is just modify your form page and change the option values on your select to reflect the row ids instead of the cat_id.
Post Reply