Converting an IP to a Long in MySQL

Question

I'm trying to use the ip2country table to show the country flags of the users on my website.

The simplest thing I came up with is to write an SQL statement that takes the users from the session table and queries to see if their respective IP is in a certain range to figure out their country/flag.

It's simple but also dangerous, because when there are 300 users online to show and I fetch them from the session table, querying their countries to display the flags, there will surely be a big memory usage.

Now I tried this to do it in one single query:

SELECT 
  s.session_ip, 
  ipc.*
FROM 
  session AS s
    LEFT JOIN ip2country AS ipc 
    ON ipc.ip_lo <= s.session_ip AND ipc.ip_hi >= s.session_ip
WHERE 
  s.session_time  > '".( time() - 60) )."' 

Now it's clear that the above query is wrong because the IPs saved in the ip2country table is an integer, e.g. 1000013824, and the IPs stored in the session table are the string representations of the IPs, e.g. 193.169.0.0

I know how to convert from an IP to a long in PHP with the ip2long(), but is there any equivalent method in MySQL so I don't have to do two queries?

Answer 1

SELECT INET_NTOA(1000013824) -> 59.155.0.0

SELECT INET_ATON('193.169.0.0') -> 3249078272

Enjoy :)