MySQL Query Join Table

Question

Both search bring back the correct results, but when I tried combining the 2 queries, it ends up giving bad results.

$query3 = "SELECT *
FROM airport_countries, airports
WHERE airports.iso_country=airport_countries.code &&
airport_countries.name like '%$search%'
ORDER BY pageviews DESC";

$query2 = "SELECT * 
    FROM airports 
    WHERE name like '%$search%' or 
municipality like '%$search%'
ORDER BY pageviews DESC";

VVVVVVVVV

SELECT *
FROM airport_countries, airports
WHERE airports.iso_country=airport_countries.code &&
airport_countries.name like '%$search%' or
    airports.name like '%$search%'  or 
airports. municipality like '%$search%'
ORDER BY pageviews DESC

What am I doing wrong?

I don't actually know the MySQL '&&' operator can be used in this case... but 'AND' instead. '&&' should be used for binary operations. — m c
– m c, Commented Mar 30, 2012 at 9:02
It would also be great if you explained what "bad results" means. — mathematical.coffee
– mathematical.coffee, Commented Mar 30, 2012 at 9:02
Bad results meaning, it is giving me way too many results. Some of the results do not appear in either query (when queried separately). — DJ Howarth
– DJ Howarth, Commented Mar 30, 2012 at 9:05
The query is changing airports.name from what it is suppose to be to the 2nd and 3rd airport.name results. So for example the 2nd airport name is London and the 3rd is Luton; it pulls in 20000 results and changes all the names to London/Luton. The correct results should be closer to say 100, each with a distinct name. — DJ Howarth
– DJ Howarth, Commented Mar 30, 2012 at 9:09

oezi · Accepted Answer · 2012-03-30 09:05:14Z

4

you problem aseems to be the operator precedence. to avoid that, just use braces:

SELECT *
FROM airport_countries, airports
WHERE airports.iso_country=airport_countries.code
AND (
    airport_countries.name LIKE '%$search%'
    OR airports.name LIKE '%$search%'
    OR airports. municipality LIKE '%$search%'
    )
ORDER BY pageviews DESC

i'd also recommend using AND instead of && (you're writing or instead of || - doesn't look very consistent), but thats just my opinion for better readability and shouldn't cause any difference.

edited Mar 30, 2012 at 9:05

answered Mar 30, 2012 at 9:03

oezi

51.9k10 gold badges102 silver badges119 bronze badges

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

Collectives™ on Stack Overflow

MySQL Query Join Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related