Joining records based on the relations

[x1nick]

Im making a simple(ish) user auth system.

Where modules have either 2 levels of admin access (some only have 1)

 

This is the table that stores the permission types

 

CREATE TABLE IF NOT EXISTS `sys_modulepermission` (
  `sysperid` int(10) NOT NULL AUTO_INCREMENT,
  `moduleid` int(10) NOT NULL,
  `module` varchar(50) NOT NULL,
  `allowadmin` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sysperid`)
) 

 

Some sample data

INSERT INTO `sys_modulepermission` (`sysperid`, `moduleid`, `module`, `allowadmin`) VALUES
(1, 4, 'php', 0),
(2, 5, 'tpl', 0),
(3, 5, 'tpl_admin', 0),
(4, 7, 'users', 0),
(5, 7, 'users_admin', 0),
(6, 9, 'content', 0),
(7, 10, 'news', 0);

 

You will notice how tpl and tpl_admin, these are the same module. Same applies to users and users_admin but the others are 1 level of admin only.

 

Also this is the table sys_module which basically stores the different modules

CREATE TABLE IF NOT EXISTS `sys_module` (
  `moduleid` int(10) NOT NULL AUTO_INCREMENT,
  `modulename` varchar(100) NOT NULL,
  `moduleold` varchar(50) NOT NULL,
  `dirfolder` varchar(100) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `static` tinyint(1) NOT NULL,
  PRIMARY KEY (`moduleid`),
  UNIQUE KEY `moduleid` (`moduleid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `sys_module`
--

INSERT INTO `sys_module` (`moduleid`, `modulename`, `moduleold`, `dirfolder`, `active`, `static`) VALUES
(4, 'System', 'php', '/', 1, 1),
(5, 'Templates', 'tpl', '/', 1, 1),
(7, 'Users', 'users', '/', 1, 1),
(9, 'Content', 'content', 'content', 1, 1),
(10, 'News', 'news', 'news/', 1, 0);

 

So my current mysql statement is

select * from `sys_modulepermission` 
join `sys_module`
on `sys_modulepermission`.`moduleid` = `sys_module`.`moduleid`

 

But what I haven't got a clue how to do is get a result like this where when I run this statment through a while loop I get each module listed with its relevant permission values

 

First column is modulename from sys_module

Second column is module from sys_modulepermission

Third column is also module from sys_modulepermission but from a different row

 

System - php

Templates - tpl - tpl_admin

Users - users - users_admin

Content - content

News - news

 

Does this make sense? is it at all possible?

[x1nick]

Forgot to mention, my reasoning for having the permission types in a separate table is in the future sometime I will likely introduce level 3, 4 and so on depending on what needs doing.

 

 

[fenway]

I don't follow.