JD* Posted September 6, 2008 Share Posted September 6, 2008 Hey all, Been bashing my head against this for a bit...I'm trying to grab users from a particular building using a LIKE statement, but I think I need some outside perspective. For my site, users are assigned to building in a column like: User Name Building Joe Smith 1,2 Jane Smith 2,4 Don Ho 12,3 What I'm looking to do is pull all users where the building, lets say, is 2. Currently, doing this: SELECT * FROM table WHERE building LIKE "%2%" Is going to return both Joe Smith, Jane Smith AND Don Ho, when I only want Joe and Jane. If I add in a "%,2,%", I'm not going to get Jane Smith because there is no comma in front of her building number. Now, I know I can put a 0 in front of my single digit numbers, but before I do so, I wanted to know if I'm missing anything. This all ties into a function where I pass the current building I'm looking for, so it needs to be fairly flexible. Thanks! Quote Link to comment Share on other sites More sharing options...
fenway Posted September 6, 2008 Share Posted September 6, 2008 You don't want to store multiple values in a single field that ... use a separate table. Quote Link to comment Share on other sites More sharing options...
JD* Posted September 6, 2008 Author Share Posted September 6, 2008 I was hoping for a better answer, but that's cool. I actually do have a table that records each of these in a separate row, but was hoping to migrate it over to this newer setup... Thanks for the help Quote Link to comment Share on other sites More sharing options...
fenway Posted September 8, 2008 Share Posted September 8, 2008 If you're stuck with this data, use FIND_IN_SET() -- as long as they're comma-separated, it will work. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.