Selecting from database.. commas

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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Selecting from database.. commas

Post by Mr Tech »

I have a project manager that allows you to assign projects to a certain user... However, I am wanting to be able to assign the project to a number of users at one time...

I've got the html and insert parts worked out fine... When you select the users, it inserts the users id into the database seperated by commas... E.g:

Code: Select all

1,5,21
This is where my problem begins... I want to select out of the database all the projects that have the user id 2 in it... Here is the code I'd use:

Code: Select all

select * from projects where userids rlike '2'
However, it will pick up anything with the number 2 in it which in this case, it would find the number 21...

Does that make sense? How would I make this work?

Would I have to change the database insert to:

Code: Select all

,1,5,21,
And my select code to:

Code: Select all

select * from projects where userids rlike ',2,'
What are your thoughts?
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

normalize your database.
hence, rethink your database layout.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

That doesn't make any sense to me? What will rethinking my database do? How else can I record that several users are assigned for the one project?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

generally, you only want one piece of information per cell in the database... so in order to accomplish that here, you need to rethink your database layout... this sounds like a many-to-many relationship (projects can have many users, users can have many projects) so you would need to introduce a table to tie these two together...

I would make a table called projects_users with 2 columns: project_id and user_id. For each project the user has, add a row to this table instead:

So this..

Code: Select all

user_id    project_id
1              4
1              5
1              11
2              4
2              5
would mean that user #1 is in projects #4, 5, and 11 while user #2 is in projects #4 and 5

Then you would need to use table joins to select from these three tables
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL has a function called FIND_IN_SET(). I don't know if other databases have such a function, but I imagine they do in some form or another.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Nice Ninja. Very good explanation.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Yea, thanks Ninja. 8)

Do you think it would be better doing it Ninja's way for would feyds FIND_IN_SET() be better?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Try them and see.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

From a normalisation/general db design point of view, Ninja's way is "more correct".

Only thing I'd do is switch the order of the columns - the project appears to be your primary piece of information, in that the project "contains" the users, so I'd have project_id, user_id and a compound primary key covering both columns. I'd also put a key on the user_id column so that I could see which users were doing which projects.
Post Reply