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

["afan@xxxxxxxx" <afan@xxxxxxxx>]

David OBrien wrote:

> At 04:53 PM 3/14/2005, afan@xxxxxxxx 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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php