delete from table using mysql and php

0
votes

I have to create a php file that will delete some rows from a mysql database.

For the first version, I have three tables: category_description, product_to_category and product_description.

I must delete the products from the product_description table if they are in a given category like notebooks or phones.

The problem is that this table has only the product_id and the product_name columns.

The product_to_category table contains the product_id and the category_id.

The category_description table has the category_id and category_name columns.

The problem is that I need to write a php code, that function will be the following.

Check the given category name (for example: smartphone) in the category_description table, in the category_name column. If it finds it, than use it's value number (like 234) from the category_id column and compare it with the category_id that can be found in the product_to_category table. If it finds it, than use the data belongs to if from the product_id column and check this in the product_description table's product_id column. If it's a match then delete the given row.

For this I have to write a php/sql code. I don't know that this can be done in this form.

What I've done so far is the database connection and the locating of the category_id for the given category name like this:

$sql = "SELECT category_id FROM category_description WHERE name = 'smartphone'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["category_id"]. "<br>";
        $data[]=array('ID' =>$row["category_id"]);
        print_r ($data);
    }

} else {
    echo "Not found";

}
2
phpmysqldatabasemysqli

2 Answers

0
votes

You can use DELETE with JOIN as follows:

DELETE product_description
FROM
category_description cd
JOIN product_to_category pc ON cd.category_id = pc.category_id
JOIN product_description as pd ON pd.product_id = pc.product_id
WHERE cd.category_name = 'smartphone'
0
votes

This code can help I think 

<?php
    $sql = "SELECT category_id FROM category_description WHERE name = 'smartphone'";
    if(!empty($cat = $conn->query($sql)->fetch_array()))
    {
         $sql = 'SELECT * FROM product_category WHERE category_id = "'. $cat['category_id']. '"';
         $cat_products = $conn->query($sql)->fetch_all(PDO::FETCH_ASSOC);
         foreach($cat_products as $product)
         {
             $sql = 'DELETE FROM product_description WHERE product_id = "'.$product['product_id'].'"';
             $conn->query($sql);
         }
    }