Page 1 of 1

Selecting from database.. commas

Posted: Tue Sep 19, 2006 1:07 am
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?

Posted: Tue Sep 19, 2006 1:47 am
by jmut
normalize your database.
hence, rethink your database layout.

Posted: Tue Sep 19, 2006 1:51 am
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?

Posted: Tue Sep 19, 2006 2:00 am
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

Posted: Tue Sep 19, 2006 7:40 am
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.

Posted: Tue Sep 19, 2006 11:02 am
by RobertGonzalez
Nice Ninja. Very good explanation.

Posted: Tue Sep 19, 2006 6:54 pm
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?

Posted: Tue Sep 19, 2006 6:55 pm
by RobertGonzalez
Try them and see.

Posted: Wed Sep 20, 2006 3:32 am
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.