Using PHP Objects to Access Your Database Tables (Part 2)
By Tony Marston2005-04-07
Deleting Rows
|
|
When it comes to deleting a row which has relationships there may need to be some additional action required, either before the row is deleted or afterwards, depending on the nature of the relationship. Using the $relationship Array described earlier I am now able handle these situations with standard code. Before a row is deleted I check for the existence of associated rows on related tables where the relationship type has been set to 'restricted'. If any rows exist the deletion is not allowed to continue.
foreach ($this->relationship as $reldata) {
switch ($reldata['type']){
case 'restricted':
// delete is not allowed if relationship is 'restricted'
$where = NULL;
foreach ($reldata['fields'] as $one => $many) {
$where .= "$many='$fieldarray[$one]' AND ";
} // foreach
$where = rtrim($where, ' AND');
// set up query to count occurrences
$query = "SELECT count(*) FROM {$reldata['many']} WHERE $where";
$count = $this->getCount($query);
if ($count <> 0) {
$this->errors[] = "Cannot delete - record still linked to "
.strtoupper($reldata['many'])
." table");
} // if
break;
case 'delete':
case 'nullify':
break;
default:
$this->errors[] = "Unknown relation type: " .$reldata['type']";
} // switch
} // foreachThe following code deals with associated rows on related tables just before the selected row is deleted. There is a choice between deleting all associated rows or setting the foreign key fields to NULL.
foreach ($this->relationship as $reldata) {
switch ($reldata['type']){
case 'nullify':
// set foreign key(s) to null
$where = NULL;
$update = NULL;
foreach ($reldata['fields'] as $one => $many) {
$where .= "$many='$fieldarray[$one]' AND ";
$update .= "$many=NULL,";
} // foreach
$where = rtrim($where, ' AND');
$update = rtrim($update, ',');
// set up query to update the database
$query = "UPDATE {$reldata['many']} SET $update WHERE $where";
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
break;
case 'delete':
// delete all related rows
$where = NULL;
foreach ($reldata['fields'] as $one => $many) {
$where .= "$many='$fieldarray[$one]' AND ";
} // foreach
$where = rtrim($where, ' AND');
// set up query to update the database
$query = "DELETE FROM {$reldata['many']} WHERE $where";
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
break;
case 'restricted':
break;
default:
$this->errors[] = "Unknown relation type: " .$reldata['type']";
} // switch
} // foreach
Tutorial Pages:
» Intended Audience
» Extending the MySQL SELECT statement
» Adding the features of a Data Dictionary/Repository
» Using the Data Dictionary/Repository
» Changing Candidate Keys
» Deleting Rows
» Custom Processing
» Virtual Database Tables
» Summary
|
