I am trying to build an English and French website by pulling the information from a MySQL table but I am stuck. I think that kind of query is too advance for me. My idea is having a table with both language translated and assign them with an ID.
Here's the table lang
+--------+-------------------+----------------------+ | id | English | French | +--------+-------------------+----------------------+ | 1 | Verbal Warning | Avis verbal | | 2 | Written Warning | Avis écrit | | 3 | Evaluation | Évaluation | | 4 | Other (specify) | Autres (spécifiez) | +--------+-------------------+----------------------+
Then I have another table that people inputs 'Topic' into the database. So when I switch the page to French the content of the table will display in French.
+-----------+---------+ | EMP_ID | Topic | +-----------+---------+ | 845689 | 4 | | 185648 | 3 | | 485497 | 1 | | 416798 | 2 | +-----------+---------+
I want the ouput to be this in a table when we're on the English page
+-----------+------------------+ | EMP_ID | Topic | +-----------+------------------+ | 845689 | Other (specify) | | 185648 | Evaluation | | 485497 | Verbal Warning | | 416798 | Written Warning | +-----------+------------------+
then this when it's the French page is selected.
+-----------+---------------------+ | EMP_ID | Topic | +-----------+---------------------+ | 845689 | Autres (spécifiez) | | 185648 | Évaluation | | 485497 | Avis verbal | | 416798 | Avis écrit | +-----------+---------------------+
Is there a way to make it work or there's easier ways to display
There is no reason to keep lang schema in DB, try to include files with your lang instead.
Well you shouldn't change the table content each time someone loads the page that would be a performance catastrophe ;-) But you can make a SQL Join Query.
If you are on the french page you do
SELECT l.French FROM topic AS t
JOIN lang AS l ON t.Topic = l.id
WHERE t.EMP_ID = $emp_id;
and on the english you do
SELECT l.English FROM topic AS t
JOIN lang AS l ON t.Topic = l.id
WHERE t.EMP_ID = $emp_id;
I think the easiest way will be to create a view per language:
For French:
CREATE VIEW empl_fr
AS
SELECT
emp.EMP_ID as EMP_ID
, lang.French as Topic
FROM employee emp, language lang
WHERE emp.Topic = lang.id
For English:
CREATE VIEW empl_en
AS
SELECT
emp.EMP_ID as EMP_ID
, lang.English as Topic
FROM employee emp, language lang
WHERE emp.Topic = lang.id
and then you can query your topics like:
SELECT * FROM empl_fr
or
SELECT * FROM empl_en
The more suffisticated way will be to create a view with both languages and query your view with a language parameter
To make your site extensible and easy to support future, additional languages; I’d suggest using MySQL in its intended fashion and create a relational schema.
If you have topics, and topic titles are to be translatable, then you’ll need three tables: a topics table, a languages table, and a table that joins the two.
Your topics table is easy: you just need a primary key, and any other language-independent columns (such as created, modified etc). For the languages table, store again a primary key, the language name, and maybe an ISO short code for consistent naming/identification.
Finally, your join table (could be called languages_topics) is where the associations happen. You would have two foreign keys (topic_id and language_id) and another column that actually holds the localised value.
For example, if English is language ID 1 and French is language ID 2, then your table could look as follows:
+----------+-------------+----------------+
| topic_id | language_id | value |
+----------+-------------+----------------+
| 1 | 1 | Verbal Warning |
| 1 | 2 | Avis verbal |
+----------+-------------+----------------+
As you can see, a topic can have multiple rows in this table, with a record per language to offer up the translations.
I know this maybe doesn’t exactly answer your question, but should set you on the right path on how best to store your data. Languages can easily be added in the future without needing to modify your database schema.
As suggested in another answer, it would be better to store your language strings in files, and use Javascript to load them. I would suggest to use i18next, which is a very useful and easy-to-use JS library. It would really be simple :
/* en.json */
{
"home":{
"title":"Hello, world !",
"welcomeMessage":"Welcome to my great website !"
}
}
/* fr.json */
{
"home":{
"title":"Bonjour !",
"welcomeMessage":"Bienvenue sur mon superbe site web"
}
}
In your html code :
<!-- head and other stuffs... -->
<script type="text/javascript" src="i18next.js"></script>
<body onload="translate()">
<h1 i18n-data="home.title"></h1>
<p i18n-data="home.welcomeMessage"></p>
</body>
In your functions JS file :
function translate() {
i18n.init({
resGetPath:'en.json',
getAsync:false
}).done(function(){
$('[data-i18n]').i18n();
});
}
This way, your website will run faster (less database calls), and it will be easier to add/update some strings. Hope it will help :)