Review: Building dynamic web pages in distinct languages using the iptocountry MySQL table - PHP

In case you haven't had an opportunity to read the preceding article of the series, below I've reintroduced the two hands-on examples built in that tutorial. The article showed how to create multi-lingual web pages by using the already familiar "iptocountry" MySQL table. And now for the examples:

(example on building a 'product' web page section in different languages)

// define 'MySQL' class

class MySQL{

private $mysqli;

private $result;

public function __construct($host='localhost',$user='user',$password='password',$database='database'){

// connect to MySQL and select database

$this->mysqli=new mysqli($host,$user,$password,$database);

if(mysqli_connect_errno()){

throw new Exception('Error connecting to MySQL: '.$this->mysqli->error);

}

}

// run SQL query

public function query($query){

if(!$this->result=$this->mysqli->query($query)){

throw new Exception('Error running SQL query: '.$this->mysqli->error);

}

}

// fetch one row

public function fetchRow(){

while($row=$this->result->fetch_assoc()){

return $row;

}

return false;

}

}

try{

// connect to MySQL and select database

$db=new MySQL('host','user','password','database');

// get user IP address

$ip=sprintf('%u',ip2long($_SERVER['REMOTE_ADDR']));

// Map IP Address to country using 'iptocountry' database

$db->query("SELECT two_chars_country FROM iptocountry WHERE lower_bound <=$ip AND upper_bound >= $ip LIMIT 1");

// get row from database table

$row=$db->fetchRow();

// fetch contents from 'products' MySQL table based on 2-char country code

$db->query('SELECT id,name,description FROM products_'.$row['two_chars_country']);

while($row=$db->fetchRow()){

// display products list and provide a link to a details page

echo '<h2>Product :'.$row['name'].'</h2><p>Description :'.$row['description'].'</p>';

echo '<p><a href="details.php?id='.$row['id'].'">View product details</a></p>';

}

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

(example on building entire web pages in different languages)

// define 'MySQL' class

class MySQL{

private $mysqli;

private $result;

public function __construct($host='localhost',$user='user',$password='password',$database='database'){

// connect to MySQL and select database

$this->mysqli=new mysqli($host,$user,$password,$database);

if(mysqli_connect_errno()){

throw new Exception('Error connecting to MySQL: '.$this->mysqli->error);

}

}

// run SQL query

public function query($query){

if(!$this->result=$this->mysqli->query($query)){

throw new Exception('Error running SQL query: '.$this->mysqli->error);

}

}

// fetch one row

public function fetchRow(){

while($row=$this->result->fetch_assoc()){

return $row;

}

return false;

}

}

// this is the front controller

try{

// connect to MySQL and select database

$db=new MySQL('host','user','password','database');

// get user IP address

$ip=sprintf('%u',ip2long($_SERVER['REMOTE_ADDR']));

// Map IP Address to country using 'iptocountry' database

$db->query("SELECT three_chars_country FROM iptocountry WHERE lower_bound <=$ip AND upper_bound >= $ip LIMIT 1");

// get row from database table

$row=$db->fetchRow();

// get 3-char country code

$code=$row['three_chars_country'];

// generate web page depending on country code

require_once 'header_'.$code.'.php';

require_once 'body_'.$code.'.php';

require_once 'footer_'.$code.'.php';

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

As shown previously, the two code samples above demonstrate how to exploit the functionality of the "iptocountry" MySQL lookup table to build different kind of web applications. In the first case, the table in question is used to generate a fictional "products" page section in a multi-lingual fashion, while the last example illustrates how to build entire web pages by means of a simple front controller.

So far, so good. At this moment, hopefully you're familiar with the basics of building dynamic web documents using a MySQL mapping table and PHP. So what's next? Well, as I explained in the introduction, my goal here is to demonstrate how this MySQL table can be utilized in conjunction with the Code Igniter PHP framework to build a simple IP-to-country mapping application.

Thus, if you're a strong advocate of using the Model-View-Controller pattern, then the examples that I plan to develop in the next section will surely appeal to you.

Click on the link that appears below and keep reading.