Delimited mySQL Column

Need to match?

[wonderbread]

I currently have a database that contains a bunch of different products. One of the columns (category) contains the category ID for each product. The catch is that some products are listed in multiple categories. To solve this problem the category column is varchar and contains data where the categories are delimited by a "¦". For example, one record may have the following category "3¦6¦1¦0".

I now want to pull up the records for only a specific category. Currently I've tried using wildcards but it isn't working perfectly. My SQL statement is currently the following:

$sql = "SELECT * FROM table WHERE category LIKE \"%$category%\" ORDER by title DESC";

This works for the most part except, when I pull up category 2 I also get results for category 20. Does mySQL have a way to split this field based on a delimiter and search each separated value?

Thank you in advance for your help.

Nick Ladd

[drbrain]

You need to add a lookup table instead of using a delimiter to do the work (because that's what databases are good at).

The lookup table should contain both a category id and a product id where each product id is mapped to N category ids.

This way you can use a simple JOIN and WHERE to pull information from either direction.

[nalin]

First - you performance on the code you have given (and moreso on mine below) will leave a lot to be desired - you may want to restructure you data in a list or tree type form where you can use indexes and equality comparisons rather than string searching and regular expressions.

In any case try

...category REGEXP(\".*[^0-9]$category[^0-9].*\") ORDER by...

or something roughly along these lines.
There also *should* be a manner in which to escape the pipes this will give far better performance particularly as strings grow.

[hiker_jjw]

Basically, you should probably redo your database.

Otherwise, this might work. Regular expressions are the key to solving your problem. Isn't this a boundard word problem.

$sql = "SELECT * FROM table WHERE category REGEXP \"[[:<:]]$category%[[:>:]]\" ORDER by title DESC";

Just a thought, I'd have to test it.