2. PHPRunner General questions
  3. Before Delete and After Delete Events
This topic is locked

Before Delete and After Delete Events

 		2/1/2010 3:03:28 PM
PHPRunner General questions
S
 scopley author

I am trying to capture the values of an array in a before delete event so that I can use them to delete files from another table in an after delete event. I have the array however it is only deleting 1 row of data from the files table when there should be multiple rows of data deleted.
Here is my Before Delete Event:

$recid = preg_split('/=/', $where);

$table = $recid[0];

$mainId = $recid[1];
// connect

$conn=db_connect();
//query for main record file foreign key from SERVICES_REQUEST_FILEIDS table

$query = "SELECT AF_MAIN_FK FROM SERVICES_REQUEST_FILEIDS WHERE AF_MAIN_FK = '".$mainId."'";

$stid = oci_parse($conn, $query);

$r = oci_execute($stid, OCI_DEFAULT);

$row = oci_fetch_array($stid, OCI_DEFAULT);

$rowID = $row[0];

$mainfk = $rowID;
//query for main record file foreign key from SERVICES_REQUEST_FILEIDS table

$query = "SELECT AF_FILES_FK FROM SERVICES_REQUEST_FILEIDS WHERE AF_MAIN_FK = '".$mainfk."'";

$filestmt = oci_parse($conn, $query);

$fr = oci_execute($filestmt, OCI_DEFAULT);

$frow = oci_fetch_array($filestmt, OCI_DEFAULT);

$frowID = $frow[0];

$filesfk = $frowID;
$_SESSION['filefk']=$filesfk;

return true;


Here is my After Delete Code:

$afid=$_SESSION['filefk'];
// connect

$conn=db_connect();
//delete BLOB from ARCHIVE_FILES table after entry deleted from SERVICES_REQUEST_FILEIDS table

$strDelete = "DELETE FROM ARCHIVE_FILES WHERE AF_FILE_ID_PK = '".$afid."'";

$stmtdel = oci_parse($conn, $strDelete);

oci_execute($stmtdel, OCI_DEFAULT);

oci_commit($conn);


Any help would be greatly appreciated.
A
 		alang 2/1/2010

Its a little tricky to see what is supposed to be happening without a bit more info on your database structure.
You have two queries in the before delete event operating on the same table. The way it is written you will only end up with a single value in the session variable ($_SESSION['filefk']). If you wanted this to have multiple values you would need to loop and repeat the fetch_array call for all the rows returned by the second query (and also loop the third query).
If a single value is all you want then the query in "after delete" may not be right. It seems in the WHERE clause that you are matching a primary key (AF_FILE_ID_PK) which would normally be unique and only return a single result anyway. Perhaps there is another FK field you need to match that would return multiple rows?
S
 		scopley author 2/2/2010

Sorry about the previous reply. Got click happy.
The table structure is a little tricky. I can't change anything in the ARCHIVE_FILES table because it is use by other application. The SERVICES_REQUEST_FILEIDS table is used as an in between table so that I can attach more than 1 blob to a data row in the SERVICES_REQUEST table. So when I delete I have to get the Primary Key of the SERVICES_REQUEST table match it to A foreign key on the FILEID table. That foreign key will correspond with a foreign key of the blob. This matches the primary key of the ARCHIVE_FILES table. This is what it would potentially look like:
FILEIDS TABLE
AF_MAIN_FK AF_FILES_FK

1 1

1 2

1 3
So I should be getting multiple values that will match up to the ARCHIVE_FILES table for deleting.
Hope this makes sense and thanks so much for your response.
J
 		Jane 2/2/2010

Hi,
this events are executed for each deleted record separately.

Also you can use After record deleted event only to select values and delete records in another table.
S
 		scopley author 2/2/2010

The After Record Delete event is working except it is only deleting 1 record. I believe my issue is with this Before Record Delete event:

//query for main record file foreign key from SERVICES_REQUEST_FILEIDS table

$query = "SELECT AF_FILES_FK FROM SERVICES_REQUEST_FILEIDS WHERE AF_MAIN_FK = '".$mainfk."'";

$filestmt = oci_parse($conn, $query);

$fr = oci_execute($filestmt, OCI_DEFAULT);

$frow = oci_fetch_array($filestmt, OCI_DEFAULT);

$frowID = $frow[0];

$filesfk = $frowID;



$_SESSION['filefk']=$filesfk;


I need it to loop through multiple values and save them but am not quite sure how to do that and then save it as a $_SESSION variable to use on the After Record Delete event.
J
 		Jane 2/3/2010

Both these events (Before record deleted and After record deleted) are executed for each row separately.

There is no loop in these events.
S
 		scopley author 2/4/2010

So can you suggest a way I can accomplish what I'm trying to do. I need to be able to delete those records.
Thanks!
J
 		Jane 2/4/2010

I recommend you to publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with detailed descritpion of what you want to achieve.