Page 1 of 1

Delete records from mutiple tables + unlink

Posted: Thu Jan 18, 2007 10:49 pm
by Jim_Bo
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,

I have 3 tables:

[syntax="sql"]CREATE TABLE product_headings (
  heading_id int(10) NOT NULL auto_increment,
  product varchar(100) NOT NULL default '',
  PRIMARY KEY (heading_id)
) TYPE=MyISAM;

###

CREATE TABLE products (
  product_id int(10) NOT NULL auto_increment,
  filter int(10) NOT NULL default '',
  product_name varchar(100) NOT NULL default '',
  description text NOT NULL,
  current int(10) NOT NULL default '',
  PRIMARY KEY (product_id)
) TYPE=MyISAM;

###

CREATE TABLE product_images (
  photo_id int(10) NOT NULL auto_increment,
  product_id int(10) NOT NULL default '0',
  photo_filename varchar(25) NOT NULL default '0',
  PRIMARY KEY (photo_id)
) TYPE=MyISAM;

Im not sure how to create a relationship between the tables and images, If somone chooses to delete a "product_heading" have it also delete the related "products" from the product table and also unlink the images related to the products, whic is where I am stuck.

The following code is what I am using to delete a product from the "products" table and unlink all the images related to that product:[/syntax]

Code: Select all

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

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

	echo '<br /><center><b>Record was successfully deleted</b></center>';
}else{
	echo '<br /><center><b>Do you really want to delete this record?</b></center>';
	echo '<br /><br /><center><a href="../index.php?action=del_products&product_id='.$_GET['product_id'].'&del_all&confirm=y">Yes</a> - <a href="#" onClick="history.go(-1)">No</a></center>';
}
Thanks.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Jan 19, 2007 1:54 am
by farinspace
you need a relationship between products table and product_headings table.

if one-to-many : a product can belong to only one heading, then you would simply put the heading_id in the products table

however if many-to-many : a product can belong to one or more headings and a heading can belong to one or more products then you will need some kind of linking table such as:

Code: Select all

CREATE TABLE products_link (
  heading_id int(10) NOT NULL,
  product_id int(10) NOT NULL,
) TYPE=MyISAM;
I think thats correct ... :roll: