homepage Welcome to WebmasterWorld Guest from 54.197.111.87
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Querying Multiple Tables
Setting User/Admin Levels for various tasks
mvaz




msg:4676193
 9:24 pm on May 30, 2014 (gmt 0)

Hello there,
I am trying to set up a small Admin Panel for my local community website. We have a group of members who have volunteered to do certain updates on the site, so the office has decided to provide them with their own log-in credentials and various pages depending on their roles.

The database schema I have thought for this purpose is as below:

table1 - admin_id, admin_password, etc.
table2 - page_id, page_description
table3 - admin_id, page_id

Say I have admin_1, admin_2 and admin_3 in table1 and
say I have page_id 1 as edit, page_id 2 as approve, page_id 3 as send_email, page_id 4 as post_article in table2

Now my table3 has the following:
admin_id page_id 
1 1
1 3
1 4
2 2
2 4
3 1
3 3
I have a login script that asks the admin to log in. Once login credentials are verified, it then queries table3 to list all pages that are associated with this admin id.

So for example it was admin_id 1 that logged in, the result of the above query would be page_id 1,3 and 4.

Now I am stuck at this point. I would like to retrieve pages 1,3 and 4 from table2 (which holds the page details as said above). I have a feeling that this is achieved through joins, I am not quite sure how to go about it.

Could the experts in here give me some sort of guidance/hints/tips so I can extract the data based on the above.

Your assistance is highly appreciated.

Thanks

Melwyn

 

LifeinAsia




msg:4676197
 9:40 pm on May 30, 2014 (gmt 0)

Yup- JOINs are your friend! :) You want to use a JOIN on the field (or fields) that relate the tables together.

I can tell you the DB side of things. I'll let you (or someone with more PHP experience) fill in the PHP part.

SELECT t2.page_id, t2.page_description
FROM table3 AS t3 INNER JOIN table2 AS t2 ON t3.page_id=t2.page_id
WHERE t3.admin_id=[current admin_id goes here]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved