hdshngout Posted January 26, 2008 Share Posted January 26, 2008 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 Link to comment https://forums.phpfreaks.com/topic/87871-trouble-with-a-multi-table-delete-query/ Share on other sites More sharing options...
fenway Posted January 26, 2008 Share Posted January 26, 2008 DELETE doesn't work that way... depending on how you write it, it can delete all matching records from the tables you specify. Link to comment https://forums.phpfreaks.com/topic/87871-trouble-with-a-multi-table-delete-query/#findComment-450122 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.