Page 1 of 1

Delete records from 3 linked tables

Posted: Wed Aug 30, 2006 12:37 am
by bob_the _builder
Hi,

Im abit lost as to how I can remove the images in this system

3 table ..

maincat = table 1
subcat = table 2
images = table 3

subcat table holds field maincat_id
images table holds subcat_id

used to cross reference them

Now I cant thinkup a way to unlink all the images related to the maincat_id

Here is code what I have so far:

Code: Select all

case 'delete_maincat':

	if (array_key_exists("confirm",$_REQUEST)) {

		echo '<br />Sub category & images were successfully deleted<br /><br /><br />Please wait while you are redirected ...
		<meta http-equiv="refresh" content="3; url='.$_GET['referer'].'">';

		$sql = mysql_query("SELECT photo_filename FROM gallery_images WHERE subcat_id='".$_GET['subcat_id']."'");
		while ($row = @mysql_fetch_array($sql)) {
   			unlink($images_dir . '/' . $row[0]);
   			unlink($images_dir . '/tb_' . $row[0]);
}
		$sql = mysql_query("DELETE FROM gallery_maincat WHERE maincat_id='".$_GET['maincat_id']."'");
	    $sql = mysql_query("DELETE FROM gallery_subcat WHERE maincat_id='".$_GET['maincat_id']."'");
}else{
		echo 'Do you really want to delete this category and all its images?';
		echo '<br /><br /><a href="index.php?action=process&process=delete_subcat&subcat_id='.$_GET['subcat_id'].'&confirm=y">Yes</a> - <a href="#" onClick="history.go(-1)">No</a>';
}
 
break;
This is the piece of code im not sure how to link the images to the maincat being deleted .. Any ideas?

Code: Select all

$sql = mysql_query("SELECT photo_filename FROM gallery_images WHERE subcat_id='".$_GET['subcat_id']."'"); 
                while ($row = @mysql_fetch_array($sql)) { 
                  unlink($images_dir . '/' . $row[0]); 
                  unlink($images_dir . '/tb_' . $row[0]); 
}

Maybe a table join is in order?

Thanks

Posted: Wed Aug 30, 2006 3:26 am
by CoderGoblin
Have you though of having "Foreign keys/Constraints". That way your database will always be in sync without relying on coding (what happens if you delete it using SQL and forget the links etc).

Other than that Postgres allows you to use an EXISTS condition where you can insert a "check". I'm sure MySql has the same.This may be something like:

Code: Select all

DELETE  FROM images as chk WHERE EXISTS(SELECT 1 FROM maincat,subcat,images  WHERE image_id = chk.image_id AND subcat.maincat_id=maincat.maincat_id AND subcat.subcat_id=images.subcat_id);
You would need to modify and test this obviously (preferably on a temporary copy of the tables). This would be performed prior to the subcat deletion and main cat deletion. After all deletions would need to be performed backwards.

I would also do it as a transaction so if any part fails the deletion fails and everything is rolled back to how it was before and sysadmin notified.

Posted: Wed Aug 30, 2006 3:57 am
by bob_the _builder
Hi,

Not really following what you have typed at all sorry ..

never used such code .. Does that mean another part has to be added to all my tables to help the reference?


Thanks

Posted: Wed Aug 30, 2006 4:18 am
by Jenk
If you can SELECT it, you can DELETE it.

Posted: Wed Aug 30, 2006 9:56 am
by CoderGoblin
MySQL FOREIGN KEY Constraints.

Do not have to be used (they can add overhead) but can be useful to maintain database integrity.

Other than that the SELECT I previously showed is an example of how to delete the images. The images get deleted, then you need to delete the relevant subcat, then the maincat. This is three delete statements in REVERSE order. If you don't do it in this order you delete the maincat and you lose the reference to the subcats (ok you have stored the maincat in a variable so you can get the subcats). Deleting the subcats next means that you lose the image links with no way of getting them back unless you store each subcat_id prior to deleting them.