How I can delete `id` and `sub id`

Question

I have database like this:

id	name_categories	id_categories
1	General	0
2	hotnews	1
3	breakingnews	2

The main categories are:

• (General) with id = 1 and id_categories = 0
• Sub categories for main (hostnews) id_categories = 1
• (breakingnews) is sub main for (hotnews) with id_categories = 2
• id linked with id_categories

Deleting general should delete all id_categories linked with:

• id main
• sub main
• sub sub main How can this be done with PDO PHP?

Answer 1

Correct database design covers this type of tasks automatically:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name_categories` varchar(255) NOT NULL,
  `id_categories` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_categories` (`id_categories`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`id_categories`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Whenever you delete a row from this table, mysql will also delete all the referencing rows, and rows that reference referencing rows, etc.

You can recursively delete rows with PHP, but this is a bad idea for a number of reasons.