?
Solved

Multi-country website coding

Posted on 2009-12-19
10
Medium Priority
?
559 Views
Last Modified: 2012-05-08
Hey guys,

I have a client who wants a website to sell products but the products would be different depending on whether the user was in, e.g. USA, UK, Africa.

How would this be done coding wise? Could you use one website but just get products from a different database depending on the country, or would you have t have say three separate websites for each country and update them separately?

Could smoeone point me in the direction to get some scope and ideas on how this would be done?

Cheers
0
Comment
Question by:andoneknight
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 1200 total points
ID: 26086644
This can be quite a complicated issue. Look up internationalisation localisation. Given the length of those words they are normally shortened to I18N and L10N and there are links on Wikipedia which is a useful background

http://en.wikipedia.org/wiki/Internationalization_and_localization

In practice you need to design it in to your database. Start with a country table

Country ( country_id, country_name, country_code, currency, etc.... )

and then anything which be multicountry (like a product) can needs a record storing with the country code, so instead of

product ( product_id, name, price..... etc )

you would design

product( product_id, country_id, name, price, ....etc )

this allows you to host products in multiple countries and is a common way of doing things especially if there are slight differences between products. If you have one product that you sold across various countries then you could use a cross reference table to match a product up against the country it is sold in.

product_sells_in ( country_id, product_id )

If there was a variation in price then that could be stored in the cross reference

product_sells_in ( country_id, product_id, local_price )


Another issue is the headings on the webpage. Normally you would display a heading or text like so

echo "Product name"

What you need to do is to have a table or set of arrays keyed by either country code or country ID from your country table and an id for the string, so you might have a table like this

webpageText ( country_code, heading_name, heading_text )

which would have entries like

"EN", "productName", "Enter the product name"
"FR", "productName", "Entrez le nom du produit"
"DE", "productName", "Geben Sie den Produktnamen"

and then you would write a little function that given the country code and keyname would return the approriate string

echo getHeading("EN", "productName");

There is a lot to it. Good luck.
0
 
LVL 5

Author Comment

by:andoneknight
ID: 26087116
Thanks for your response. Could you explain what the "cross reference" table would be? If the product table itself contains a product and country id, amongst other fields, then why would you want another "cross-reference" table with just those details in?

In terms of the language, that isn't a problem in this case,  it was more building an international site and how datbases etc would be structured etc... (which youve answered) :)

In terms of having a shopping cart, how would you suggest starting this - again any documentation much appreciated. Would the fact that each product has an id be sufficient to make the cart work?
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 400 total points
ID: 26087299
You may want to know the country codes.  This should help.  Best regards, ~Ray
<?php // RAY_country_codes.php
error_reporting(E_ALL);

