No Exact match when using LIKE in SQL statement

Question

   SELECT bp.*,r.rating,COUNT(r.review_for),bp.business_name,bp.profile_member 
     FROM ibf_business_reviews r 
LEFT JOIN ibf_business_profiles bp ON ( r.review_for=bp.profile_member ) 
    WHERE bp.sub_category LIKE '%{$id},%'{$location_sql} 
 GROUP BY r.review_for HAVING COUNT(r.review_for) >=1 
 ORDER BY r.date_posted DESC LIMIT 0,2");

This query is used to show results for business_name in a certain sub_category id '%{$id} in a certain location. My problem is that extra results are showing in categories that share a second or third digit aka ...viewcat&id=54 will show in ..viewcat&id=154 etc

I using the LIKE may be my issue? WHERE bp.sub_category LIKE '%{$id},%'

Answer 1

You are storing a comma-separated list in a varchar, when you should store one number per row in a child table. Then you wouldn't have to use LIKE at all.

Read up on First Normal Form.

Answer 2

That's right using like can result in matches that fit the pattern your searching on.

Answer 3

Here was my comment

+! for the need to reformat the SQL. You do realize that the "percent" signs (%) are the wildcards. So you're essentially telling it that you can return ANYTHING that includes id... so if you search "23" you could get "123", you could get "234" or "1234"... etc.

and you replied

Thanks @Rock removing the wildcards worked!

Now my answer to this is... If you removed BOTH wildcards from your string, then you're essentially doing an "equals".

IE:

WHERE bp.sub_category LIKE '{$id},'

should be the same as

WHERE bp.sub_category = '{$id},'

Because you don't have any wildcards to "match" in the "LIKE" statement.

Please forgive me if I screwed up the "$" or the ","... I'm not a MySQL guy