Thread

Hi,

This is more if a SQL question than directly related to ExpressionEngine. Our client hired us to upgrade their existing ExpressionEngine installation from an early version of EE2 to 2.5.2 installed by another developer. When doing this, we got an error saying the SQL was invalid. After troubleshooting we found that the error stemmed from a SQL query to show a modal to users in a specific country that was querying the exp_ip2nation table:

<?php
 $sg_country = ip2long($_SERVER['REMOTE_ADDR']);
?>

 {exp:query sql="SELECT country AS sg_country FROM exp_ip2nation WHERE ip < '<?php echo $sg_country; ?>' ORDER BY ip DESC LIMIT 0,1"}
 {if sg_country == "gb" OR sg_country == "uk"}
 
 <!-- code to show modal -->
 
 {/if}
 {/exp:query}

The problem is that the new version doesn’t have the “ip” column; it has “ip_range_low” and “ip_range_high” so this caused the site to break.

My question is: Has anyone queried the database in a similar fashion using the new structure and how would I do it now that there are 2 columns in the database instead of the single one? If so, how would I modify the SQL above to achieve it with the new structure?

Any help would be greatly appreciated.

Thanks,

Scotty

Hi Scotty,

I can’t really help with custom queries here, but I’d be glad to move this thread over to the Development forum where others can toss in and help you come up with working query. Would you like me to do that?

Hi Scotty,

I asked for some insight on this. This revised query may work:

{exp:query sql="SELECT country AS sg_country FROM exp_ip2nation WHERE ip_range_low <= '<?php echo $sg_country; ?>' ip_range_high >= '<?php echo $sg_country; ?>' ORDER BY ip_range_low DESC LIMIT 0,1"}

Cheers!

Hi Dan and thanks for the reply. I tried that and it caused a 500 error. I then tried an AND and an OR between the IP ranges like this:

{exp:query sql="SELECT country AS sg_country FROM exp_ip2nation WHERE ip_range_low <= '<?php echo $sg_country; ?>' AND ip_range_high >= '<?php echo $sg_country; ?>' ORDER BY ip_range_low DESC LIMIT 0,1"}

and

{exp:query sql="SELECT country AS sg_country FROM exp_ip2nation WHERE ip_range_low <= '<?php echo $sg_country; ?>' OR ip_range_high >= '<?php echo $sg_country; ?>' ORDER BY ip_range_low DESC LIMIT 0,1"}

Unfortunately, while both of these did load the page, I no longer got correct indication of the country I was in.

Thanks again for trying.

Scotty

Scotty,

Have you tried the IP 2 Nation module’s native template tags?

{exp:ip_to_nation:world_flags type="text"}
        {ip_address}
{/exp:ip_to_nation:world_flags}

With that, {ip_address} will output the name of the country of the person viewing the page.

~

* Edit, I got it working. I took the channel name out of the tag and it works great.

Thanks!

Great! Glad to see it, Scotty. Anything else we can help with?

That’s it. Thanks, a ton!

Hey Scotty,

Excellent!

~