LIKE statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
DSM
Forum Contributor
Posts: 101
Joined: Thu May 02, 2002 11:51 am
Location: New Mexico, USA

LIKE statement

Post 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.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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]
DSM
Forum Contributor
Posts: 101
Joined: Thu May 02, 2002 11:51 am
Location: New Mexico, USA

Post by DSM »

mikeq-
thx...
worked like a charm!
:D
Post Reply