2. PHPRunner General questions
  3. [SOLVED]  how to update records in table B when deleting item in table A?
This topic is locked
[SOLVED]

 how to update records in table B when deleting item in table A?

 		2/28/2017 5:23:35 PM
PHPRunner General questions
W
 wfcentral author

Can you help me with formatting some SQL into a phprunner event?
I have two tables...
invoices (id, date, company)

time_entries (id, time, inv_ID)
I have code working to add the inv ID to my time_entries table.
Now I would like it so if the user deletes an invoice it performs this sql
update time_entries SET inv_ID = '' WHERE inv_ID = $the_invoice_being_deleted
Can you point me to a phprunner version of this code?
I know it will be an event on the "before record deleted area"
S
 		stiven 2/28/2017

you have to put the code in after record deleted event on the list page for invoice. Wouldn't you want to delete the record there instead of changing the value to nothing?


$sql = "UPDATE time_entries SET inv_ID = '' WHERE inv_ID = '".$deleted_values['id']."'";

CustomQuery($sql);
W
 		wfcentral author 3/1/2017



you have to put the code in after record deleted event on the list page for invoice. Wouldn't you want to delete the record there instead of changing the value to nothing?


$sql = "UPDATE time_entries SET inv_ID = '' WHERE inv_ID = '".$deleted_values['id']."'";

CustomQuery($sql);



that worked perfectly! Thanks!
90288 3/1/2017

Hello

Stivens answer is doing what exact you asked for. but if by chance (as he asked in his reply) you want to delete the records in table time_entries when the parrent id is deleted in table invoices, you can set inv_ID in table time_entries as foreign key , by executing this sql code for MYSQL

ALTER TABLE time_entries

ADD CONSTRAINT fk_invoices

FOREIGN KEY (inv_ID)

REFERENCES invoices(id)

ON DELETE CASCADE

ON UPDATE CASCADE

;