Geo IP database query problem

Question

I'm running this query

SELECT 
country, 
countries.code, 
countries.lat, 
countries.lng, 
countries.zoom, 
worldip.start, 
worldip.end 

FROM countries, worldip 
WHERE countries.code = worldip.code 
AND 
'91.113.120.5' BETWEEN worldip.start AND worldip.end 
ORDER BY worldip.start DESC

on a tables with these fields,

worldip           countries
--------------    ----------------
start             code
end               country
code              lat
country_name      lng
                  zoom

And sometimes I'm getting two results in two different countries for one ip. I understand why

'91.113.120.5' BETWEEN worldip.start AND worldip.end

would return two different results since 10 is between 9 and 11, but also 5 and 12. I would have thought including WHERE countries.code = worldip.code would have prevented this, or at least ensure I got the right country no matter how many results it returned. but it doesn't.

I also added ORDER BY worldip.start DESC which seems to work since the more accurate an ip adress, the higher up the list it appears. you can see it working (or not) here . But that's a quick fix and I'd like to do it right.

SQL is a real weak point for me. Can anyone explain what I'm doing wrong?

Answer 1

Firstly nice app. I was looking for flights - I would love price comparisons and no #based links please. You could try a free geolocator service instead of using your own geoip database. That aside is your ip field of 'IP' datatype in MySQL allowing comparison ? This may help you get correct ordinality. Otherwise the stuff is compared as strings and problems may arise where the length of IP's is different and so on.

With integer representation of IP's you can use the <= and >= operators.