I want to delete a entry from table1 and delete also the matching entry from table2, link with table1 with foreign key and ON DELETE CASCADE. On Mariadb, the cascade works, entries form table1 and table2 are deleted but not with pdo prepare and execute. Only the entry of table1 is deleted from the database. It doesn't impact my site, but the database is not clean. I checked similar questions without finding answers to my case.
My tables :
//table1
CREATE TABLE plats(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
description VARCHAR(300) NOT NULL,
price INTEGER NOT NULL) ENGINE=InnoDB;
CREATE TABLE category1(
c1_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1_type VARCHAR(255) NOT NULL) ENGINE=InnoDB;
idem category2
//table2
CREATE TABLE plats_categories(
pc_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
plats_id INTEGER NOT NULL,
category1_id INTEGER NOT NULL,
category2_id INTEGER NOT NULL,
FOREIGN KEY(plats_id) REFERENCES plats(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(category1_id) REFERENCES category1(c1_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(category2_id) REFERENCES category2(c2_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
php model:
<?php
require_once __DIR__ .'/../db.php';
$getIdCourse = $_GET['id'];
$courses = $pdo->prepare('SELECT * FROM plats WHERE id = ?');
$courses->execute(array($getIdCourse));
if ($courses->rowCount() > 0)
{
$deleteCourse = $pdo->prepare('DELETE FROM plats WHERE id = ?');
$deleteCourse->execute(array($getIdCourse));
}else
{
$_SESSION['flash']['danger'] = 'Aucun plat n\'a été trouvé';
}
php controller :
<?php
session_start();
if (isset($_GET['id']) && !empty($_GET['id']))
{
require __DIR__ .'/../../../model/menus-carte/delete-course.php';
$_SESSION['flash']['success'] = 'Le plat a bien été supprimé';
header('Location: admin-courses.php');
}else
{
$_SESSION['flash']['danger'] = 'L\'identifiant du plat n\'a pas été récupéré';
}
I assume the problem comes from the php code because it works on Mariadb, but I don't see it. I'm new in developping, I may forgot something evident. Thank you for your help !
