here's one...
Working on a project where the admin wants to be able to upload documents, but limit who can view which docs.
Here's my thinking, and this is why I put this in db forum.
Users have individual id numbers.
I want to store the docs in the db, give them a doc_id and set who can view them in a user_id column. When a user logs in, the sql SELECT gets the documents he is authorized to view and lists them as links. OK, no prob until we got to multiple user_ids for a document, do I use "LIKE" when I do the sql SELECT as in "WHERE user_id LIKE '$user_id'", is "LIKE" able to roll thru an array and pickup on a matching user_id within the array?
If anyone has a better idea on how to control auth per document, lemme know. I am open to suggestions.
LIKE statement
Moderator: General Moderators
You might be able to split the user id. Like this
User Id has to be stored in the db like this "1,5,7,3" etc
Code: Select all
$sql = "SELECT user_id FROM WHATEVER WHERE doc_id = '$doc_id'";
$temp = mysql_query($sql);
list($result) = mysql_fetch_array($temp);
$users = explode(",",$result);
$num = count($users);
$i = 0;
while($users != $num)
{
if($user_id == $usersї$i])
{
echo "User can view this doc";
} else {
echo "User can't";
}
}Don't store things like 1,3,4,2 in one field representing all users that are allowed to view a particular publication, it is bad database design and will cause many problems like the one you are experiencing now. Also can you imagine if an employee is suddenly not allowed to see any publication, how would you write a SQL statement to update the records.
What you have is a many to many relationship between user and document so you need to break this down into 2 1 to many relationships with a linking table
USERTABLE
user_id|firstname|surname
DOCUMENTTABLE
doc_id|doc_title|doc_description
USER_DOCUMENT_TABLE (this is the linking table)
user_id|doc_id
so you might have data like
USERTABLE
1|Mike|Quinn
2|Another|User
3|AN|Other
DOCUMENTTABLE
1|Database Design|Complete guide
2|MySQL for Beginners|blah blah
3|PHP Bible|The best book around
and your USER_DOCUMENT_TABLE might look like this
1|1
1|2
1|3
2|3
3|1
3|2
A query like this
Select U.firstname, U.surname, D.doc_title
from USERTABLE U, DOCUMENTTABLE D, USER_DOCUMENT_TABLE UD
where
U.user_id = UD.user_id and
UD.doc_id = D.doc_id and
U.user_id = '1';
would produce
Mike|Quinn|Database Design
Mike|Quinn|MySQL for Beginners
Mike|Quinn|PHP Bible
So now things like updates,deletes etc become easy, if you want to stop Mike Quinn seeing PHP Bible
delete from USER_DOCUMENT_TABLE
where user_id = '1' and doc_id = '3';
If you want to stop Mike Quinn seeing any documents
delete from USER_DOCUMENT_TABLE
where user_id = '1';
If you want to know how many users can see Database Design
select count(*) NumberUsers
from USER_DOCUMENT_TABLE UD, DOCUMENTTABLE D
where UD.doc_id = D.doc_id and
D.doc_title = 'Database Design';
would return
NumberUsers
2
Hope this helps, it is worth getting it correct now as it will save you a lot of hassle later.
by the way
USER_DOCUMENT_TABLE UD is just creating an alias for that tablename, you can use USER_DOCUMENT_TABLE as UD, but the as word is not actually required. Same with the NumberUsers alias I used for the count(*) column.
[/i]
What you have is a many to many relationship between user and document so you need to break this down into 2 1 to many relationships with a linking table
USERTABLE
user_id|firstname|surname
DOCUMENTTABLE
doc_id|doc_title|doc_description
USER_DOCUMENT_TABLE (this is the linking table)
user_id|doc_id
so you might have data like
USERTABLE
1|Mike|Quinn
2|Another|User
3|AN|Other
DOCUMENTTABLE
1|Database Design|Complete guide
2|MySQL for Beginners|blah blah
3|PHP Bible|The best book around
and your USER_DOCUMENT_TABLE might look like this
1|1
1|2
1|3
2|3
3|1
3|2
A query like this
Select U.firstname, U.surname, D.doc_title
from USERTABLE U, DOCUMENTTABLE D, USER_DOCUMENT_TABLE UD
where
U.user_id = UD.user_id and
UD.doc_id = D.doc_id and
U.user_id = '1';
would produce
Mike|Quinn|Database Design
Mike|Quinn|MySQL for Beginners
Mike|Quinn|PHP Bible
So now things like updates,deletes etc become easy, if you want to stop Mike Quinn seeing PHP Bible
delete from USER_DOCUMENT_TABLE
where user_id = '1' and doc_id = '3';
If you want to stop Mike Quinn seeing any documents
delete from USER_DOCUMENT_TABLE
where user_id = '1';
If you want to know how many users can see Database Design
select count(*) NumberUsers
from USER_DOCUMENT_TABLE UD, DOCUMENTTABLE D
where UD.doc_id = D.doc_id and
D.doc_title = 'Database Design';
would return
NumberUsers
2
Hope this helps, it is worth getting it correct now as it will save you a lot of hassle later.
by the way
USER_DOCUMENT_TABLE UD is just creating an alias for that tablename, you can use USER_DOCUMENT_TABLE as UD, but the as word is not actually required. Same with the NumberUsers alias I used for the count(*) column.
[/i]