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 58

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 58

Expert Comment

by:Julian Hansen
ID: 41870050
You are welcome.

Featured Post

Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

628 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