mysql feild with multiple user ids
Posted: Sat Jun 06, 2009 2:31 pm
i have a table called books with a feild called user_ids which correspond to the books authors account. now seeing as a book can have multiple authors; this left me no choice but to store mutiple users ids in one column. for example a row in the book table may look like this:
--------------------------------------------------------------
book_id | user_ids | title | isbn | publisher | date |
--------------------------------------------------------------
NA | ;12;5;2;3;1; | NA | NA | NA | NA |
now lets say author with primary key 5 logs on to the site and wants to see the books he has contibuted to
now seeing as i cant no loner say: SELECT * FROM books WHERE user_ids = 5, i have devloped another way:
you may be wondering why i have semis all over the place: its becuase the record would appear like this originally: 12;5;2;3;1
but i had to specify the regex as ;?{id};? which returned data unexpectedly which is why i have this method now. but beucase the last method did this
its leaving me totaly paranoid about this new method in which i have semis before the id starts and once it finishes. so my question is this: is this a good way to access the data or can this method also return unexpcted results?
--------------------------------------------------------------
book_id | user_ids | title | isbn | publisher | date |
--------------------------------------------------------------
NA | ;12;5;2;3;1; | NA | NA | NA | NA |
now lets say author with primary key 5 logs on to the site and wants to see the books he has contibuted to
now seeing as i cant no loner say: SELECT * FROM books WHERE user_ids = 5, i have devloped another way:
Code: Select all
SELECT * FROM books WHERE user_ids REGEXP ';{author_user_id};'but i had to specify the regex as ;?{id};? which returned data unexpectedly which is why i have this method now. but beucase the last method did this
its leaving me totaly paranoid about this new method in which i have semis before the id starts and once it finishes. so my question is this: is this a good way to access the data or can this method also return unexpcted results?