SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting records from mutiple tables

    Hi,

    I have table structures like so..

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    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..
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •