delete image from row that has been expired

Question

Hey I have a php code that checks which rows date got expired and delete them , one of the columns is an image name that was uploaded to the server via ftp, when I delete all expired records I also want to delete the images that is attached to them. I read about the unlink command on php but I don't see how I can apply it to all of the images at once.

I have this code that I just rambled for example cause I don't really know how to do it..

     $sql = "DELETE FROM table WHERE date < NOW()";
 mysqli_query($conn,$sql);

$file = "public_html/images/".//Here should go the image name;
unlink($file);
if($sql)
{
    echo "Records were deleted";
}
?>

Can anyone tell me how can I delete all images that are attached to the deleted rows?

Answer 1

IF you want to delete the rows from the table in your database and at the same time delete the images from the folder on the server where you keep them then you will have to process each delete candidate one at a time, so that you can get the filename from the row to accomplish the file delete at the same time sa deleting the row.

// connect to database

$base_path = 'public_html/images/';
$del_count = 0;

// SELECT all the delete candidates
$sql = "SELECT id,image FROM table WHERE date < NOW()";
$sel4del = mysqli_query($conn,$sql);
if ( ! $sel4del ) {
    echo $sel4del->error;
    exit;
}

// prepare a delete by id
$sql = 'DELETE FROM table WHERE id = ?';
$delrow = mysqli_prepare($conn,$sql);
if ( ! $delrow ) {
    echo $delrow->error;
    exit;
}

// Process all the delete candidates one by one
while ($row = $sel4del->fetch_object() ) {
    // remove the file from disk
    unlink($base_path . $row->image);
    $del_count++;

    // bind the current id to the prepared delete query
    $delrow->bind_param('i', $row->id);    

    // execute the delete of this one row
    $delrow->execute();
}
echo 'images removed = ' . $del_count
?>