class CountryNames
{

// DECLARE THE VARIABLES
private $can; // COUNTRY ABBREVIATIONS POINT TO NAMES
private $cna; // COUNTRY NAMES POINT TO ABBREVIATIONS

// DECLARE THE CONSTRUCTOR TO LOAD THE ARRAY
function __construct()
{
	if (!isset($can))
	{
		$can = array();
		$this->can["af"] = "Afghanistan";
		$this->can["ax"] = "&Aring;land Islands";
		$this->can["al"] = "Albania";
		$this->can["dz"] = "Algeria";
		$this->can["as"] = "American Samoa";
		$this->can["ad"] = "Andorra";
		$this->can["ao"] = "Angola";
		$this->can["al"] = "Anguilla";
		$this->can["ag"] = "Antigua and Barbuda";
		$this->can["ar"] = "Argentina";
		$this->can["am"] = "Armenia";
		$this->can["aw"] = "Aruba";
		$this->can["au"] = "Australia";
		$this->can["at"] = "Austria";
		$this->can["az"] = "Azerbaijan";
		$this->can["bs"] = "Bahamas";
		$this->can["bh"] = "Bahrain";
		$this->can["bd"] = "Bangladesh";
		$this->can["bb"] = "Barbados";
		$this->can["by"] = "Belarus";
		$this->can["be"] = "Belgium";
		$this->can["bz"] = "Belize";
		$this->can["bj"] = "Benin";
		$this->can["bm"] = "Bermuda";
		$this->can["bt"] = "Bhutan";
		$this->can["bo"] = "Bolivia";
		$this->can["ba"] = "Bosnia and Herzegovina";
		$this->can["bw"] = "Botswana";
		$this->can["bv"] = "Bouvet Island";
		$this->can["br"] = "Brazil";
		$this->can["io"] = "British Indian Ocean territory";
		$this->can["bn"] = "Brunei Darussalam";
		$this->can["bg"] = "Bulgaria";
		$this->can["bf"] = "Burkina Aso";
		$this->can["bi"] = "Burundi";
		$this->can["kh"] = "Cambodia";
		$this->can["cm"] = "Cameroon";
		$this->can["ca"] = "Canada";
		$this->can["bs"] = "Cape Verde";
		$this->can["cv"] = "Cayman Islands";
		$this->can["cf"] = "Central African Republic";
		$this->can["td"] = "Chad";
		$this->can["cl"] = "Chile";
		$this->can["cn"] = "China";
		$this->can["cx"] = "Christmas Island";
		$this->can["cc"] = "Cocos (Keeling) Islands";
		$this->can["co"] = "Colombia";
		$this->can["km"] = "Comoros";
		$this->can["cg"] = "Congo";
		$this->can["cd"] = "Congo, Democratic Republic";
		$this->can["ck"] = "Cook Islands";
		$this->can["cr"] = "Costa Rica";
		$this->can["ci"] = "C&ocirc;te d'Ivoire (Ivory Coast)";
		$this->can["hr"] = "Croatia (Hrvatska)";
		$this->can["cu"] = "Cuba";
		$this->can["cy"] = "Cyprus";
		$this->can["cz"] = "Czech Republic";
		$this->can["dk"] = "Denmark";
		$this->can["dj"] = "Djibouti";
		$this->can["dm"] = "Dominica";
		$this->can["do"] = "Dominican Republic";
		$this->can["ec"] = "Ecuador";
		$this->can["eg"] = "Egypt";
		$this->can["sv"] = "El Salvador";
		$this->can["gq"] = "Equatorial Guinea";
		$this->can["er"] = "Eritrea";
		$this->can["ee"] = "Estonia";
		$this->can["et"] = "Ethiopia";
		$this->can["fk"] = "Falkland Islands";
		$this->can["fo"] = "Faroe Islands";
		$this->can["fj"] = "Fiji";
		$this->can["fi"] = "Finland";
		$this->can["fr"] = "France";
		$this->can["pf"] = "French Polynesia";
		$this->can["tf"] = "French Southern Territories";
		$this->can["ga"] = "Gabon";
		$this->can["gm"] = "Gambia";
		$this->can["ge"] = "Georgia";
		$this->can["de"] = "Germany";
		$this->can["gh"] = "Ghana";
		$this->can["gl"] = "Gibraltar";
		$this->can["gr"] = "Greece";
		$this->can["gl"] = "Greenland";
		$this->can["gd"] = "Grenada";
		$this->can["gp"] = "Guadeloupe";
		$this->can["gu"] = "Guam";
		$this->can["gt"] = "Guatemala";
		$this->can["gn"] = "Guinea";
		$this->can["gw"] = "Guinea-Bissau";
		$this->can["gy"] = "Guyana";
		$this->can["ht"] = "Haiti";
		$this->can["hm"] = "Heard and McDonald Islands";
		$this->can["va"] = "Honduras";
		$this->can["hk"] = "Hong Kong";
		$this->can["hu"] = "Hungary";
		$this->can["is"] = "Iceland";
		$this->can["in"] = "India";
		$this->can["id"] = "Indonesia";
		$this->can["ir"] = "Iran";
		$this->can["iq"] = "Iraq";
		$this->can["ie"] = "Ireland";
		$this->can["il"] = "Israel";
		$this->can["it"] = "Italy";
		$this->can["jm"] = "Jamaica";
		$this->can["jp"] = "Japan";
		$this->can["jo"] = "Jordan";
		$this->can["kz"] = "Kazakhstan";
		$this->can["ke"] = "Kenya";
		$this->can["ki"] = "Kiribati";
		$this->can["kp"] = "Korea (north)";
		$this->can["kr"] = "Korea (south)";
		$this->can["kw"] = "Kuwait";
		$this->can["kg"] = "Kyrgyzstan";
		$this->can["la"] = "Lao People's Democratic Republic";
		$this->can["lv"] = "Latvia";
		$this->can["lb"] = "Lebanon";
		$this->can["ls"] = "Lesotho";
		$this->can["lr"] = "Liberia";
		$this->can["ly"] = "Libyan Arab Jamahiriya";
		$this->can["li"] = "Liechtenstein";
		$this->can["lt"] = "Lithuania";
		$this->can["lu"] = "Luxembourg";
		$this->can["mo"] = ">Macao";
		$this->can["mk"] = "Macedonia (FYROM)";
		$this->can["mg"] = "Madagascar";
		$this->can["mw"] = "Malawi";
		$this->can["my"] = "Malaysia";
		$this->can["mv"] = "Maldives";
		$this->can["ml"] = "Mali";
		$this->can["mt"] = "Malta";
		$this->can["mh"] = "Marshall Islands";
		$this->can["mq"] = "Martinique";
		$this->can["mr"] = "Mauritania";
		$this->can["mu"] = "Mauritius";
		$this->can["yt"] = "Mayotte";
		$this->can["mx"] = "Mexico";
		$this->can["fm"] = "Micronesia";
		$this->can["md"] = "Moldova";
		$this->can["mc"] = "Monaco";
		$this->can["mn"] = "Mongolia";
		$this->can["ms"] = "Montserrat";
		$this->can["ma"] = "Morocco";
		$this->can["mz"] = "Mozambique";
		$this->can["mm"] = "Myanmar";
		$this->can["na"] = "Namibia";
		$this->can["nr"] = "Nauru";
		$this->can["np"] = "Nepal";
		$this->can["nl"] = "Netherlands";
		$this->can["an"] = "Netherlands Antilles";
		$this->can["nc"] = "New Caledonia";
		$this->can["nz"] = "New Zealand";
		$this->can["ni"] = "Nicaragua";
		$this->can["ne"] = "Niger";
		$this->can["ng"] = "Nigeria";
		$this->can["nu"] = "Niue";
		$this->can["nf"] = "Norfolk Island";
		$this->can["mp"] = "Northern Mariana Islands";
		$this->can["no"] = "Norway";
		$this->can["om"] = "Oman";
		$this->can["pk"] = "Pakistan";
		$this->can["pw"] = "Palau";
		$this->can["ps"] = "Palestinian Territories";
		$this->can["pa"] = "Panama";
		$this->can["pg"] = "Papua New Guinea";
		$this->can["py"] = "Paraguay";
		$this->can["pe"] = "Peru";
		$this->can["ph"] = "Philippines";
		$this->can["pn"] = "Pitcairn";
		$this->can["pl"] = "Poland";
		$this->can["pt"] = "Portugal";
		$this->can["pr"] = "Puerto Rico";
		$this->can["qa"] = "Qatar";
		$this->can["re"] = "R&eacute;union";
		$this->can["ro"] = "Romania";
		$this->can["ru"] = "Russian Federation";
		$this->can["rw"] = "Rwanda";
		$this->can["sh"] = "Saint Helena";
		$this->can["kn"] = "Saint Kitts and Nevis";
		$this->can["lc"] = "Saint Lucia";
		$this->can["pm"] = "Saint Pierre and Miquelon";
		$this->can["vc"] = "Saint Vincent and the Grenadines";
		$this->can["ws"] = "Samoa";
		$this->can["sm"] = "San Marino";
		$this->can["st"] = "Sao Tome and Principe";
		$this->can["sa"] = "Saudi Arabia";
		$this->can["sn"] = "Senegal";
		$this->can["cs"] = "Serbia and Montenegro";
		$this->can["sc"] = "Seychelles";
		$this->can["sl"] = "Sierra Leone";
		$this->can["sg"] = "Singapore";
		$this->can["sk"] = "Slovakia";
		$this->can["sl"] = "Slovenia";
		$this->can["sb"] = "Solomon Islands";
		$this->can["so"] = "Somalia";
		$this->can["za"] = "South Africa";
		$this->can["gs"] = "South Georgia and the South Sandwich Islands";
		$this->can["es"] = "Spain";
		$this->can["lk"] = "Sri Lanka";
		$this->can["sd"] = "Sudan";
		$this->can["sr"] = "Suriname";
		$this->can["sj"] = "Svalbard and Jan Mayen Islands";
		$this->can["sz"] = "Swaziland";
		$this->can["se"] = "Sweden";
		$this->can["ch"] = "Switzerland";
		$this->can["sy"] = "Syria";
		$this->can["tw"] = "Taiwan";
		$this->can["tj"] = "Tajikistan";
		$this->can["tz"] = "Tanzania";
		$this->can["th"] = "Thailand";
		$this->can["tg"] = "Togo";
		$this->can["tk"] = "Tokelau";
		$this->can["to"] = "Tonga";
		$this->can["tt"] = "Trinidad and Tobago";
		$this->can["tn"] = "Tunisia";
		$this->can["tr"] = "Turkey";
		$this->can["tm"] = "Turkmenistan";
		$this->can["tc"] = "Turks and Caicos Islands";
		$this->can["tv"] = "Tuvalu";
		$this->can["ug"] = "Uganda";
		$this->can["ua"] = "Ukraine";
		$this->can["ae"] = "United Arab Emirates";
		$this->can["gb"] = "United Kingdom";
		$this->can["us"] = "United States of America";
		$this->can["uy"] = "Uruguay";
		$this->can["uz"] = "Uzbekistan";
		$this->can["vu"] = "Vanuatu";
		$this->can["ve"] = "Venezuela";
		$this->can["vn"] = "Vietnam";
		$this->can["vg"] = "Virgin Islands (British)";
		$this->can["vi"] = "Virgin Islands (US)";
		$this->can["wf"] = "Wallis and Futuna Islands";
		$this->can["eh"] = "Western Sahara";
		$this->can["ye"] = "Yemen";
		$this->can["zm"] = "Zambia";
		$this->can["zw"] = "Zimbabwe";
	}

	if (!isset($this->cna))
	{
		$this->cna = array_flip($this->can);
	}

}

// FUNCTION TO GET NAME FROM ABBREVIATION
public function get_country_name($abbr)
{
	$abbr = strtolower($abbr);
	if (!isset($this->can["$abbr"])) return FALSE;
	return $this->can["$abbr"];
}

// FUNCTION TO GET ABBREVIATION FROM NAME
public function get_country_abbr($name)
{
	if (!isset($this->cna["$name"])) return FALSE;
	return strtoupper($this->cna["$name"]);
}

// END OF CLASS
}

