0

I'm currently working on an advanced search feature for part of my site. An issue i have faced is when comparing values from a database field list eg. (database field item_sizes can be 1 or 1,4,12,15) and i then have to compare this against a selected list of filtered sizes (1, 12) to see if ANY of those values are present.

In a normal case of one database value i would use

And item_sizes In (#filter_sizes#)

However, all this will do is find a match of the actual LIST, rather than checking each value against filter_sizes to check for any match at all. Does anyone know how i can do this?

TEMPORARY EDIT

Problem has been solved by using (in my case) SKU's, which allow for unique matching of sizes against the filtered list. A more technical edit will be up later once i have implemented the feature myself.

ale
  • 6,369
  • 7
  • 55
  • 65
Banny
  • 811
  • 4
  • 13
  • 36
  • I don't understand your example as it seems like that should work for you. What data type is your `item_sizes` column? Perhaps you could give us an example of the data and a search filter with your desired output. And always remember to use `` tags within your `` statements. – Miguel-F Nov 26 '13 at 15:27
  • 3
    Normalize your database and this sort of thing will be simple. – Dan Bracuk Nov 26 '13 at 15:32
  • 1
    If you mean a *single* column contains CSV lists of values, that is the problem. **Databases are optimized to work with rows of data, not lists**. Storing lists is a poor design that generally creates a host of problems. Not the least of which is the one you just discovered: *it is difficult to query*. While there are hacks to work around some of the deficiencies of that structure, in the long run you are much better off normalizing the data and storing the relationships in a separate table, as rows not lists. – Leigh Nov 26 '13 at 15:46
  • @Leigh i would LOVE to do so, however i'm working with a database that i have no management over, and the data has been stored like this for years. I need to find some sort of workaround to work with the dataset i have then work towards convincing them of changing it to a new database structure. – Banny Nov 26 '13 at 16:06
  • Actually i just remembered we have SKU's this might just solve my issue.. Love answering my own questions! – Banny Nov 26 '13 at 16:07
  • @LeeB - Ugh... the joys of working with legacy systems. Probably good to mention that up front next time, to stave off the inevitable slew of normalize comments ;-) (I still think it should be normalized, but I know that is not always possible). (Edit): Could you update your question to indicate the relationship of SKU to the query above? That way your subsequent answer will make more sense to others. – Leigh Nov 26 '13 at 16:10
  • @Leigh ha yeah i should of pre-empted that one! I want to learn advanced SQL and optimize our database as much as possible. That's something for the future though.. And yeah sure, i'll just implement it first so I don't get it wrong and confuse others. – Banny Nov 26 '13 at 16:15
  • 1
    @LeeB - Well sometimes the "higher-ups" miss the strong reasons to avoid lists, until you start having problems, like the reports are slow or they are missing data because of a stupid extra space in a list (or my favorite the queries searched for "49.0" instead of "49"). Then all of a sudden it becomes clear! – Leigh Nov 26 '13 at 16:25
  • @Leigh i think sometimes they may see the issues but have the approach of "it costs money and nothing is wrong with it right now" but if a company wishes to grow then things have to be changed or some of the issues you stated can tend to crop up! – Banny Nov 27 '13 at 10:43
  • @LeeB - you should delete this question since you [created a another one](http://stackoverflow.com/q/20240514/1636917). In the future just [edit] the original question instead of creating a new one. – Miguel-F Nov 27 '13 at 14:07
  • Temporary edit reminds me of [this](http://stackoverflow.com/a/778275/2742805). – OGHaza Dec 23 '13 at 14:37
  • Haha, that amused me :) I never got round to finishing this unfortunately! – Banny Jan 22 '14 at 16:46

0 Answers0