## overview
The `m_certification` table is for all credentials.
The `certification` table is managing certification in the school use.
Each table relationship is 1:1.
The `m_certification` table has school_id for use. using to get school information.
But my php code failed for insert.
Why does a simple insert fail?
https://drive.google.com/file/d/1GCiEKXt...p=drivesdk
## detail
Now I'm trying to make a record in the `certification` table then I enter the record by hand, it can be inserted correctly.
https://drive.google.com/file/d/1ipPO9vQ...p=drivesdk
PHP Code:
-- ci4db.certification definition
CREATE TABLE `certification` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`school_id` int unsigned NOT NULL,
`m_certification_id` int unsigned NOT NULL,
`name_short` varchar(100) NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `school_id_m_certification_id` (`school_id`,`m_certification_id`),
KEY `certification_m_certification_id_foreign` (`m_certification_id`),
CONSTRAINT `certification_m_certification_id_foreign` FOREIGN KEY (`m_certification_id`) REFERENCES `m_certification` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `certification_school_id_foreign` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
PHP Code:
INSERT INTO ci4db.certification
(id, school_id, m_certification_id, name_short, created_at, updated_at)
VALUES(14, 1, 35, '漢検準１級', '2022-03-20 11:22:07', '2022-03-20 11:22:07');
PHP Code:
public function editPost(): void
{
// guide: check box state update to model
https://codeigniter.com/user_guide/tutorial/create_news_items.html
$checks = $this->request->getPost('checked');
$nameShort = $this->request->getPost('name_short');
// TODO: Using demo school
$schoolId = SchoolController::SCHOOL_LIST['Demo'];
$model = model(CertificationModel::class);
$alreadyExists = $model->where('school_id', $schoolId)->findAll();
// TODO: focus to create!
$certifications = [];
foreach ($checks as $mCertificationId => $v) {
$certification = new CertificationEntity([
'school_id' => $schoolId,
'm_certification_id' => $mCertificationId,
'name_short' => $nameShort[$mCertificationId]
]);
if (!in_array($mCertificationId, array_column($alreadyExists, 'm_certification_id'))) {
$certifications['create'][] = $certification;
} elseif (isset($mCertificationId)) {
$certifications['update'][] = $certification;
} else {
$certifications['delete'][] = $certification;
}
}
foreach ($certifications['create'] as $record) {
// dd($record);
$model->save($record);
}
redirect('school/certification/list');
}
## additional report
Looking at the stack trace, there is sloppy SQL.
Despite being converted from Entity, SQL does not contain foreign key information. This certainly causes a foreign key violation.
https://drive.google.com/file/d/1eZQB0Hy...p=drivesdk
Maybe Codeigniter shouldn't use foreign keys?
Update) Why does a simple insert fail?
