There are 2 tables. Product (the product itself) and images (id, the product_id and the path to the image are stored). How to implement the removal of the product and images from the database. Now I only have to remove the product (it’s impossible to delete through join). How to do this correctly? Need to delete multiple queries? Can I change 2 tables from MyIsam to InnoDB? Nothing breaks because of this? so that you can take advantage of cascading delete.

    2 answers 2

    You need to configure cascade deletion, for example, you have tables:

    Products:

    CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL );

    Pictures:

     CREATE TABLE images ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE );

    And then removing the product images will be deleted automatically.

    You can read here a more detailed example.

    If your tables are created in MyISAM then they need to be converted to InnoDB, you can do it like this:

    1. Execute the query:

      SET @DATABASE_NAME = 'name_of_your_db';

      SELECT CONCAT ('ALTER TABLE ', table_name, ' ENGINE = InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_

    2. Then copy this query and execute it as a new SQL query.

    • This is if the type is Inno. and I have MyISAM, Can I change it to Inno in phpmyadmin? - Andrii
    • @Andrii yes, added a request in response - Yaroslav Molchan

    Join only for fetching data. It will be necessary to make two requests, one to delete from the images table (delete the file at the same time) and only then delete from the Product.

    • Thank you, made 2 requests and everything turned out. - Andrii