0

What I would like is to store every word / sentence / ... in a database tabel like so:

Table name:

translate

columns:

id, var, dutch, english, french

Example:

'1', 'titleOne', 'Hallo Wereld!', 'Hello World!', 'Bonjour le monde!'

What I would like is to print the variable for example at the title section:

<?php
echo $titleOne;
?>

Since there will be hundreds of lines I do not want to set $titleOne = $row['titleOne'] for every line.

Can someone please help me out with a nice query, pull, fetch, array, loop, ... however you call this? (or a nice alternative way is also good!)

This is plain PHP, no frameworks are used.

PS: I am not a PHP expert at all so please try to keep it simple :-)!

Thanks a lot!

  • Are you using any Framework? OR coding in the core PHP? – Rohit.007 May 14 '18 at 16:37
  • What contain a typical `$row` array? – Syscall May 14 '18 at 17:14
  • I use plain PHP, no frameworks. –  May 14 '18 at 17:33
  • 2
    You have the right idea about using translation keys but don't use a single column for each language. You don't want to have to modify table structure each time out add/drop a language. There are many ways to implement a multi-language site. Here's a simple method I use for small/medium sized sites - https://stackoverflow.com/a/49758067/296555. If you want more features for i18n then look into a package from a framework - Symfony's is good https://symfony.com/doc/current/best_practices/i18n.html. – waterloomatt May 14 '18 at 17:46
  • 1
    Echoing @waterloomatt Stop now with the design you are using and take a step back on this `Translate` table. It should be, at the very least `id | var | language | translation`. Where an entry will look like `1 | 'titleOne' | 'english' | 'Hello World'`. Then when you query it, you do `SELECT var, translation FROM translate WHERE language = 'english';` Now you can parameterize your query to this table for a language instead of switching out columns in your code which will save you from a potential sql injection vector and make it so your app can scale without constantly changing the DB schema. – JNevill May 14 '18 at 17:49
  • That doesn't answer your question though. Load up all your `var` and translations from a query into an array and then just pull up the element you need for each label or whatever you are populating. – JNevill May 14 '18 at 17:52
  • @JNVevill What exactly would be the difference between `"SELECT var, '$lang' FROM translate"` and `"SELECT var, translation FROM translate WHERE language = '$lang'"`? And this 'load up all your var .... ', is exactly my question on **how to do this** :-)! –  May 14 '18 at 18:09
  • 1
    @KevinGoethals - you can but shouldn't be embedding variables directly into your SQL statements (SQL Injection attacks). Instead, you should be using prepared statements to bind variables into your query. However, prepared statements do not support, nor should they, column names as placeholders. See more here - https://stackoverflow.com/q/182287/296555. It is the job of the designer to understand this and to use a design that works well. These restrictions are there for a reason. Sounds like you're ready to dive into SQL queries. Start with PDO - https://phpdelusions.net/pdo – waterloomatt May 15 '18 at 12:05

2 Answers2

1

I 2nd the advice given by others about your table structure, but to answer your question you can use extract

$row = array(
    col_1 => 'a', 
    col_2 => 'b', 
    col_3 => 'c' 
)

extract($row);


// results in:

//   $col_1  assigned value 'a'
//   $col_2  assigned value 'b'
//   $col_3  assigned value 'c'
andrew
  • 9,313
  • 7
  • 30
  • 61
0

To answer your question directly. You could query your current setup as follows:

-- For Dutch (pseudocode)
$query = 'SELECT var, dutch FROM translate';

$translations = array();   
while loop ($row = $query) {
    $translations['var'] = $row['dutch'];
}

// Then, to access translations you would:
echo $translations['titleOne'];

BUT you don't want to do this. This table structure will lead you down a path of regret. Heed our warnings. There are many ways to go about this and you've chosen to go the SQL route so here are some tips.

Change your table structure so you don't have to add a new column each time you add a new language.

Table: languages (Add a new row to support a new language)

id, locale, language

1, en_CA, English
2, en_US, English
3, en_UK, English
4, es_ES, Spanish
5, es_MX, Spanish
...

Table: translations (add a new row to add a translation)

id, locale, translation_key, translation

1, en_CA, 'hello', 'Hi from Canada'
2, en_US, 'hello', 'Hi from the US'
3, en_UK, 'hello', 'Hi from the UK'
4, es_ES, 'hello', 'Hi from Spain'
5, es_MX, 'hello', 'Hi from Mexico'
...

translations.locale should be a foreign key pointing back to languages but I'm not sure what your SQL level is so leaving it as clear as I know how.

I assume you can figure out how to query your database from PHP. What you want to do is:

  • figure out which locale to use. This will be one of the trickiest parts. Ex. should you be using the one specified in the URL, the session, the browser setting? What happens if none of those is specified? What is the fallback locale? See my getLanguage() function in https://stackoverflow.com/a/49758067/296555 for some ideas.

  • Pull all translations for that locale and store them in memory. Here is a basic query to pull all translations for a given locale. 

    <?pseudocode 
$locale = 'en_CA';    

// Find all translations for a given locale
$dbQuery = 'SELECT translation_key, translation
FROM translations
WHERE locale = :locale';
;

  • Put those entries you just pulled in to memory. 

    <?pseudocode 
$translations = array();
while loop ($row = $dbQuery) {
    $translations[$row['translation_key']] = $row['translation'];
}

  • Throughout your application, you can now access translations via that array.

    echo $translations['hello'];

You'll want to do this high up in your application in a part that is called on every request. Once you have this going you will want to start optimizing. You could store the database results to the session so you don't have to query the DB on each page load or implement a more robust form of caching.

In all honesty, I think you have the right idea but the devil is in the details or so they say. Read the link that I pointed to. It isn't perfect but it should help you get to a workable solution relatively quickly.

waterloomatt
  • 3,662
  • 1
  • 19
  • 25