Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Multilanguage Database Design in MySQL

Posted on 2016-10-31
Medium Priority
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 111

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 59

Accepted Solution

Julian Hansen earned 2000 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 111

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 59

Expert Comment

by:Julian Hansen
ID: 41870050
You are welcome.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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