Reputation: 39
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},%'
Upvotes: 2
Views: 2765
Reputation: 47377
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
Upvotes: 1
Reputation: 562290
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.
Upvotes: 3