CODEWITHSUNDEEP
phpmysqlsql
Googlebot
Googlebot

Reputation: 15683

Mysql query to check access to a private page

Consider a group table and member-group relationship table as

CREATE TABLE group (
group_id int(11) not null auto_increment,
title varchar(50),
status ENUM('private', 'public'),
PRIMARY KEY(group_id)
);

CREATE TABLE group_map (
group_map_id int(11) not null auto_increment,
group_id int(11) REFERENCES group(group_id),
user_id int(11) REFERENCES user(user_id),
PRIMARY KEY(group_map_id)
);

Now in the group page, how can I show the content if

1. Group is public

OR

2. user is member of that group (user_id comes from $_SESSION login, 
and we check if the current group_id && user_id exists in group_map table).

Upvotes: 1

Views: 171

Answers (3)

Query Master
Query Master

Reputation: 7097

You should try this... 

SELECT g.*,gm.* FROM group g INNER JOIN group_map gm ON g.`group_id` = gm.`group_id`

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53880

Here's how to retrieve a list of all groups that user 1 can access, complete with group name:

SELECT g.group_id, g.name
FROM `group` g
LEFT JOIN group_map gm
  ON gm.group_id = g.group_id
WHERE g.status = 'public'
  OR gm.user_id = 1

Here's an alternative, using UNION.

(SELECT g.group_id, g.name
FROM `group` g
WHERE g.status = 'public')
UNION
(SELECT g.group_id, g.name
FROM `group` g
JOIN group_map gm
  ON gm.group_id = g.group_id
WHERE gm.user_id = 1)

They them both out for size.

Notice the backticks around the group tablename because GROUP is a reserved word in MySQL.

Upvotes: 1

qpaycm
qpaycm

Reputation: 930

First, if I understand right what you want, you don't need 2-nd table. You can use Users table.

If group is private I was doin it that way

SELECT tblGroups.*, tblUsers.* 
FROM `tblGroups`, `tblUsers` 
WHERE tblGroups.id=tblUsers.group AND // here you put what you need to open //

otherwise group is public just this

SELECT tblGroups.* 
FROM `tblGroups` 
WHERE // here you put what you need to open //

Upvotes: 0

Related Questions