2. PHPRunner General questions
  3. Deleting related records
This topic is locked

Deleting related records

 		2/13/2007 2:36:14 PM
PHPRunner General questions
D
 Dale author

I have seached the forum for help with deleting related records with the Before Delete function and Im still not sure of the snippets needed to delete linked records when deleting a master record.
I have a record called construction_data that has two tables linked to it this mastertable, project_details and project_notes
When the user checks one (or all) of the records on the mastertable list to delete, I need to check to see if there are other records that are linked to the master. If so, delete them, then check the other table, delete and then finally delete itself from the master table.
I have played with the Check if record exists in the before delete event, Im just not sure of the exact delete code snippet I would use when records are found.
But the other question I had related to this, if a user selected a 100 of these records to delete, and each of the 100 records had 20 records that are related to it, that would be over 2000 deletions. Will there be any problems here with time to delete all these records. Is there a more effecient way to do this if this is in fact an issue.
J
 		Jane 2/14/2007

Dale,
here is a sample code of the BeforeDelete event for the mastertable:

function BeforeDelete($where)

{

global $conn;

$str = "select MasterForeignKey from mastertable where ".$where;

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);

//$data["MasterForeignKey"]- FK
$strSQLExists = "select * from project_details where DetailForeignKey=".$data["MasterForeignKey"];

$rsExists = db_query($strSQLExists,$conn);

$dataExists=db_fetch_array($rsExists);

if($dataExists)

{

$strDelete = "delete from project_details where DetailForeignKey=".$data["MasterForeignKey"];

db_exec($strDelete,$conn);

}

return true;

}
D
 		Dale author 2/14/2007

Thanks Jane,
Perfect. This would be a great snippet for that snippet database. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=15533&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
Thanks for the sample.
Z
 		zavka 2/15/2007

Hi
Look at this solution: create Foreign Key with ON DELETE Rule set to CASCADE and example ON UPDATE Rule set to CASCADE

All this things do in your database