adding multiple values to a single field

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
axltrauts
Forum Newbie
Posts: 1
Joined: Thu May 20, 2004 5:50 pm

adding multiple values to a single field

Post by axltrauts »

hello, i´m new to this forums and somewhat amateur to php and i love it

but i have a problem.. serious problem. I have a database called db1, it is used to record users, their data and one of this data is a field used to store wich applicatiosn this user can run.

Let´s say John has the right to run Excel, Word and Access, and not the rest.

I have two tables, one is called User and the other table of the application list is in the table applist

What i´m thinking, is to have the field applright (for the application permissions) inside he Table Users. this field will have two fields, applid and applname.

I will have a form where i can choose form a multiple selection list all the applications the user can have the rights.

What i dont know is how to store this, because all the values form application tables will be stored in a single field, Users. can it be done? is there a better way to keep a record of so many values in a sigle field?

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[php_man]serialize[/php_man] sounds like a good solution.. or some unified formatting of your own flavor..
Radical
Forum Newbie
Posts: 3
Joined: Tue May 25, 2004 2:52 am
Location: Bucharest, Romania
Contact:

Post by Radical »

feyd wrote:[php_man]serialize[/php_man] sounds like a good solution..
That's a good start too... but I had a similar thing to do.
If you have a `users` table and an `applications` table that certainly have a primary a better approach is to create a 3rd table withch will contain
id - (BIG)INT NOT NULL AUTO_INCREMENT - can be skipped but, if you're using MySQL, internaly he will add this column for his internal order... although it's better to put it (queries like SELECT a,... FROM tablename WHERE id=x are faster then SELECT a,... FROM tablename WHERE col1=x AND col2=y)
user - (BIG)INT NOT NULL - here you will put the id of the user (from `users` table) with you want to asign (deny) a right
application - (BIG)INT NOT NULL - here you will put the id of the application from the `applications` table
rights - ENUM('run4view','run4modifybutsavedifferentname',.....,'donotrun') - here are the rights... be creative...

Now for every user and every application you will have a new row into this table. By keeping the table with ROW_FORMAT = FIXED (no column of type TEXT or VARCHAR) you will have very fast access to it.

Wish you easy programming
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Radical's solution is good, however, personally, I'd use an INT for rights, where it's actually bit fields, like how unix file permissions work.. just to save some space...
Post Reply