Creating multilingual website is an easy task, but sometimes it appears as a tricky task, in my opinion the reason is that task have a full cycle which starts from a specific point and ends to a specific point and Most of the tutorials focus on one of those points and ignore others, which makes learner lost when try to practice, in addition, most of those tutorials do not provide the full cycle as a code example.

So in this tutorial we will focus on that side, to give you a good understanding of the basis, and finally, we will practically show how to build a multi-language website with an example.

Multilingual SQL structure methods

Firstly, you should choose the proper database structure for you, I said that because every database structure has its pros and cons, so whatever you chose it was the best for you.

There were many database designs out there but we will discuss two common database methods for this task:

1- Column method

2- Multi-row method

1- Column method

This solution is the simplest structure, it basically creates an extra column for each text (every language) that should be translated (as an example may be a number of columns in your table, such as title, name, description etc.). Below the example for this table in MySQL.

Figure 1-1. Create column method table.

CREATE TABLE ci_news ( `id` int(10) NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `title_en` varchar(255) NOT NULL, `title_es` varchar(255) NOT NULL, `title_fr` varchar(255) NOT NULL, `desc_en` varchar(255) NOT NULL, `desc_es` varchar(255) NOT NULL, `desc_fr` varchar(255) NOT NULL, `image` varchar(255) NOT NULL PRIMARY KEY (`id`) );

Now, to make a query it is also simple enough. You may do it by automatically by selecting the exact columns along with the chosen language:

Figure 1-2. Usage of column method table

// Retrieve titles for all languages $sql = "SELECT * FROM `ci_news ` WHERE 1"; // Retrieve appropriate title according to the chosen language in the system $sql = "SELECT `title_".$_SESSION['current_language']."` as `title` FROM `ci_news`";

Pros:

· Simplicity - easy to implement

· Easy querying - no JOINs needed

· No duplications - doesn't have duplicate content (there is only one row for every record and only the language columns are duplicated)

Cons:

· Hard to maintain - works in an easy way for 2-3 languages, but it comes to be a really hard when you have many columns or many languages

· Hard to add a new language - adding new language requires schema changes for every table with Multi-language content

· Store empty space - if not all translations are required (e.g. at some places default language should always be used) it might cause redundant data or empty database fields

· Need to build the watch - what column you are working with depending on the language

2- Multi-row Method

This solution is like the one above, but instead of duplicating the content in columns it duplicated the rows. See this example for this table below to understand the method in MySQL.

Figure 2-1. Create multi-row approach table.

CREATE TABLE ci_news ( `ne_id` int(10) NOT NULL AUTO_INCREMENT, `ne_created` datetime NOT NULL, `ne_lang` varchar(2) NOT NULL, `ne_title` varchar(255) NOT NULL, `ne_desc` varchar(255) NOT NULL, `ne_img` varchar(255) NOT NULL PRIMARY KEY (`id`) );

Let’s check now, how we could build the query. Usually, the idea is to do it by automatically selecting the right rows in relation to the chosen language:

Figure 2-2. Usage of multi-row method table.

// Retrieve titles for all languages $sql = "SELECT * FROM `ci_news ` WHERE `id` = 1"; // Retrieve appropriate title according to the chosen language in the system $sql = "SELECT `ne_title`FROM `ci_news` WHERE `ne_lang` = '".$_SESSION[lang]."'";

Pros:

· Simplicity - easy to implement

· Easy querying - no JOINs required

Cons:

· Hard to maintain - each column that is not translated must be changed in all rows for each language. E.g. changing the image for single article requires repeating this process for all languages

· Hard to add a new language - requires repeating insertion operation for every language (duplicating the record for default language)

· Duplicate content - you will have a lot of duplicate content for all the columns that are not translated

Building multi-language website steps

In our practical example, we will use the first database structure method because it's easier than others to implement also easier to understand for beginners, in addition, we provide easy integration for Databtables jQuery plugin to admin list with many features (client-side pagination-sorting-filtering)

So follow these steps below and at the end of this tutorial you will learn how to build multi language website.

1- Add language column to your tables 2- Define languages in the route file 3- Add lang_switcher function to parent class 4- Define languages to admin (create-edit-list) 5- Add languages drop down menu to your site 6- Build SQL queries based on Lang session 7- Add languages files

1- Add language column to your tables

First, to define language for any record we should add new column specified to add languages code, it is popular to make it varchar with two characters because every language has a shortcode with two characters, you can see many languages codes from here

`ne_lang` varchar(2) NOT NULL,

In our example, it will be three codes (en for English), (or for French) and (es for Spanish).

2- Define languages in the route file

Add this lines to the file in this path application/routes.php

This step will tell route system when someone clicks at URL ended with these characters like this

Website.com/fr

Don’t run 404 page otherwise return him to the home page, because it's a right URL

