Re: can't pull products from DB with REGEXP?

David OBrien wrote:

At 04:53 PM 3/14/2005, afan@afan.net wrote:

I have table with products. In column categories are listed numbers of categories a product belongs, like: 1. 21, 2. 35, 8, 72, 1, 4, 3. 23, 11, 48, 4. 65, 5. 11, 6. 23, 7. ... (somebody else created the table this way and I have to keep it for the moment) Now, I have to list all products that belongs to category with cat. number, e.g. 11. If I use query: select * from products where categories like '11, ' product no. 5 will be listed but not product no. 3 select * from products where categories like '%11,% ' will list products 3 and 5 but select * from products where categories like '%1,% ' will list products 2, 3 and 5 too - and, 3 & 5 are wrong. I got from friend this solution: select * from products where categories REGEXP '(^".$cat_num."|, ".$cat_num.")' it works just fine if I use 2 digit cat number. e.g. 11 or 26 or 62. But if I select for cat. num. 2 it will list all products from categories 2 and everything between 20 and 29. 32 or 62 will not be listed. Confusing, ha? Thanks for any help! -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

get the categories in a variable. like <? $cat = "35, 8, 72, 1, 4,"; $cat = str_replace("," , "|" , $cat); $cat = str_replace(" " , "\\ " , $cat); // this will get you $cat = "35|\ 8|\ 72|\ 1|\ 4|"; $cat = rtrim($cat, "|"); // this will get you $cat = "35|\ 8|\ 72|\ 1|\ 4"; $sql = "SELECT * FROM products WHERE categories REGEXP '(" . $cat . ")'"; echo $sql; ?> produces SELECT * FROM products WHERE categories REGEXP '(35|\ 8|\ 72|\ 1|\ 4)' which matches the correct items in my test data -Dave

Maybe I didn't explain very well: Navigation has list ofo all categories. If I click on e.g. "Travel mugs" (cat_num=3) I have to list all products from that category. Means $cat = $_GET['cat_num']; Im my case it's not $cat = "35, 8, 72, 1, 4,"; then $cat = 3; Always just ONE number. I tried what Jamie Alessio said: select itemNumber from products_test where FIND_IN_SET(".$value.", categories) (I took out all spaces after comma first) Now it works just fine. Thanks Jaime. Thanks to all of you guys for help! :) -afan