PHP MYSQL Delete query doesn't delete data from table

Question

This is the function I use to query the data

public function MassDelete($POST){

    $DB = new Database();

    for ($i=0; $i < count($POST['delete-checkbox']); $i++) { 

        $productID = $POST['delete-checkbox'][$i];

        $CID = ($DB->read("SELECT * FROM products WHERE id = $productID"));
        $categoryName = $this->CategoryList[$CID[0]["category_id"]];

        $productQuery = "DELETE * FROM products WHERE id = $productID";
        $productData = ($DB->write($productQuery));
        if($productData){
            $categoryQuery = "DELETE * FROM $categoryName WHERE product_id = $productID";
            $categoryData = ($DB->write($categoryQuery));
        }

    }

    // header("Location:" . ROOT . "Home");
}

This is the DB->Write Method that I'm using

public function write($query, $data=[])
{
    $db = $this->Connect();

    if(count($data)==0){
        $stm = $db->query($query);
        $check = 0;
        if($stm){
            $check = 1;
        }
    }else{
        $stm = $db->prepare($query);
        $check = $stm->execute($data);
    }

    if($check)
    {
        return true;
    }else{
        return false;
    }
}

This line of code works just fine and I'm able to fetch the data, so I don't understand why the DELETE query doesn't work

$CID = ($DB->read("SELECT * FROM products WHERE id = $productID"));

Your `read` and `write` methods allow you to execute queries with parameters. Why aren't you using that? — Barmar, May 21 '22 at 00:22
You should enable myqli error reporting. See https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments — Barmar, May 21 '22 at 00:24
the write method need be re-implemented for CUD operation. You can always get error message when there is error happening. — SeanH, May 21 '22 at 03:43
`DELETE * FROM` is incorrect syntax. You don't tell `delete` what columns to delete. This also is potentially open to SQL injections..should parameterize queries and use prepared statements. — user3783243, Jun 01 '22 at 11:36

Tarkhna · Answer 1 · 2022-06-01T11:34:57.663

I found a solution, thanks to everyone for helping me out.

public function MassDelete($POST){

    $DB = new Database();

    //loop to delete all selected products
    if(isset($POST['delete-checkbox'])){
        for ($i=0; $i < count($POST['delete-checkbox']); $i++) { 
        
            //get product id
            $productDetails['id'] = $POST['delete-checkbox'][$i];
        
            //get product category name and store in $CategoryDetails['name']
            //get product category id and store in $categoryDetails['product_id']
            $CID = ($DB->read("SELECT * FROM products WHERE id = :id", $productDetails));
            // $categoryDetails['name'] = $this->CategoryList[$CID[0]["category_id"]];
            $categoryDetails['product_id'] = $POST['delete-checkbox'][$i];
        
            //Query to delete the product from products table
            $productQuery = "DELETE FROM products WHERE id=:id";
            $productData = ($DB->write($productQuery,$productDetails));
        
            //Query to delete the categoryDetails of the product from the category table
            $categoryQuery = "DELETE FROM ". $this->CategoryList[$CID[0]["category_id"]] ." WHERE product_id=:product_id";
            $categoryData = ($DB->write($categoryQuery, $categoryDetails));
        }
    }
    header("Location:" . ROOT);
}

using params instead of writing the entire SQL query in PHP is more reliable.

for anyone having the same problem, try saving the values in a variable and passing it with an array to the write function.

PHP MYSQL Delete query doesn't delete data from table

1 Answers1