Possibilities with Modify Actions amd Databases
Posted: Wed Apr 25, 2007 11:56 am
Imagine there are 2 tables. Head and Hair. Head has a foreign key to Hair that may be null (a head can be bald). A simple parent child relationship, each table describes itself. I have these in separate tables because I believe it gives me extra flexibility e.g. if I wanted a new table to describe a cat I could use the existing Hair table to describe its fur. OK I realise this is an eccentric example but bare with me.
So the SQL looks a bit like this
Now say I have a load of PHP code where people can create and modify heads. There is one create form and one modify form. This means the POST submissions from these forms are going to be dealing with both tables. The conditionally of the hair is handled by using a check field to toggle hair on or not. On the create form this is simple:My predicament begins with the modify form. When I modify a head that Head record may or may not have Hair and the user may or may not want it modified to have Hair. So I'm faced with 4 possible scenarios I have to cope with.
Oh this is only with one optional table. What if there are 2 or more! Say I want my head to optionally have a hat.
So the SQL looks a bit like this
Code: Select all
CREATE TABLE `Head` (
`headId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`headId`),
`roundness` INT UNSIGNED NOT NULL DEFAULT 0,
`_hairId` INT UNSIGNED NULL
);
CREATE TABLE `Hair` (
`hairId` INT UNSIGNED NOT NULL,
`hexColour` CHAR(6) NOT NULL,
`greasyness` INT UNSIGNED NULL
);Code: Select all
if ($_POST['wantHair']) {
// add a hair record
$hairId = $db->insert_id;
} else {
$hairId = 'null';
}
// create the head record using the $hairId- There is currently no hair on the head and we don't need to add any (SELECT `Head` followed by UPDATE `Head`)
- There is currently no hair on the head and the user wants to add some (SELECT `Head` followed by INSERT `Hair` followed by UPDATE `Head`)
- There is currently hair on the head and the users wants to get rid of it (SELECT `Head` followed by DELETE `Hair` followed by UPDATE `Head`)
- There is currently hair on the head and the user wants to keep it and no doubt update some of properties of the hair (SELECT `Head` followed by UPDATE `Hair` followed by UPDATE `Head`)
Oh this is only with one optional table. What if there are 2 or more! Say I want my head to optionally have a hat.