Page 1 of 1

LIKE statement

Posted: Wed Aug 21, 2002 5:27 pm
by DSM
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.

Posted: Thu Aug 22, 2002 1:44 am
by Takuma
You might be able to split the user id. Like this

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";
  }
}
User Id has to be stored in the db like this "1,5,7,3" etc

Posted: Thu Aug 22, 2002 4:39 am
by mikeq
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]

Posted: Thu Aug 22, 2002 7:03 am
by DSM
mikeq-
thx...
worked like a charm!
:D