Deleting Multiple Files

scopley

I'm new to PHP so bear with me. I'm trying to write a function that will delete multiple files from a database. The main table has 2 secondary tables attached to it through the use of primary/foreign keys. 1 of them being just a file id table that links to a blob table. If the main data row has more than 1 blob attached to it will show 2 entries in the file id table referencing the primary key from the main table and the primary key from the files table. You would have an entry like this:

Main Primary Key - Files Primary Key
1 - 1
1 - 2

I delete the main table row the file ids table rowS but when it gets to the actual blob table it will only delete the first row. I created an array to pull all the file ids and when I echo them out it is referencing the correct primary keys. So I'm at a loss. Here is my code:

function deleteEntry($selection, $conn)
{
  for($a = 0; $a < sizeof($selection); $a++)
   {
     /*gets the fk to vendor_survey_fileids*/
     $selectFkStmt = oci_parse($conn, getSelectFkQuery());
     oci_bind_by_name($selectFkStmt, ':VS_ID_PK', $selection[$a]);
     oci_execute($selectFkStmt);		
     while ($row = oci_fetch_array($selectFkStmt))
      { $deleteFk = $row[0]; }

  oci_free_statement($selectFkStmt);

   /*deletes the data from VENDOR_SURVEY_FILEIDS*/
   $deleteDataStmt = oci_parse($conn, getDeleteDataQuery());
   oci_bind_by_name($deleteDataStmt, ':VS_ID_PK', $selection[$a]);

    if(oci_execute($deleteDataStmt))
     {
       /*THIS PART DOESN'T WORK - deletes the file from archive_files*/

       $query = "delete from ARCHIVE_FILES where AF_FILE_ID_PK = '$deleteFk'";
       $deleteFileStmt = oci_parse($conn, $query);
         if(oci_execute($deleteFileStmt))
{ oci_commit($conn); }
         }
}
return;
}

Any help would be greatly appreciated. Thanks.

NogDog

You should be able to do it all with one query by using a JOIN. I'm not an Oracle user, so the specifics may be a little different (not to mention table/column names 🙂 ):

$query = "
DELETE FROM main_table AS mt
INNER JOIN join_table AS jt ON mt.id = jt.main_id
INNER JOIN image_table AS it ON jt.image_id = it.id
WHERE mt.id = $id_to_be_deleted
";

scopley

Thanks for your response. But somehow I need to loop through the results of the $deleteFk variable and delete all the rows that have that id. Right now it is only deleting the first one.

Not sure how to correct the code to make it loop.

Ashley_Sheridan

As you loop through, print out the queries you think your script is meant to be processing. I'd have a bet that there is some problem with the queries such as malformedness, which is causing them not to be run.

Remember, if in doubt, just output things to see that they're within expected parameters. More often than not, that's all the debugging that's needed to fix most problems!