Country total amount in PHP from MYSQL Query

Question

I want to get Country = Total by IPs Adressen in php query already build the basis:

$result = mysql_query("SELECT t.*, gi.cname FROM tasks t LEFT JOIN geoip_all gi WHERE t.ip > geoip_all.iplong_start &&  t.ip < geoip_all.iplong_end", $link);
$county = mysql_num_rows($result);

echo "<p>County:\n $county</p> ";

I made this query so far only it don't work.

SELECT t.*, gi.cname FROM tasks t LEFT JOIN geoip_all gi WHERE t.ip > geoip_all.iplong_start &&  t.ip < geoip_all.iplong_end

Error message from query run in phpmyadmin:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN geoip_all.cname LIMIT 0, 30' at line 1

geoip_all.cname = Country name geoip_all.iplong_start = start of the ip ranges geoip_all.iplong_end = start of the ip ranges task.ip = IP from the visitor in the colunm is this the value : 636736027

Screenshots of the database:

geoip_all table: http://screencloud.net/v/5CgY

task.ip: http://screencloud.net/v/6B0L

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

It looks like you are possibly mixing up ANSI 89 and 92 syntax in your statement

SELECT 
    t.*, 
    gi.cname 
FROM 
    tasks t 
        LEFT JOIN geoip_all gi 
            on t.ip > gi.iplong_start 
            and  t.ip < gi.iplong_end

I have changed your where to an on statement. You are able to still add an actual where clause on the bottom of this statement though.

You might also do well to have a read of this in depth article I put together on how to get data from multiple tables. It covers off joins, inner and outer, aggregates and a bunch more.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 10 '13 at 03:34

Fluffeh

33,228
16
67
80

Get this error: #1054 - Unknown column 'geoip_all.iplong_start' in 'on clause' – RaoulCode Sep 10 '13 at 04:00
@RaoulCode Made an edit to the code. I guess if we are aliasing the tables, we should be using the alias itself when referencing them... :) – Fluffeh Sep 10 '13 at 04:19

score 0 · Answer 2 · answered Sep 10 '13 at 03:35

0

Change

SELECT t.*, gi.cname FROM tasks t LEFT JOIN geoip_all gi WHERE t.ip > geoip_all.iplong_start &&  t.ip < geoip_all.iplong_end

To:

SELECT t.*, gi.cname FROM tasks AS t LEFT JOIN geoip_all AS gi WHERE t.ip > geoip_all.iplong_start &&  t.ip < geoip_all.iplong_end;

I believe you're missing the AS statements to query your tables AS something.

answered Sep 10 '13 at 03:35

Josh Davis

157
3
10

You don't need to as `as` when making an alias. It's nice and readable, but not a syntax thing. – Fluffeh Sep 10 '13 at 03:48
Try changing your geoip_all.iplong_start and geoip_all.iplong_end to gi.iplong_start and gi.iplong_end – Josh Davis Sep 10 '13 at 04:03

score 0 · Answer 3 · answered Sep 10 '13 at 04:31

You probably want to use inclusive comparison (e.g. match when IP address is 1.0.0.0)

SELECT t.*, gi.cname
FROM tasks t
LEFT JOIN geoip_all gi ON (t.ip BETWEEN gi.iplong_start AND gi.iplong_end)

It's not clear from your screenshot if the IP ranges in geoip_all can overlap. If so, the above query may return multiple rows for one task.

If you want the number of tasks in each country (based on IP address), you'd be better off using COUNT() and GROUP BY. Something like,

SELECT gi.cname, COUNT(*)
FROM tasks t
LEFT JOIN geoip_all gi ON (t.ip BETWEEN gi.iplong_start AND gi.iplong_end)
GROUP BY 1

Again, this query returns accurate results only if the IP ranges are guaranteed to not overlap.

Country total amount in PHP from MYSQL Query

3 Answers3