Solved

Multilanguage Database Design in MySQL

Posted on 2016-10-31
5
226 Views
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 (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `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


<?php
// 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.
0
Comment
Question by:Rawand Amin
  • 2
  • 2
5 Comments
 
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.
https://www.experts-exchange.com/articles/8910/A-Polyglot-Web-Site-in-PHP.html
0
 
LVL 56

Accepted Solution

by:
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.
session_start()

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.
title_en
title_es
title_fr

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.
0
 
LVL 110

Expert Comment

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

If you're new to PHP:
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html

How the PHP session works:
https://www.experts-exchange.com/articles/11909/PHP-Sessions-Simpler-Than-You-May-Think.html

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.
0
 

Author Closing Comment

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

Expert Comment

by:Julian Hansen
ID: 41870050
You are welcome.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 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