[SOLVED] MySQL query nightmare

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

[SOLVED] MySQL query nightmare

Post 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.
Last edited by programmermatt on Fri Jun 03, 2005 6:51 pm, edited 2 times in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

can you use != as an operator?

I thought it had to be <>??? 8O
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

ok cool...good to know!

thanks!

Burr
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

Post 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 :)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Glad to help!
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply