Deleting Multiple Rows and Files

lulubell

I have categories stored in a db - galleryCategory_db and then images stored in gallery_db. When an image is uploaded via a web browser, the id, category, header and link to image are stored in gallery_db. The file gets uploaded to the server - ../gallery/

That all works great, what I'm trying to do is when I delete a category, it looks up all of the images with that category, then unlink() the file on the server, delete the entry in gallery_db, then delete the category in galleryCategory_db.

I'm using the code below, but it will only delete one file from the server and gallery_db.

I've tried replacing the return $result; bit, but then get mysql_fetch_array errors

Can anyone help in the right direction?

Please.....

LoganK

function deleteCategory($category)
{
     //Delete the category
     $SQL = "DELETE from galleryCategory_db WHERE category='$category'";
     $result=mysql_query($SQL) or die("ERROR - $SQL");
     //DELETE All Gallery Images if category=$category
     $SQL = "SELECT id, category, downfile from gallery_db WHERE category='$category'";
     $result=mysql_query($SQL) or die("ERROR - $SQL");
     $numrows=mysql_num_rows($result);
     while ($myrow = mysql_fetch_array($result))
     {
          $id=$myrow["id"];
          $category=$myrow["category"];
          $downfile=$myrow["downfile"];
          if ($result)
          {
               if (!unlink($downfile)) die ("Can't delete the file $downfile");
               $SQL = "DELETE from gallery_db WHERE id='$id'";
               $result=mysql_query($SQL) or die("ERROR - $SQL");
               return $result;
          }
     }
}

Please put the code in [ PHP ] tags next time!!!

Edit: After taking a look at it, nothing seems wrong. Except that you may want to remove the "return $result", because that ends your function prematurely!!

devinemke

a slightly different version of LoganK's function with fewer queries:

function deleteCategory($category)
{
	mysql_query("DELETE from galleryCategory_db WHERE category = '$category'") or exit(mysql_error());
	$result = mysql_query("SELECT id, downfile from gallery_db WHERE category = '$category'") or exit(mysql_error());
	while ($row = mysql_fetch_assoc($result))
	{ 
		unlink('/path/to/images/' . $downfile) or exit('error deleting ' . $row['downfile']);
		$ids[] = $row['id'];
	}
	mysql_query('DELETE FROM gallery_db WHERE id IN(' . implode(',', $ids) . ')') or exit(mysql_error());	
}

laserlight

The main problem I see is with atomicity - what happens if your DELETE fails midway? If you're using transactions you might be able to rollback, but you cant rollback the unlink().

Anyway, there is no need to consider gallery_db.id methinks:

function deleteCategory($category) {
	$result = mysql_query("SELECT downfile from gallery_db WHERE category='$category'")
		OR die(mysql_error());
	while ($row = mysql_fetch_assoc($result)) {
		unlink('/path/to/images/' . $row['downfile'])
			OR die('Error deleting ' . $row['downfile']);
	}
	mysql_query("DELETE FROM gallery_db WHERE category='$category'")
		OR die(mysql_error());
	mysql_query("DELETE FROM galleryCategory_db WHERE category='$category'")
		OR die(mysql_error());
}

LoganK

devinemike - if you noticed, that's not my code at all. That's just the original posters code, cleaned up and enclosed within [ PHP ] tags, where it belongs! 😉

laserlight - good point. What if the delete fails midway? Then what's gonna happen?

lulubell

Sorry about not putting it in [ php ] tags... I'm quite new to this and the forum therefore didn't realise...

My worry is also if things fail midway, but I just don't know what the best of doing things are?

I know that I can't get back the file if I unlink it, but then the record doesn't delete from the db, but if I delete it from the db first, then I won't be able to do the unlink ??

laserlight

The problem with your original code snippet is that you delete the category first, and then attempt to delete the records from that category. If the record deletion or file deletion fails, then you have orphaned records - records belonging to a category that no longer exists.

devinemke's version has that same problem, but it introduces the idea of deleting only those records which had their corresponding files successfully deleted. This is inefficient compared to my method, assuming that there are no file deletion errors.

My method attempts to delete the files first, and then the records of the given category, and then the category itself. This avoids orphaned records, but introduces another problem - if there's an error with file deletion, then one will have records that point to non-existent files.

My reasoning for a solution is this: we want to delete as many records as possible, but at the same time ensure that for every record deleted, there is one (and only one) file deleted. If any file/record is not deleted, we do not want to delete the category.

function deleteCategory($category) {
	$result = mysql_query("SELECT id, downfile from gallery_db WHERE category='$category'")
		OR die(mysql_error());
	$unlink_error = false;
	if (mysql_num_rows($result) > 0) {
		$del_ids = array();
		while ($row = mysql_fetch_assoc($result)) {
			if (unlink('/path/to/images/' . $row['downfile'])) {
				$del_ids[] = $row['id'];
			} else {
				$unlink_error = true;
			}
		}
		mysql_query("DELETE FROM gallery_db WHERE id IN (" . implode(',', $del_ids) . ")")
			OR die(mysql_error());
	}
	if (!$unlink_error()) {
		mysql_query("DELETE FROM galleryCategory_db WHERE category='$category'")
			OR die(mysql_error());
	}
}

LoganK

I like the way laserlight is thinking... 🙂

lulubell

Thanks Laserlight...

It worked although had to remove the () from

if (!$unlink_error()) {

I wanted also to let the user know that the category had been deleted, I have a reponse

if ($result) {
$error_msg = "The Gallery Item could not be deleted";
}
else {
$thanks_msg = "The Gallery Item has been deleted";
}

I used to do this with return $result, what would I return it with now?

LoganK

You can still return $result, right in this if clause:

    if (!$unlink_error()) {
        mysql_query("DELETE FROM galleryCategory_db WHERE category='$category'")
            OR die(mysql_error()); // If successfull, the category was deleted
        return 1; // If everything gets to here, the category and all files were deleted! Yay!
    } 
    return 0; // If it gets to here - something's wrong!

And now you can use something like:

if (!deleteCategory("test"))
     die("Could not delete!");
else
     echo "Category test deleted!";