[Resolved] Complicated Select question involving comma seperated data in fields

ajna

Problem in a nutshell involves: breaking contents of field by comma and searching against search form input.

I am looking to set up a table with the following logic...

Item ID auto-incremented #
Categories selection of numbers sep. by commas (2,5,12,3)
Name desc. text
Colors selection of numbers sep. by commas (2,4,6)

My Categories and colors are determined in sep tables like

ColorID auto-incremented #
Color_Name Name to display

ColorID | Color_Name
1 | White
2 | Black
3 | Blue

CatID auto-incremented #
Cat_Name Name to display

CatID | Cat_Name
1 | Widgets
2 | Fidgets
3 | Nods

The idea being that an ITEM can belong to multiple Categories and be available in multiple colors without having to have a separate table entry for each variation.

how can i string together a select for this? let's say I want a list of all ITEMS that contain a ColorID of 2 and/or 1 and CatID of 3?

I mean typically I could "SELECT * WHERE Categories = 3" for the Category portion, but the actual Categories value stored is "2,3" or "1,3,5" -- not the number 3. I don't think LIKE will work either because it would match 3 and 13 and 23 etc if the field contained "1,13,6,3"

I know that I can send a string of comma separated values to compare against a single value in my table (ie: (Colors IN 1,4,2)) but that seems to presume that the value of Colors would be a single value -- when it will actually be a series of comma separated values.

I want to avoid a reiterating loop of search results process through further searches, etc to weed out my results while keeping the search function quick and low on resources --- any ideas on what kind of SELECT structure I would need to use?

ANY HELP is greatly appreciated.

Thanks!

bodzan

...using LIKE statement...

Say searching the category 3 in the categories field...

SELECT * FROM TABLE_NAME WHERE Categories LIKE %3%;

It is pretty dumb but I think would work...

ajna

Let's say the table in question contained the options 2,7,13,17 and the search was for category 13. The user searches for 3.

SELECT * FROM TABLE_NAME WHERE Categories LIKE %3%;

Wouldn't that bring the record up as a match even though it isn't?

The same issue with the number 1 -- 13 and 17 would be considered a match if a user searched for category 1.

Maybe it's better to make the tables a bit more diverse....a category/inventory relational table on it's own --

so given

Color Table
ColorID | Color_Name
1 | White
2 | Black
3 | Blue

Category Table
CatID | Cat_Name
1 | Widgets
2 | Fidgets
3 | Nods

CatMerge Table
ItemID | CategoryID
3 | 1
3 | 2

ColorMerge Table
ItemID | ColorID
3 | 2
3 | 3

Inventory Table
ItemID | Name
1 | This
2 | That

so a search for Blue Widgets ($color = 3, $cat = 1) would be something like:

SELECT DISTINCT I.ItemID, I.Name

FROM Inventory I, CatMerge C, ColorMerge CO WHERE

(I.ItemID = C.ItemID) AND

(I.ItemID = CO.ItemID) AND
(C.CategoryID = '$cat') AND
(CO.ColorID = '$color')

Does that look like it's workable?

I am trying to plan out the logic on this without having to create the test environment right away.

Thanks

Kev

AstroTeg

Ajna, you've got the right idea. Although you're using a 1 to 1 join which is good. But you really have a 1 to many join. Example: You have 1 inventory item which could have many categories. Or 1 inventory item which has many colors. To do this, you might look into how inner joins work (as well as left or outer joins).

Keep this thought in mind: If you have the urge to store comma-delimited data in a field, you have a database/table design problem. In most cases, all you need is an additional table or two to define the data you're storing and the relationship of that new data to the existing data (if that makes sense).

ajna

Thanks Astro.

I've played with INNER and OUTER JOINS some but shied away from heavy power-usage so far.

I don't know why this method didn't occur to me before, I already set up a series of tables for another application using the logic of multiple tables related by separate 'relational' tables.

Don't be surprised if am posting here in the near future seeking clarification on some OUTER and INNER JOIN issues. 😃

Thank!

Kev