Mysql错误重复结果

Question

I wrote a mysql query and got same result twice. Now i can't fix the problem. can anyone please help me.

SELECT first_name , second_name
FROM profile_doctors_main , category
WHERE (first_name LIKE "%'.(string)$name.'%" OR second_name LIKE "%'.$name.'%")
    AND category_name LIKE "%'.$category.'%" AND city LIKE "%'.$city.'%"

result = Sharon Zing

Any helps would be appreciated.

Answer 1

when you need to query data from multi tables, you should you use phrase 'JOIN', and alias the table when necessary.

and when you use "JOIN", you should add the "ON" condition, which do something like "where" as a filter that remove the duplicate results.

Try the sql following(as I do not know about the table field, it's just a sample)

'SELECT first_name , second_name FROM profile_doctors_main , category 
 on profile_doctors_main.xxx = category.xxx 
 WHERE (profile_doctors_main.first_name LIKE "%'.(string)$name.'%" 
 OR profile_doctors_main.second_name LIKE "%'.$name.'%") 
 AND category.category_name LIKE "%'.$category.'%" 
 AND profile_doctors_main.city LIKE "%'.$city.'%"'

there's another problem, that wrong match of the "and" condition and the "or" condition .