Deleting records from mutiple tables

PHP
1

Hi,

I have table structures like so…


CREATE TABLE members (
  memberid int(25) NOT NULL auto_increment,
  firstname varchar(55) NOT NULL default '',
  lastname varchar(55) NOT NULL default '',
  PRIMARY KEY (memberid)
) TYPE=MyISAM;

CREATE TABLE reports (
  reportid int(25) NOT NULL auto_increment,
  memberid int(5) NOT NULL default '0',
  PRIMARY KEY (reportid)
) TYPE=MyISAM;

CREATE TABLE photos (
  photoid int(25) NOT NULL auto_increment,
  reportid int(5) NOT NULL default '0',
  photoname varchar(55) NOT NULL default '',
  PRIMARY KEY (photoid)
) TYPE=MyISAM;

I want to delete all member, reports and photos from the specific member…

The member record and report records are easy but am a bit lost on best way to pick up on the photos that correspond to each report being deleted… Should I be using a query and join to grab the corresponding photos for unlink?

Cheers

2

the easiest way to handle the deletions is to let the database do it for you, using the ON DELETE CASCADE option of foreign keys

that way, you delete the member, and the member’s related reports and photos are deleted automatically

if you don’t use foreign keys, you would use a multi-table delete, which is simply a DELETE statement where the rows of the related tables are properly joined – see http://dev.mysql.com/doc/refman/5.0/en/delete.html for the syntax

3

Not really getting my head around this… trying to delete a member, all the members reports and unlink all the images linked to the reports…

Also how would I structure the tables to use foreign keys in the future?

Thanks

4

delete a member, and all his reports, and unlink the photos? you want to leave the photos behind instead of deleting them?

that will require custom php code in your application

as for structuring the tables to use foreign keys, any competent sql tutorial can teach you that

:slight_smile:

5

Ok I have managed to sort it uisng 2 sql joins… The first to collect all the photos id’s and unlink the photos… the 2nd to select all records in the 3 tables and delete them… What a mission that was…

I will have a look for tutroials that may help… Do you know of any easy to follow ones off hand?

Thanks