Paul's Dev Blog – iGonzo.net

Might have been on the losing side. Still not convinced it was the wrong one.

Archive for the ‘mysql’ tag

Search Arrays without server code (sorta)

without comments

So it happens that I have a database field value that is a comma separated string of numbers. Classically, I would grab the field, turn it into an array, and search the array. Somewhat cumbersome and not really worth the effort of another function. That, and I would have to grab every record in the database, search through the applicable fields, and filter out the record(s) I need.

Me, being lazy, want an easier way to do it. Then I found this:

Then I came across this: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

All I have to do is construct a statement like this:

SELECT * FROM table_name WHERE FIND_IN_SET(‘thevalue‘,Field_To_Search)

You can also use a variable in place of ‘thevalue‘ if you need to search for, say, a passed parameter.

In the end, I was given a list of one or two records that I can easily do the rest of the work for, instead of grabbing every record in the table and filtering after the search.

For the lazy folks, this is quite a good thing. For the non-lazy folks, this is still a good thing. The above query not only executes faster, the code weight is greatly reduced and also runs faster (tests show about a 40% increase in speed and a 50% reduction in code execution time).

Final thoughts; this now leaves me more time to either work on something else, or go grab some food. I vote food.

Cheers

Written by iGonzo

October 15th, 2010 at 11:53 am

Posted in Database,Programming

Tagged with , ,