Loop through a table to fetch all records with a particular ID and delete the corresponding files in a folder only deleting one file in folder in php

Question

I tried to query a table to get all records with a particular ID so as to delete the records from the table as well as unlink the corresponding files from a folder on the server but only the first file is getting deleted while all records are getting deleted as it should be.

Below is my code

$querydpp = "SELECT dpID, dpphoto, fprid FROM projectdrawingplanstbl WHERE fprid=?";
$stmtdpp = $connREMAX->prepare($querydpp);
$stmtdpp->bind_param('i', $fpid);
$stmtdpp->execute();
$stmtdpp->store_result();
$totalRowsdpp = $stmtdpp->num_rows;
$stmtdpp->bind_result($dpID, $dpphoto, $fprid);

for ($i = 0; $i < $totalRowsdpp; $i++) {
            
    $queryd = "SELECT dpphoto FROM projectdrawingplanstbl WHERE fprid=?";
    $stmtd = $connREMAX->prepare($queryd);
    $stmtd->bind_param('i', $fpid);
    $stmtd->execute();
    $stmtd->store_result();
    $stmtd->bind_result($dpphotod);
    $stmtd->fetch();

    if($dpphotod!="")
    {
        unlink($targetd . $dpphotod);
        unlink($thumbtargetd . $dpphotod);
    }           
        
    $stmtpd = $connREMAX->prepare("DELETE FROM projectdrawingplanstbl WHERE fprid=?");
    $stmtpd->bind_param('i', $fpid);
    $results_delpd = $stmtpd->execute();
    $stmtpd->close();
            
}

I was expecting all corresponding files with the ID in the table to be deleted from the folder, but its not. Only one file at a time is getting deleted.

My appologies the first query is what I used to have the files displayed on the page so the person deleting can see what files they are deleting. While the 2nd query in the loop was what I used to get the records to delete.

Answer 1

A few things are wrong:

1. Your second SELECT query is redundant, because your first SELECT already selected all the necessary rows and columns.

2. Your second SELECT is causing a problem, because instead of fetching a new row every time you loop, you would keep fetching the same (first) row again from the query, because you're re-executing that query every time.

3. However that problem never actually happens because within the loop you also DELETE all the records, so the second time the loop runs there is nothing to select anymore.

In other words, the logic needs a bit of a rethink.

Here's a possible approach:

1. Query the records once and loop through them

2. Don't delete anything from the database until all the selected rows have been processed and the files deleted

$querydpp = "SELECT dpID, dpphoto, fprid FROM projectdrawingplanstbl WHERE fprid=?";
$stmtdpp = $connREMAX->prepare($querydpp);
$stmtdpp->bind_param('i', $fpid);
$stmtdpp->execute();
$result = $stmtdpp->get_result();

while ($row = $result->fetch_assoc())
{
    $dpphoto = $row["dpphoto"];

    if($dpphoto != "")
    {
        unlink($targetd . $dpphoto);
        unlink($thumbtargetd . $dpphoto);
    }           
}

$stmtpd = $connREMAX->prepare("DELETE FROM projectdrawingplanstbl WHERE fprid=?");
$stmtpd->bind_param('i', $fpid);
$results_delpd = $stmtpd->execute();