Forum Moderators: coopster
I have records in my database that look like this:
"1,4,5,15,23"
"2,3,23"
(they are stored as strings)
the variable is chosen by a bunch of numbered checkboxes, then the result is imploded with commas and stored as a string.
When I do a normal database query, I get the result back as a string, like this:
$row=mysql_fetch_array($result);
echo $row['services'];
(prints 1,4,5,15,23)
What I need to do now is create a SELECT query that finds only the records containing a certain number. I originally had it doing this kind of thing:
$i=5;
SELECT * FROM mytable WHERE services LIKE '%$i%';
I know that if there was a comma added to the beginning and end of the string, I could look for this:
SELECT * FROM mytable WHERE services LIKE '%,$i,%';
But that seems like a weird solution, and my data isn't arranged that way right now.
Can mySQL parse a delimited string somewhere in the WHERE clause?
$i=5;
SELECT * FROM mytable WHERE FIND_IN_SET [dev.mysql.com]('$i',services);
I'll try that right now - thanks!
Am I the only one who finds the MySQL manual unreadable?
You are not alone.
This is a multi-part form where the user chooses from many services, locations, specialties, and a few other other factors, and those steps have n options stored in separate tables. Each step of the form presents a large group of checkboxes.
With this method, i can grab the string "1,2,4,15,17,34,45,57" and very easily display which services are available at a given location. And on the other side, I can just as easily implode the values returned from the checkboxes for use in a SELECT (thanks again, coop!), or an UPDATE.
I can also EASILY add new services and locations to the other tables, just by adding a single row to another table, and my forms will show a new checkbox ready to go. If I added a new tinyint(1) row to the main table, I'd have to rewrite every INSERT,UPDATE,SELECT query in the entire program.
maybe i haven't explained it perfectly, but I'm happy with this method, in this situation.
cheers
thanks - and yeah, I considered that option very seriously. then I realized if I made a separare tinyint(1) column for every checkbox, my tables would have over 150 columns, and they would not be so nicely grouped. And what would I call them all? service_option_1, service_option_2, service_option_3...?
Why would you do it that way? Make a second table called Options with two fields:
UserID
OptionID
-
UserID would contain the unique ID for each user (Primary Key from the other table) and OptionID would be the option number. If my UserID was 100 and I had checked options 34,35,50, and 60 you would see a table that contains:
USERID,OPTIONID
-----------------
100,34
100,35
100,50
100,60
This is not only faster, but a much more efficient use of space than storing string data. Then when you want to look up a user you would do something like:
SELECT A.* FROM mytable A, Options B WHERE B.OptionID = 35 AND B.UserID = A.UserID
it's working my way, so I'll leave it (ain't broke)
even though i'm not taking your advice, thanks, seriously, you reminded me why i need to go back to school, and that perhaps i need a long vacation
With this method, i can grab the string "1,2,4,15,17,34,45,57" and very easily display which services are available at a given location
If you will always be trying to find which services are available at a given location, your method should always be faster than digitalv's solution.
However, if you want to find which locations have a given service, I believe that things might start to slow down because
1. your indexes will be much larger (that's obvious)
2. your selects will be less efficient (you will be doing a "like %arg%" on a very large index rather than an exact match on a smaller index)
3. the FIND_IN_SET function will not be optimized because the column type can't be a set (or would be a huge set).
Of course, you would have to benchmark it to know which is faster.
Tom
