2. PHPRunner General questions
  3. [SOLVED] Master-detail problem
This topic is locked
[SOLVED]

Master-detail problem

 		1/3/2024 4:04:22 AM
PHPRunner General questions
I
 I author

Hello everyone,
I use a master-detail table in an application. If I delete a record in the master table, I also want to delete the records in the detail table. That's why I wrote this code in the "before record deleted" event in the master table:

$id = $deleted_values["ID"];
$sql = DB::Query("delete from commantaires where visitenr = '$id'");
DB::Exec($sql);
return true;

My local web server and the host use PHP 8.0``

This works great on localhost, but not on the hoster, the record is deleted in the detail table but not in the master table, an error message appears, see screenshot:

img alt

C
 		cristi 1/3/2024

DB::Exec expects a string but you write the query string as an query object: DB::Query("delete from commantaires where visitenr = '$id'");

There is no need for the DB::Query part because you are not returning any data - you are just deleting some records.

So just delete the DB::Query part and leave the rest as:

$id = $deleted_values["ID"];
DB::Exec("delete from commantaires where visitenr = '" . $id . "'");
return true;

Also, in my opinion, you should never leave master details delete/update relations to PHP - this is something for MySql to take care and any DB server for that matter whenever is possible. In MySQL there is something called foreign key constraints that you can use to cascade delete, update, etc child records when a primary key master value is changed or deleted - even more, using foreign keys constraints has the benefit of adding database level integrity checks for your data.

In your case instead of writing PHP code you could just implement a foreign key constraint on your details table so that all the detail records will cascade delete when a master record with that ID is deleted - in my opinion this is the best solution to your problem.

If you still decide to go PHP way you should parametrize your query ( using "visitenr = '$id'" is like asking for trouble).
I
 		I author 1/3/2024

Thank you for the valuable tip, it works fine now