Posted by David OBrien on 03/15/05 07:11

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

[Back to original message]