Jump to content


Photo

SQL query help


  • Please log in to reply
6 replies to this topic

#1 esport

esport
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts

Posted 25 July 2006 - 12:42 AM

Hey, need some help...
I have 3 tables:
1. users (userID, user_level)
2. categories (catID)
3. permissions (catID, userID)

I want to be able to view all values in the catergories table for a particular userID where it is set in the permissions table. However if the user_level is set to 1 in the users table, they may not exist in the permissions table, but i still want to view all categories. How do i go about this query.

Thanks

Daniel

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 July 2006 - 01:12 AM

Post an example of the data in the tables and an example of the output you want.

Also post the version of MYSQL you're using. If you don't know use the following query
SELECT VERSION()


#3 esport

esport
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts

Posted 25 July 2006 - 04:23 AM

Thanks for the reply, i think its an easy one...

Example table.

users (userID, name, user_level)
(user1, Daniel, 1)
(user2, Simon,0)

categories(catID,name)
(1, Office)
(2, Rules)
(3, Procedures)
(4, Gallery)

permissions(username,catID,permission)
(user2,2,1)
(user2,4,1)

The idea is when the user logs in, the categories are displayed but are determined weather it has been set in the permissons table and the if ther user_level is equal to 1. If the user level is set to 0, the only categories displayed are the ones set in the permissions table.

Im trying to write a query that will incorporate all that into it. I have done a basic join but because a user has its user_level to 1 and is not in the permissions table, the query doesnt produce a result.

Im using version 4.0.27

Thanks heaps

Daniel

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 July 2006 - 06:52 AM

I've come up with this query as an exercise only. I wouldn't recommend using it. It would most likely be slower than performing 2 queries where you handled the logic in PHP.
SELECT
c.*
FROM 
users AS u
LEFT JOIN
permissions AS p
ON
IF(u.user_level=1,0,p.userID=u.userID)
LEFT JOIN
categories AS c
ON
IF(u.user_level=1,1,c.catID=p.catID)
WHERE u.userID=$userID;

I'd recommend you do the following in PHP
SELECT user_level FROM users WHERE userID = $userID
if (user_level == 1)
{
    SELECT c.* FROM categories;
}
else
{
    SELECT
    c.*
    FROM 
    permissions AS p
    INNER JOIN
    categories AS c
    ON c.catID=p.catID
    WHERE p.userID=$userID;
   
}


#5 esport

esport
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts

Posted 25 July 2006 - 08:15 AM

Hey thanks fpr the reply.
I tried it but i keep getting a mysql error when using the if statement. ??

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (user_level == 1) { SELECT c.* FROM c...

Thanks

Daniel

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 July 2006 - 01:32 PM

Hey thanks fpr the reply.
I tried it but i keep getting a mysql error when using the if statement. ??

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (user_level == 1) { SELECT c.* FROM c...

Thanks

Daniel


The second snippet I posted was a guide on how you could do this programmatically, not a query.

Retrieve the user_level for the user based on their userID

if their user_level is the same as 1
{
    Retrieve all the categories from the categories table
}
else
{
    retrieve the categories that have an entry in the permissions table for the user
}


#7 esport

esport
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts

Posted 26 July 2006 - 12:32 AM

Thanks heaps,
yeh i thought the second snippet was for PHP use, however i have managed to successfully use your first snippet and it works great.

Thanks for your help, much appreciated.

Daniel




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users