Multilanguage Database Design in MySQL

Posted on 2016-10-31
Last Modified: 2016-11-02
Hi to all,

I am new with PHP language and trying to translate a content of database column to another column in different languages.  I found this site below, seems very useful:

I was practicing the easiest approach first approach 1. Column Approach.   as instructed I created the Database andadded PHP file as shown below:

CREATE TABLE app_product (
  `date_created` datetime NOT NULL,
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `title_en` varchar(255) NOT NULL,
  `title_es` varchar(255) NOT NULL,
  `title_fr` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)

Open in new window

// Retrieve titles for all languages
$sql = "SELECT * FROM `app_product` WHERE 1";
if($result = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "English: ".$row["title_en"]."<br>";
        echo "Spanish: ".$row["title_es"]."<br>";
        echo "French: ".$row["title_fr"]."<br>";

// Retrieve appropriate title according to the chosen language in the system
$sql = "SELECT `title_".$_SESSION['current_language']."` as `title`
        FROM `app_product`";
if($result = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];


The Bold text is where I am stuck.  I don't understand it and don't know how to create PHP code or drop down menu or...etc to select a language I want so I can fetch the data?

thanks in advance.
Question by:Rawand Amin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 110

Expert Comment

by:Ray Paseur
ID: 41867013
We have an article here at E-E that shows the general design pattern for a multi-lingual web site.  Please read it over and post back if you still have specific questions.
LVL 57

Accepted Solution

Julian Hansen earned 500 total points
ID: 41867025
Let's break the statement down
$sql = "SELECT `title_".$_SESSION['current_language']."` as `title`

Open in new window

$_SESSION is a global array that stores session data for a particular visitor. You would populate this with information based on user interaction. For instance if they perform a login to your site you might put a user ID in there - in you case if they click on a link to say what language they prefer you might store that in the session.

You can read more about PHP sessions here

If you use sessions then every page on which you need to get / set SESSION data must have the following at the top.

Open in new window

You can then set a SESSION variable
$_SESSION['current_language'] = 'en';

Open in new window

Or retrieve it
$currentlanguage = isset($_SESSION['current_language']) ? $_SESSION['current_language']: $default_language;

Open in new window

The first line of the query concatentating whatever is in the $_SESSION['current_language'] to the string 'title_'
If you look at your DB definition you have three columns.

Open in new window

Based on this we expect that $_SESSION['current_language'] contains the strins 'en','es' or 'fr' - by concatenating the strings we create a column name.
FROM `app_product`";

Open in new window

Just means from the app_product table.

The next 3 lines are setting up and running the SQL query and fetching the results.

Post back if you need more than the above.
LVL 110

Expert Comment

by:Ray Paseur
ID: 41867038
I should probably also share these links, too.

If you're new to PHP:

How the PHP session works:

And to be fair, a multi-language web site is a fairly advanced topic.  If you find yourself stuck, you may want to consider creating a project in Gigs.

I would be disinclined to store all of the translations in a single database; a simpler design would store the language-specific information in separate language-specific databases.  Each of these databases would be strictly parallel to the other databases, using identical table and column names.  The same PHP code set could serve any language by just connecting to the correct database.  A little more discussion and some code samples can be found in the Polyglot article.

Author Closing Comment

by:Rawand Amin
ID: 41869951
Thanks you so much, great detail
LVL 57

Expert Comment

by:Julian Hansen
ID: 41870050
You are welcome.

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

751 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