Mysql error duplicate results

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.

GROUP BY (first_name , second_name) in the end of query or SELECT DISTINCT first_name , second_name — M Khalid Junaid
– M Khalid Junaid, Commented Nov 17, 2013 at 10:16
Its getting from both tables , you haven't joined the tables — M Khalid Junaid
– M Khalid Junaid, Commented Nov 17, 2013 at 10:18
OK. I got solved that. i need to add join constraint. I have omited that. Thanks all. — Lakmal Vithanage
– Lakmal Vithanage, Commented Nov 17, 2013 at 10:21

shafi · Accepted Answer · 2013-11-17 10:54:06Z

2

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

edited Nov 17, 2013 at 10:54

answered Nov 17, 2013 at 10:34

shafi

3103 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fayhot · Accepted Answer · 2013-11-17 10:23:41Z

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 .

Collectives™ on Stack Overflow

Mysql error duplicate results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related