Trouble with a multi-table delete query

[hdshngout]

Hello. Recently, I have been working on an advanced group feature for a website I contribute to.  In order for this feature to work correctly, this immense number of tables were required. 

 

My problem arises when I create a query to delete a group.  I used to just query each table one by one until the record was erased, however this proved to be somewhat heavy on resources.  As per the suggestion of a co-developer and a bit of reading at the mysql manual, I discovered in theory, I could combine the "would-be" seven queries into one.

 

Upon reading, I believe it can be done via a pseudo-join for lack of a better word.  I attempted using a resource from another thread (http://www.phpfreaks.com/forums/index.php/topic,101350.0.html) and the query sort-of worked.  However, it would only delete from `groups` nothing else.

 

Below is a watered down example of the database structure and my "attempted" delete query.  I am aware its complex and I don't want to make it seem like I'm trying to get someone else to do my work for me, its just I am rather stuck.  Any help would be greatly appreciated.

 

CREATE TABLE `groups` (

  `id` int(20) NOT NULL auto_increment,

  `title` varchar(255) collate latin1_general_ci NOT NULL,

  `description` longtext collate latin1_general_ci NOT NULL,

  `category` int(20) NOT NULL,

  `url` varchar(255) collate latin1_general_ci NOT NULL,

  `private` varchar(3) collate latin1_general_ci NOT NULL default 'no',

  `disableDemo` varchar(3) collate latin1_general_ci NOT NULL default 'no',

  `owner` int(20) NOT NULL,

  `timestamp` int(20) NOT NULL,

  `agree` int(2) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

CREATE TABLE `groups_calendar` (

  `id` int(20) NOT NULL auto_increment,

  `group_id` int(20) NOT NULL,

  `timestamp` int(20) NOT NULL,

  `title` varchar(255) NOT NULL,

  `description` varchar(255) NOT NULL,

  `type` int(2) NOT NULL default '0',

  `poster` int(20) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

 

 

 

 

CREATE TABLE `groups_memberships` (

  `id` int(20) NOT NULL auto_increment,

  `userid` int(20) NOT NULL,

  `group_id` int(20) NOT NULL,

  `joined` int(20) NOT NULL,

  `level` int(10) NOT NULL,

  `active` int(3) NOT NULL,

  `on_council` int(20) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

 

 

CREATE TABLE `groups_message_boards` (

  `id` int(20) NOT NULL auto_increment,

  `group_id` int(20) NOT NULL,

  `name` varchar(255) collate latin1_general_ci NOT NULL,

  `description` varchar(255) collate latin1_general_ci NOT NULL,

  `image` varchar(255) collate latin1_general_ci NOT NULL,

  `timestamp` int(20) NOT NULL,

  `min_group_level` int(2) NOT NULL default '0',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

 

CREATE TABLE `groups_message_boards_posts` (

  `id` int(20) NOT NULL auto_increment,

  `group_id` int(20) NOT NULL,

  `board_id` int(20) NOT NULL,

  `thread_id` int(20) NOT NULL,

  `poster` int(20) NOT NULL,

  `post` longtext collate latin1_general_ci NOT NULL,

  `timestamp` int(20) NOT NULL,

  `deleted` int(2) NOT NULL default '0',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

 

CREATE TABLE `groups_message_boards_threads` (

  `id` int(20) NOT NULL auto_increment,

  `group_id` int(20) NOT NULL,

  `title` varchar(255) collate latin1_general_ci NOT NULL,

  `board_id` int(20) NOT NULL,

  `starter` int(20) NOT NULL,

  `min_group_level` int(2) NOT NULL default '0',

  `timestamp` int(20) NOT NULL,

  `closed` int(2) NOT NULL default '0',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

CREATE TABLE `groups_pages` (

  `id` int(20) NOT NULL auto_increment,

  `type` varchar(255) NOT NULL default 'page',

  `title` varchar(255) NOT NULL,

  `content` longtext NOT NULL,

  `timestamp` int(20) NOT NULL,

  `group_id` int(20) NOT NULL,

  `url` varchar(255) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

 

CREATE TABLE `groups_styles` (

  `id` int(20) NOT NULL auto_increment,

  `group_id` int(20) NOT NULL,

  `style` longtext collate latin1_general_ci NOT NULL,

  `xhtml` longtext collate latin1_general_ci NOT NULL,

  `timestamp` int(20) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

 

As per (http://www.phpfreaks.com/forums/index.php/topic,101350.0.html), I attempted to modify the answer he or she received.  Below is the mess which resulted

 

DELETE groups, groups_calendar, groups_memberships, groups_message_boards, groups_message_boards_posts, groups_message_boards_threads, groups_pages, groups_styles FROM groups LEFT JOIN groups_calendar ON groups.id=groups_calendar.group_id  LEFT JOIN groups_memberships ON groups_calendar.group_id=groups_memberships.group_id LEFT JOIN groups_message_boards ON groups_memberships.group_id = groups_message_boards.group_id LEFT JOIN groups_message_boards_posts ON groups_message_boards.group_id=groups_message_boards_posts.group_id LEFT JOIN groups_message_boards_threads ON groups_message_boards_posts.group_id=groups_message_boards_threads.group_id LEFT JOIN groups_pages ON groups_message_boards_threads.group_id=groups_pages.group_id LEFT JOIN groups_styles ON groups_pages.group_id=groups_styles.group_id WHERE groups.id = ?

 

 

 

--------------------Other Information-------------------------

 

MySQL Version 5.0

 

What's happening: Only deleting the information from `groups,` nothing else.

 

What should happen: All information linked with the `group_id` being deleted should be erased.

 

What I've Tried: I've tried to write the query multiple times, using the examples from mysql.com, and other threads on this forum.

 

 

 

---------------------------------------------------------------

 

 

 

 

 

 

I apologize for this post ending up as long as it did. and thank you for your time in reading my post.

 

-Nick

 

 

[fenway]

DELETE doesn't work that way... depending on how you write it, it can delete all matching records from the tables you specify.