+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Update) Why does a simple insert fail? (/showthread.php?tid=81575)
Update) Why does a simple insert fail? - yoshi - 03-19-2022
## 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/1GCiEKXthXqXUdoIn_i5zz_Wqp1XJ-DaL/view?usp=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_4LqPKJv-Xlzaga6fY9Q1imkt/view?usp=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/1eZQB0Hyj_ByLOhmNYff8HeSGF9z6aUoD/view?usp=drivesdk
Maybe Codeigniter shouldn't use foreign keys?
RE: Update) Why does a simple insert fail? - iRedds - 03-20-2022
Are all fields specified in CertificationModel::$allowedFields?
RE: Why does a simple insert fail? - yoshi - 03-20-2022
(03-20-2022, 05:41 PM)iRedds Wrote: Are all fields specified in CertificationModel::$allowedFields?
Thank you as always. I was able to resolve the SQL execution. I thought that foreign keys were not covered by "permitted items".