mySQL Delimited Fields

wonder_bread23

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

paulnaj

Hi Nick,

I think this'll work (sorry, can't help formatting it in my own little way) ...

$sql = '
	SELECT * 
	FROM table 
	WHERE category REGEXP "(^|\\|)'.$category.'($|\\|)" 
	ORDER by title DESC
';

If you don't understand regular expressions then try it before you attempt to understand what's going on!

Briefly, this bit ...

(^||)

... say's "match the beginning of the string (⁾ OR (|) match the bar-separator (|).

And this bit ...

($||)

... say's "match the end of the string ($) OR (|) match the bar-separator (|).

Paul.

EDIT: Had problems displaying the REGEXP between the PHP tags ... fixed now.

wonder_bread23

Hmmm..I understand what you are saying but it still doesn't work...the SQL statement dies.

I've tried escaping the dollar sign but it doesn't seem to work. Does mySQL support REGEXP?

Thanks!

Nick Ladd

paulnaj

Hi Nick,

Yeah, regexp is OK!

So, try this ...

$sql = ' 
    SELECT * 
    FROM table 
    WHERE category REGEXP "(^|\\\\|)'.$category.'($|\\\\|)" 
    ORDER by title DESC 
';

I forgot to 'double' the escape from one \ to two, because of the way php, then mysql works on the string.

(There might be other little adjustments, so come back if it still doesn't work 🙂)

Paul.

wonder_bread23

Hmmm...It still doesn't seem to work...can't seem to figure it out...any thoughts?

Thanks for all of your help!

Nick Ladd

paulnaj

No probs, Nick, 🆒

What happened to the last try? Did it die, or just return the wrong result?

If it died, try this ...

$sql = " 
    SELECT * 
    FROM table 
    WHERE category REGEXP \"(^|\\\\\\\\|)".$category."($|\\\\\\\\|)\" 
    ORDER by title DESC 
";

Paul.

wonder_bread23

That did the trick! Thanks a ton!

nick