Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multilanguage Database Design in MySQL

Posted on 2016-10-31
5
Medium Priority
?
682 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 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.
https://www.experts-exchange.com/articles/8910/A-Polyglot-Web-Site-in-PHP.html
0
 
LVL 60

Accepted Solution

by:
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.
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 111

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 60

Expert Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

916 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