// TEST DATA
echo "<pre>\n";
$ca   = new CountryNames;
$abbr = $ca->get_country_abbr("United States of America");
var_dump($abbr);

$name = $ca->get_country_name("GS");
var_dump($name);

$xerr = $ca->get_country_name("XX");
var_dump($xerr);

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26087306
The point of a cross reference table is to allow you to relate different items in a flexible way. For example, say I have one product that I want to sell in 3 countries and I will be using the same language and the same price and the same currency

so I have a record like this in the product table

product_id=1234, name=Left handed widget, price=12.34

and I am going to sell it in UK, France and Germany (which are allin my country talbe) then my cross reference would have

product_id, country
1234,UK
1234,DE
1234,FR

If I then change the price from 12.34 to 13.00 then I only need to change the product record - 1 change - for all three countries. It is also flexible, say I want to sell it in the US and Canada, I just add two more records to the cross reference

1234,UK
1234,DE
1234,FR
1234,US
1234,CA

and the job is done. Now let us say that the French and Germans insist on local prices in Euros. By adding a extra "override" field you can determine that this product has to be sold in EUROs

product_id, country, override_currency
1234,UK
1234,DE,EURO
1234,FR,EURO
1234,US
1234,CA

In practice currency things like this would likely be done at the "country record" level but I am trying to illustrate how a cross reference file can add enormous flexibility to an application and database.

