Delete records from mutiple tables + unlink

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Delete records from mutiple tables + unlink

Post 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]
farinspace
Forum Newbie
Posts: 6
Joined: Thu Jan 18, 2007 4:00 pm

Post 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:
Post Reply