Page 1 of 1

[SOLVED] MySQL query nightmare

Posted: Fri Jun 03, 2005 5:31 pm
by programmermatt
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

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);
forum_perm_groups:

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:&quote;VIEW&quote;;s:1:&quote;1&quote;;s:4:&quote;READ&quote;;s:1:&quote;1&quote;;s:4:&quote;POST&quote;;s:1:&quote;1&quote;;s:11:&quote;POST_STICKY&quote;;s:1:&quote;0&quote;;s:13:&quote;POST_ANNOUNCE&quote;;s:1:&quote;0&quote;;s:5:&quote;REPLY&quote;;s:1:&quote;1&quote;;s:10:&quote;DELETE_OWN&quote;;s:1:&quote;0&quote;;s:8:&quote;EDIT_OWN&quote;;s:1:&quote;0&quote;;}');
INSERT INTO `forum_perm_groups` VALUES (1, 2, 'a:13:{s:4:&quote;VIEW&quote;;s:1:&quote;1&quote;;s:4:&quote;READ&quote;;s:1:&quote;1&quote;;s:4:&quote;POST&quote;;s:1:&quote;1&quote;;s:11:&quote;POST_STICKY&quote;;s:1:&quote;0&quote;;s:13:&quote;POST_ANNOUNCE&quote;;s:1:&quote;0&quote;;s:5:&quote;REPLY&quote;;s:1:&quote;0&quote;;s:10:&quote;DELETE_OWN&quote;;s:1:&quote;1&quote;;s:8:&quote;EDIT_OWN&quote;;s:1:&quote;0&quote;;}');
forum_forums

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);
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.

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'
)
It is probably something stupid, but I just cannot see it.

Posted: Fri Jun 03, 2005 5:55 pm
by pickle

Code: Select all

SELECT
 distinct(g.group_id),
 g.group_name
FROM
 groups as g,
 forum_forums as ff,
 forum_per_groups as p
WHERE
 (g.ingame='01' OR
  (g.ingame==ff.ingame AND
   ff.forum_id = '2'))
 AND
 (g.gropu_id != p.group_id AND
  forum_id = '2')

Code: Select all

AND
 (p.perms != '')
You've got your table setup so perms can't be NULL. What value goes in instead of NULL? Just modify my addition to put that string in quotes, and it should work.

Posted: Fri Jun 03, 2005 6:01 pm
by Burrito
can you use != as an operator?

I thought it had to be <>??? 8O

Posted: Fri Jun 03, 2005 6:05 pm
by pickle
Burrito wrote:can you use != as an operator?

I thought it had to be <>??? 8O
I use != all the time. Seems to work fine.

Posted: Fri Jun 03, 2005 6:06 pm
by Burrito
ok cool...good to know!

thanks!

Burr

Posted: Fri Jun 03, 2005 6:21 pm
by programmermatt
Thanks for pointing out the NOT NULL without default, but sadly your solution didn't work, it still returns users that have permissions in the permissions table.

EDIT:
Nevermind. There was a problem with the code that handles the query results. Your solution works wonderfully. I thank you very much for your help; you have helped me to squash a long standing bug in my permission system :)

Posted: Mon Jun 06, 2005 1:09 pm
by pickle
Glad to help!