I have a need to display user-levels for all possible users(T1) in all possible Pages(T2) whereby the values are stored in another table (T3) like:
UserID, Page, Level(R,E,W,A,O) (Read,Edit,Write,Assistant,Owner)
But I just can't understand how to dynamically get the columns made and then refer to it as LAST(col1) Any hint in the right direction will be appreciated.
I know how to get it done in a number of foreach loops in php but I believe it should be possible in MySql?..
Tables look like:
Table 1; Users
ID | Name
------------------
1 | James
------------------
2 | John
------------------
3 | Peter
------------------
Table 2; Pages
ID | PageName
------------------
1 | Home
------------------
2 | Members
------------------
3 | Courses
------------------
4 | Lybrary
------------------
5 | StrangePage
------------------
6 | Unused Page
------------------
Table 3; UserLevels
ID | User | Page | Lev
---------------------------------------
1 | James | Home | W
---------------------------------------
2 | James | Members | R
---------------------------------------
3 | James | Lybrary | O
---------------------------------------
4 | John | Home | R
---------------------------------------
5 | Peter | Home | O
---------------------------------------
6 | Peter | Courses | A
---------------------------------------
Expected out put would contain all users as rows, all pages as columns, all permissions (where available) as results:
User | Home | Members | Courses | Lybrary | StrangePage | Unused Page
-----------------------------------------------------------------------
James | W | R | | | |
-----------------------------------------------------------------------
John | R | | | | |
-----------------------------------------------------------------------
Peter | O | | A | | |
-----------------------------------------------------------------------