2. PHPRunner General questions
  3. Delete Child when Master deleted...
This topic is locked

Delete Child when Master deleted...

 		10/23/2008 5:52:03 PM
PHPRunner General questions
W
 wfcentral author

I have two tables...
tbl_customers (id, name)
tbl_location (id, customer_ID, city, state)
I have the tables joined on tbl_customers.id > tbl_location.customer_ID
I cannot find a way to delete the "detail" records when the "master" record is deleted.
I can see in the event area where it should go, but I cannot find any examples of the code...
A
 		alang 10/23/2008

In the BeforeDelete event:

  • get the id of the master record being deleted (using $where variable)
  • execute SQL to delete the detail records ie

    "DELETE FROM tbl_location WHERE customer_ID={id of master record being deleted}"
M
 		mustafa 10/25/2008

Hello,
You can do it inside the database by creating foreign key for 'customer_ID'
For example, In MySQL:
CREATE TABLE `tbl_location` (

`id` int(11) NOT NULL auto_increment,

`customer_ID` int(11) NOT NULL,

......

...

CONSTRAINT `name_of_foreign_key` FOREIGN KEY (`customer_ID`) REFERENCES `tbl_customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;
Note: MyISAM doesn't support foreign keys.

I have two tables...

tbl_customers (id, name)
tbl_location (id, customer_ID, city, state)
I have the tables joined on tbl_customers.id > tbl_location.customer_ID
I cannot find a way to delete the "detail" records when the "master" record is deleted.
I can see in the event area where it should go, but I cannot find any examples of the code...
W
 		wfcentral author 10/30/2008

In the BeforeDelete event:

  • get the id of the master record being deleted (using $where variable)
  • execute SQL to delete the detail records ie

    "DELETE FROM tbl_location WHERE customer_ID={id of master record being deleted}"
    okay, I'm dumb... can someone show me an example of how to use the $where code?
J
 		Jane 10/31/2008

Hi,
here is just a sample:

global $conn;

$rs = db_query("select MasterID from MasterTable where ".$where,$conn);

while($data = db_fetch_array($rs))

{

//remove detail records for selected master record

$strDelete = "delete from DetailTableName where DetailID=".$data["MasterID"];

db_exec($strDelete,$conn);

}