[SOLVED] MySQL query nightmare
Posted: Fri Jun 03, 2005 5:31 pm
I have been trying to fix this bug in my code for a month or more now and cannot seem to squash it. Whenever it seems to start working approriatly, the next day it doesn't.
Hope you guys can help
Ok, I have the following tables:
groups
forum_perm_groups:
forum_forums
This is taken a little out of context of the code, but for simplicity's sake and will not affect a working solution.
What we have is a multi-game with CMS program that needs to have groups that are global, CMS-only and GAME-only (ingame= -1, 0, game_id respectively). Now, what I need to be able to do is to select groups out of the groups table that are: global, in the game or CMS (corresponding to wether the forum is in the game or in the CMS), and do not currently have permissions set.
Here is the best query I have so far, but it returns groups that have permissions already set.
It is probably something stupid, but I just cannot see it.
Hope you guys can help
Ok, I have the following tables:
groups
Code: Select all
CREATE TABLE `groups` (
`group_id` tinyint(3) unsigned NOT NULL auto_increment,
`group_name` varchar(25) NOT NULL default '',
`group_desc` varchar(255) NOT NULL default '',
`ingame` tinyint(4) NOT NULL default '-1',
PRIMARY KEY (`group_id`),
KEY `group_name` (`group_name`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
INSERT INTO `groups` VALUES (5, 'Guests', 'Built-in group for handeling permissions of guests. Only applies to CMS as games require login', 0);
INSERT INTO `groups` VALUES (1, 'Users', 'Default user group for new users. It is a superglobal group', -1);
INSERT INTO `groups` VALUES (2, 'Administrators', 'Built-in user group for Administrators. It is a superglobal group', -1);Code: Select all
CREATE TABLE `forum_perm_groups` (
`group_id` tinyint(3) unsigned NOT NULL default '0',
`forum_id` tinyint(3) unsigned NOT NULL default '0',
`perms` text NOT NULL,
KEY `user_id` (`group_id`)
) TYPE=MyISAM;
INSERT INTO `forum_perm_groups` VALUES (2, 2, 'a:14:{s:4:"e;VIEW"e;;s:1:"e;1"e;;s:4:"e;READ"e;;s:1:"e;1"e;;s:4:"e;POST"e;;s:1:"e;1"e;;s:11:"e;POST_STICKY"e;;s:1:"e;0"e;;s:13:"e;POST_ANNOUNCE"e;;s:1:"e;0"e;;s:5:"e;REPLY"e;;s:1:"e;1"e;;s:10:"e;DELETE_OWN"e;;s:1:"e;0"e;;s:8:"e;EDIT_OWN"e;;s:1:"e;0"e;;}');
INSERT INTO `forum_perm_groups` VALUES (1, 2, 'a:13:{s:4:"e;VIEW"e;;s:1:"e;1"e;;s:4:"e;READ"e;;s:1:"e;1"e;;s:4:"e;POST"e;;s:1:"e;1"e;;s:11:"e;POST_STICKY"e;;s:1:"e;0"e;;s:13:"e;POST_ANNOUNCE"e;;s:1:"e;0"e;;s:5:"e;REPLY"e;;s:1:"e;0"e;;s:10:"e;DELETE_OWN"e;;s:1:"e;1"e;;s:8:"e;EDIT_OWN"e;;s:1:"e;0"e;;}');Code: Select all
CREATE TABLE `srbase_forum_forums` (
`forum_id` tinyint(3) unsigned NOT NULL auto_increment,
`cat_id` tinyint(3) unsigned NOT NULL default '0',
`forum_name` varchar(25) NOT NULL default '',
`forum_desc` varchar(255) NOT NULL default '',
`forum_posts` int(11) unsigned NOT NULL default '0',
`forum_order` tinyint(2) unsigned NOT NULL default '0',
`forum_status` tinyint(2) unsigned NOT NULL default '0',
`ingame` tinyint(4) NOT NULL default '0',
`location` tinyint(4) NOT NULL default '1',
PRIMARY KEY (`forum_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
INSERT INTO `forum_forums` VALUES (1, 1, 'Test Forum', 'A test forum', 1, 0, 0, 0, 1);
INSERT INTO `forum_forums` VALUES (2, 0, 'The Academy', 'A place for learned citizens of society.', 1, 0, 0, 1, 2);What we have is a multi-game with CMS program that needs to have groups that are global, CMS-only and GAME-only (ingame= -1, 0, game_id respectively). Now, what I need to be able to do is to select groups out of the groups table that are: global, in the game or CMS (corresponding to wether the forum is in the game or in the CMS), and do not currently have permissions set.
Here is the best query I have so far, but it returns groups that have permissions already set.
Code: Select all
SELECT DISTINCT g.group_id, g.group_name
FROM groups g, forum_forums ff, forum_perm_groups p
WHERE (
g.ingame = '-1'
OR (
g.ingame = ff.ingame
AND ff.forum_id = '2'
)
)
AND (
g.group_id != p.group_id
AND p.forum_id = '2'
)