使用mysql显示特定国家/地区的Web内容的最佳方式

Question

Need a big help from you. I need an advice from all you good people out there.

I have some contents that i need to display only to few countries.I am okay with getting the user's country with Geoip.i am storing my content in MySQL database with a country_code field.country code can be empty or any single code or group of comma separated values

What i want to do is,

1. checking country_code and if it is empty, display that content for all users.
2. if country_code is not empty and only a single value,display content only for the given country_code users.
3. if country_code is group of comma separated values,display contents for all the comma separated country users.

i have two options for do this

1. adding data columns for each country separately and display contents as for example three columns for three countries US,UK,AU and if user's location is US,

   $sql=mysql_fetch_array(mysql_query("SELECT * FROM content_table WHERE US=='$country_code'"));

2. adding country data for single column as country_code and store US,UK,AU and if user's location is US,

   $sql=mysql_fetch_array(mysql_query("SELECT * FROM content_table WHERE country_code!=''")); $pieces = explode(",", $sql['country_code']); foreach($pieces as $val){ if($country_code=="US") //display content }

NOTE:amount of countries may differs according to my needs

what will be the best practice to overcome this,

• using different columns to different country make fast search but adding more columns is not good i think
• using single column and iterate comma separated values needs more computation

What should i do..?? please advice me on this.. OR do you have any idea better than this..? Thanks

Answer 1

$sql=mysql_fetch_array(mysql_query("SELECT * 
    FROM content_table 
    WHERE country_code = '' 
    OR country_code REGEXP ',?". $user_country_code .",?'"));

If I understood you well, you want to show elements to the user only if the user country code is in the country_code field or if the country_code is not specified ?

This should do it, even if a database solution would be better.