Skip to content
Advertisement

Website language translation in PHP (SQL)

i want to implement in my website a language translation. For this is created a table “translations” in my database:

id | de    | en    | other languages    
1  | Hallo | Hello | ...

Based on this i have added the following query into my header:

 if ($user['language'] == 'de') {
        // Select prepared PDO 
        $statement = $pdo->prepare("SELECT id, de FROM translations");
        $result = $statement->execute(array());
        $text = $statement->fetch();
    }

if .... other languages

I want to show the text translation based on the user language. I am wondering if there is a simple php echo method i can use now. As explained above i have the translation in the table available but whta is the easiest method to echo it?

For example. Current state:

<h4 class="...">Hallo</h4>

New state with translation (of course its not working):

<h4 class="..."><?php echo $text['en'] ?></h4>

So what i am missing is a simple method to adress the id (row) to this text output which is in that example “1”.

As a possible method i found this example:

<?php
$age=array("Peter"=>"35");
echo "Peter is " . $age['Peter'] . " years old.";
?>

I think i can use that and it looks from text size as efficient. But i am not sure how to modify the query for my table above. Best would be i can do somethink like:

echo $text['1']

and it shows the text output: “Hallo“. And in case:

echo $text['2']

it will show “Hello“.

What do you think about this method? And can you help me please to modify my query to get this method working ?

Thanks a lot!

Advertisement

Answer

Based on your questions under my first answer i decided to add another solution – easier without functions etc.

Fetch all translations in single query with fetchAll:

$statement = $pdo->prepare("SELECT id, de, en FROM translations");
$result = $statement->execute();
$textsRaw = $statement->fetchAll();

Your $textsRaw will look like this:

$textsRaw = [ 
  ['id' => 1, 'de' => 'Hallo', 'en' => 'Hello'],
  ['id' => 2, 'de' => 'auf Wiederschauen', 'en' => 'Good bye'],
];

Now we transform $textsRaw into $texts with easier structure:

$texts = [];
foreach ($textsRaw as $text) {
  $texts[$text['id']] = $text[$user['language']];
}

Now $texts look like this:

$texts = [1 => 'Hello', '2' => 'Good bye'];

If you have this, then translating is as easy as doing:

echo $texts[1];
echo $texts[2];

If you access translation id that doesnt exist you will get normal php notice: PHP Notice: Undefined offset: 3 in ....php on line ...

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement