2. PHPRunner General questions
  3. Delete master child records
This topic is locked

Delete master child records

 		4/19/2011 9:17:42 AM
PHPRunner General questions
author

I know that this was answered before...

I searched the forum and I found this topic link.

But this is not working for me.

Since I am a total beginner please explain me this code:

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;

}


I have two tables: projects and results.

Projects is the master table.

The relation is between Title in master table projects and an Title in child table results.
So, I want to delete a Title in the Project title and all the associated records from the results table to be also deleted.
From the code below that means to add a "before record deleted" to the list page in Projects table with this code:

function BeforeDelete($where,$deleted_values,$message)

{

{

global $conn;

$str = "select Title from Projects where ".$where;

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

$data = db_fetch_array($rs);

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

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

$dataExists=db_fetch_array($rsExists);

if($dataExists)

{

$strDelete = "delete from results where Title=".$data["Title"];

db_exec($strDelete,$conn);

}

return true;

}


The result is an Mysql error : unknown column "Project title" in where clause...

What I am doing wrong???
Sergey Kornilov admin 4/19/2011

Make sure text values are wrapped by single quotes in your code.
Wrong:

Delete from results where title=project title
Right:

Delete from results where title='project title'
500489 4/19/2011



Make sure text values are wrapped by single quotes in your code.
Wrong:

Delete from results where title=project title
Right:

Delete from results where title='project title'


Thank you for your suggestion.
It seems that the problems originates there but when I change the code to this:



function BeforeDelete($where,$deleted_values,$message)

{

global $conn;

$str = "select 'Title' from projects where ".$where;

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

$data = db_fetch_array($rs);

//$data["MasterForeignKey"]- FK
$strSQLExists = "select * from results where Title=".$data['Title'];

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

$dataExists=db_fetch_array($rsExists);

if($dataExists)

{

$strDelete = "delete from results where Title=".$data['Title'];

db_exec($strDelete,$conn);

}

return true;

}


NOW It works excessively in the way That if I delete one master Project it deletes ALL the child records even the ones that are not related with that project.

Example:

Project1 (master) - result1, result2, result3 (child)

Project2 (master) - result22, result23, result24 (child)

(Title is the master-child relation - present in both tables)
I choose to delete Project1 and it deletes ALL the child records even the ones from Project2. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=57723&image=1&table=forumreplies' class='bbc_emoticon' alt=':blink:' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=57723&image=2&table=forumreplies' class='bbc_emoticon' alt=':blink:' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=57723&image=3&table=forumreplies' class='bbc_emoticon' alt=':blink:' />
I know that I can use cascade delete on the mysql level but I've seen this snippet on the forum and I like this method better.
Sergey Kornilov admin 4/19/2011

Hard to tell what might be wrong without seeing your files and data. I recommend to upload your app to Demo Account and to contact support directly.