As the title suggests I have a MySQL query like this:
SELECT DISTINCT `friendly_url` FROM `post` WHERE `description` LIKE ? OR `heading` LIKE ? ORDER BY `friendly_url`
I have given the string '%' wild card in the parameters so that this works as a Search function. How ever, Say a user is searching for a common word like 'is' and it appears in both heading and description in the same post. Then this query returns the same post twice. I don't want that to happen, hence the 'DISTINCT'.
Why does this happen? Any way I can work around to make it work the way i want?
The query is not returning the same row twice. The predicates in the WHERE clause are evaulated against each row, and either the row is returned, or it's not. No combination of OR conditions is going to cause MySQL to return the "same row" multiple times.
If you are getting duplicate values of friendly_url, then you have multiple rows in the post table that have the same value for friendly_url. (i.e. friendly_url column is not unique in the post table.)
You don't have to use the DISTINCT keyword to remove duplicates from the resultset. You could remove the DISTINCT keyword, and add GROUP BY friendly_url before the ORDER BY clause.
To identify the "duplicated" values of friendly_url, and how many rows have that same value:
SELECT p.friendly_url
, COUNT(1)
FROM post p
GROUP BY p.friendly_url
HAVING COUNT(1) > 1