check if ip address is present between the given range of ip addresses in the database

check if this works for you.

select * 
  from (
		select replace(substring(substring_index(ip_from, ',', 1), length(substring_index(ip_from, ',', 1 - 1)) + 1), ',', '') ip_from_1,
			   replace(substring(substring_index(ip_from, ',', 2), length(substring_index(ip_from, ',', 2 - 1)) + 1), ',', '') ip_from_2,
			   replace(substring(substring_index(ip_from, ',', 3), length(substring_index(ip_from, ',', 3 - 1)) + 1), ',', '') ip_from_3,
			   replace(substring(substring_index(ip_from, ',', 4), length(substring_index(ip_from, ',', 4 - 1)) + 1), ',', '') ip_from_4,
			   
			   replace(substring(substring_index(ip_to, ',', 1), length(substring_index(ip_to, ',', 1 - 1)) + 1), ',', '') ip_to_1,
			   replace(substring(substring_index(ip_to, ',', 2), length(substring_index(ip_to, ',', 2 - 1)) + 1), ',', '') ip_to_2,
			   replace(substring(substring_index(ip_to, ',', 3), length(substring_index(ip_to, ',', 3 - 1)) + 1), ',', '') ip_to_3,
			   replace(substring(substring_index(ip_to, ',', 4), length(substring_index(ip_to, ',', 4 - 1)) + 1), ',', '') ip_to_4,
			   
			   replace(substring(substring_index($ip_to_look, ',', 1), length(substring_index($ip_to_look, ',', 1 - 1)) + 1), ',', '') ip_to_look_1,
			   replace(substring(substring_index($ip_to_look, ',', 2), length(substring_index($ip_to_look, ',', 2 - 1)) + 1), ',', '') ip_to_look_2,
			   replace(substring(substring_index($ip_to_look, ',', 3), length(substring_index($ip_to_look, ',', 3 - 1)) + 1), ',', '') ip_to_look_3,
			   replace(substring(substring_index($ip_to_look, ',', 4), length(substring_index($ip_to_look, ',', 4 - 1)) + 1), ',', '') ip_to_look_4
			   
		  from your_table) t1
 where (ip_to_look_1 between ip_from_1 and ip_to_1)
   and (ip_to_look_2 between ip_from_2 and ip_to_2)
   and (ip_to_look_3 between ip_from_3 and ip_to_3)
   and (ip_to_look_4 between ip_from_4 and ip_to_4)

Select allOpen in new window

or you can simply remove the "." from the ip address and compare if that works.

select *
  from (select replace(ip_from,'.','') as ip_from,replace(ip_to,'.','') as ip_to,replace($ip_to_look,'.','') as ip_to_look   
          from your_table) t1
 where ip_to_look between ip_from and ip_to

Select allOpen in new window

Just a side comment, since the solutions offered cover a complete spectrum.

The dot between the digits is NOT a problem as long as you are using ranges where upper and lower IP addresses have the dot in the same position: 1.234xxx will not be part of 12.34xx to 12.35yy (let's forget that .3zz cannot be in an IP address!)

Rather than deal with the dots in the IP address, you may find these functions helpful.
http://php.net/manual/en/function.ip2long.php <-- Interesting comments
http://php.net/manual/en/function.sprintf.php <-- See the %u format

Be careful about doing arithmetic with the "integer" values of IP addresses, since the IPv4 address scheme can generate negative numbers on 32-bit machines.

I have not tried to handle the IPv6 yet.

Best to all, over and out, ~Ray

ip2long is exactly what is used in solution I linked above:

$range_start = ip2long("68.61.156.0");
$range_end   = ip2long("68.61.181.255");
$ip          = ip2long($_SERVER['REMOTE_ADDR']);
if ($ip >= $range_start && $ip <= $range_end) {
...
}

Select allOpen in new window

Again, what is the interest of dealing with dots if all range limits are such that both their values have the dot in the same position?

@Ray_Paseur:
You have provided a really great solution and i have used it.
I have a question:
I have run the query 45 mins ago and it is still going, is it because of the much amount of data present in the .csv file ?
Would it take this long if i have to export the database and install it on some other server ?

@Ray
Only 38,000 rows has been inserted into database so far in 45 min and it is still going, and in the .csv file there are are 34,00000 rows,
is there some company that provide API so that instead of inserting data into my database i can get the service from them?

You mean 34M rows?
That's certainly a lot.
This was not clear in your question!

Now may be you should address this volume itself. It implies that you have very detailed IP ranges, probably detailed by clock owner etc.
Since there are 200 countries in the world, that would mean that you have on average 170K IP ranges per country? Do you really need THAT level of detail?

Looking back at the question and the answers

- your table just need 3 data columns (ip_start, varchar 1; ip_end, varchar 15; country, varchar 100) and probably as an id the record number (autocincrement primary)
- importing should be easy (but probably not with that volume!)

Once the import is over (but NOT before!!!!!!!!!!!), remember putting in place an index on each of the columns, so that you search will as fast as possible

(just to be sure: you are not currently importing with the index active, right?)

i am not importing with the index active. I have ip location to the city so there is a lot of rows in the file.

your initial question specifically mentions country... Beware: city ip resolution is fuzzy at best.

Use of countries is usually for serving country-based service, eg language or some specific pages.

Use of cities is usually for location based services, like local info and services (and advertisment). You will probably find in THAT case that your ip reference data is not good enough. If your site targets mobiles, you should consider the location service they integrate, and ask them for permission to get it.

Just exactly what do you want to achieve?

I am developing an application that helps me to know about the visitors who visits on my website. I am creating an chat application that will let me to chat with the visitors in real time.
so i need to know that from which specific region the visitor is .

You might consider reconsidering. Knowing which country they are from will be enough in most situations.
And city / IP are not really trustable enough to build business upon: in countries like mine (France) some ISPs are frequently moving IPs between region. And most IP databases place me in the center of France because they do not know for sure which city I'm living in.

(I presume that by now your database has been fully loaded...)

Well i dont need it to be precise , it is just my customer's requirement . I just need to get it working somehow regardless the accuracy.
I stopped loading the database because it would have took more than 24 hours to execute the 3700000 quires.
Other thing is that when i try to know that which browser the visitor is using by $_SERVER['HTTP_USER_AGENT'] , instead of giving me one browser's name it gives me a string that includes the name of all browsers , it gives me something like this:
 [HTTP_USER_AGENT] => Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.19 (KHTML, like Gecko) Ubuntu/12.04 Chromium/18.0.1025.151 Chrome/18.0.1025.151 Safari/535.19
it includes the name of Mozilla, chrome, safari while in actual i am using the chrome browser.
So these are the two problems i am facing right now.

The user agent problemis another complete issue and probably needs another question.
Here again, I fear that you search after a perfect identification, which in most situations is not possible

Something has got to be wrong in the logic or the data.  Let's do the arithmetic...

3,700,000 queries.  24 hours = 86,400 seconds, suggests a query rate of 43 per second.

When I ran this using the Maxmind data, I found that I could insert 170,000 rows in about 15 seconds, or about 11,300 per second.  

Your loading process should have finished in less than 6 minutes.  Please go back to the article and try it that way, then post back here if you're still having troubles.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3437-IP-Address-to-Country-in-PHP.html