// '/en' and '/fr' URIs -defining language characters to your website $route['^en/(.+)$'] = "$1"; $route['^fr/(.+)$'] = "$1"; $route['^es/(.+)$'] = "$1"; // '/en' and '/fr' URIs -> use default controller $route['^en$'] = $route['default_controller']; $route['^fr$'] = $route['default_controller']; $route['^es$'] = $route['default_controller'];

3- Add lang_switcher function to parent class

You should create main class to inherit from, usually named as my_parent.php and put it in core path like this core/my_parent.php and in this class put lang_switcher function which handles switching between languages

/** * return module language file */ protected function lang_switcher () { // Detect current language from URL and set it to session and changing default system language config if ($this->uri->segment(1) == 'en' || $this->uri->segment(1) == 'fr'|| $this->uri->segment(1) == 'es' ) { $this->session->set_userdata("lang", $this->uri->segment(1)); redirect($this->session->flashdata('redirectToCurrent')); } if ($this->session->userdata('lang') == "es") { $lang = "spanish"; $this->config->set_item('language',$lang); $this->session->set_userdata("lang",'es'); } elseif ($this->session->userdata('lang') == "fr") { $lang = "french"; $this->config->set_item('language',$lang); $this->session->set_userdata("lang",'fr'); }else { $lang = "english"; $this->config->set_item('language',$lang); $this->session->set_userdata("lang",'en'); } }

4- Define languages to admin (create-edit-list)

To have the ability to handle SQL queries based on language code you should first add additional input with Create and edit form, also to have the ability to distinguish and filter articles by the language you have to put an additional column in your admin list table.

Create

<div class="form-group"> <label for="lang" class="control-label">language</label> <select class="textBox sml rnd5" name="ne_lang"> <option value="en">English</option> <option value="fr">French</option> <option value="es">Spanish</option> </select> </div>

Edit

<div class="form-group"> <label for="lang" class="control-label">language</label> <select name="ne_lang"> <option value="en" <?php if ($news->ne_lang == "en") echo "selected='selected'";?> > English</option> <option value="fr" <?php if ($news->ne_lang == "fr") echo "selected='selected'";?> >French</option> <option value="es" <?php if ($news->ne_lang == "es") echo "selected='selected'";?> >Spanish</option> </select> </div>

List

<table> <thead> <tr> <th>Image</th> <th>Title</th> <th>language</th> <th>Created</th> </tr> </thead> <tbody> <tr> <td>> // add image url </td> <td><?= $ph['ne_lang'] ?></td> <td> // add date here</td> </tr> </tbody> </table>

5- Add languages drop down menu to your site

To give the visitors the ability to browse your site with a chosen language you have to add these languages on the front page like this

<ul class="dropdown-menu"> <li><a href="<?=base_url()?>en">English</a></li> <li><a href="<?=base_url()?>fr">French</a></li> <li><a href="<?=base_url()?>es">Spanish</a></li> </ul>

6- Build SQL queries based on Lang session

After visitors click the language URL, the route will detect language , return them to the front page then lang_switcher function will catch the shortcode and set the current language to lang session.

When this done, our model will set Lang session to $lang variable which we are using to retrieve data based on current language

public $lang = "en"; function __construct() { parent::__construct(); $this->lang = $this->session->userdata("lang"); } // get all news function get_all() { $this->db->where('ne_lang', $this->lang); $result = $this->db->get('ci_news'); return $result->result_array(); } function create($file) { $this->db->set('ne_img',$file); $this->db->set('ne_title', $this->input->post('ne_title')); $this->db->set('ne_desc', $this->input->post('ne_desc')); $this->db->set('ne_lang', $this->input->post('ne_lang')); $this->db->set('ne_created', time()); $this->db->insert('ci_news'); $id = $this->db->insert_id(); return $id; }

7- Add languages files

Remember lang_switcher function and what we did there?

Yes we changed default system language from config and beside changing lang session, the session used to retrieve queries based on language, so what about config language?

Changing config language will in order to provide application-specific error and other messages, or to translate core messages of the system into other languages. These translations or additional messages will create inside your application/language/ directory, with separate sub-directories for every language (for instance, ‘French’ or ‘Spanish’).

The CodeIgniter framework comes with a set of language files which provide translations for different languages, you may found in the CodeIgniter 3 languages Translations .

When CodeIgniter loads language files, it will load the one in system/language/ first and will then look for an override in your application/language/ directory.

But what if we have many languages directories on this path, what going to happen?

It will load the default language which Defined in application/config.php file.

So to support multiple languages in your application, you would provide folders inside your application/language/ directory for each of them,

The application/language/english/ directory would contain any additional language files needed by your application, for instance for translated strings or error messages.

It will look like this figure :

application/ language/ english/ error_messages_lang.php french/ ... email_lang.php error_messages_lang.php form_validation_lang.php … spanish/ ... email_lang.php error_messages_lang.php form_validation_lang.php

In our site, we provide three languages with their folders as you see screenshot above.

Conclusion

I hope you enjoyed learning,and if you have any question don't hesitate to ask

DOWNLOAD LINK