Finally if you are going to have an eCommerce shop then don't waste time writing one - use either ZenCart or Magento. Both are very well supported and extremely flexible and ZenCart does mutilple currencies as well. I'm sure that magento does as well, but I know ZenCart a lot better.

I think I might write an article on cross-refs as it does pop up on here fairly often.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26087333
Actually, re-reading that after I posted it, I realise that I have missed out a MAJOR advantage of the cross-reference. Searching.

Even with that very simple cross reference I can answer the following questions with simple queries

1. How many countries do we sell product 1234 in?

SELECT count(country) FROM  crossRef WHERE product_id = 1234

2. What are the countries do we sell product 1234 in?

SELECT countryName FROM  crossRef
    INNER JOIN countryTable on country=country_code
    WHERE product_id = 1234


3. How many countries have an override currency?

SELECT count(country) FROM  crossRef WHERE product_id = 1234 and override_currency <> ''


4. What products do we sell in france?

SELECT product_id, product_name
     FROM crossRef as c
     INNER JOIN products as p on c.product_id = p.product_id
     WHERE country='FR'

and so on...
0
 
LVL 5

Author Comment

by:andoneknight
ID: 26087394
Ok. Many many thanks for your explanation. In summary:

You might construct this using a "products" table and a "country" table.

The "products" table would have in it, e.g. product id, name, price, size, etc..etc.. (without a country code)
The "country" table would have in it, e.g. product id, country code.

This means that the products are independant of the country until they are stored in the cross reference table where each product is assigned a country code dependant on where it is sold. That way, if anyone is using the site in FRANCE, the query would pick out all the products in the Cross reference table where the country code was for france, and then join the product data onto the results.

It confused me as in your earlier post you said
---------------
and then anything which be multicountry (like a product) can needs a record storing with the country code, so instead of

product ( product_id, name, price..... etc )

you would design

product( product_id, country_id, name, price, ....etc )
---------------
which confused me as you were still putting the country_id in with all of the product details, but i get it now!


Ray - many thanks for that country list!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26087603
You're welcome!  Good luck with your project, ~Ray
0
 
LVL 6

Assisted Solution

by:basic612
basic612 earned 400 total points
ID: 26090536
I see there has been a lot of talk specific to custom coding a solution. I wonder however why you would be wanting to re-invent the wheel. Have you thought of building your solution on an existing framework that already has features such as those you are talking about, eg:

http://www.magentocommerce.com/product/features/site-management/view

- Dan
0
 
LVL 6

Expert Comment

by:basic612
ID: 26090542
0
 
LVL 5

Author Comment

by:andoneknight
ID: 26092782
Thankyou - will take a look at those two links! :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question