How to translate data from MySQL with gettext?

Question

I would like to translate strings from MySQL using gettext php. E.g.

$string = $db->prepare('SELECT * FROM `cities`');
$string->execute();
$value = $string->fetch(PDO::FETCH_ASSOC);

foreach($value as $k => $v) {
  $array[$v['id']] = _($v['name']);
}

and this is where _($v['name']) I wanna use gettext

msgid "Some City"
msgstr "Some City Other Lang"

But this method does not work. I would like another solution

Answer 1

does not work

That means (please correct me if I'm wrong) that string extractors aren't able to find the text to translate when parsing source code and possibly even throw error messages. That's to expect, since your strings are not in the source code at all—they are in an out of reach relational database.

The basic problem is of course that gettext isn't designed to work that way.

I've sometimes addressed this use case with the following method:

1. Write a helper script to fetch strings from database and dump them into a regular PHP file that looks like this:

   <?php // db-strings.php
   // Autogenerated file, do not edit
   _('Bar');
   _('Foo');
   

   This auto-generated file is not meant to be executed. It's just to be used by Poedit or whatever extractor you'll be using. The helper script would be something like:

   <?php // dump-db-strings.php
   
   $fp = fopen('db-strings.php', 'wb');
   fwrite($fp, "<?php\n");
   fwrite($fp, "// Autogenerated file, do not edit\n");
   
   /** @var PDO $db*/
   $stmt = $db->prepare('
       SELECT name AS text
       FROM cities
       UNION
       SELECT some_other_column AS text
       FROM some_other_table
       ORDER BY text
   ');
   $stmt->execute();
   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       fwrite($fp, sprintf("_('%s');\n", addcslashes($row['text'])));
   }
   fclose($fp);
   

2. Add the helper script (dump-db-strings.php) to your build system, so db-strings.php remains updated.

3. Create a custom function to act as gettext wrapper so you no longer get extractor error messages, e.g.:

   // Untested!
   function _db($string) {
       return ${'_'}($string);
   }
   
   foreach($value as $k => $v) {
       $array[$v['id']] = _db($v['name']);
   }
   

Now, when you launch Poedit it'll scan db-strings.php, it will find the actual strings and it'll include then in the *.po catalogue. On runtime, _db($v['name']) will execute as e.g. _db('Foo') and gettext will already find Foo in the *.mo compiled resource.

Please note this method is mostly meant for strings you control (i.e. they're fixed texts that only developers update), what really makes sense since you also need to obtain the actual translations. If your text is generated by users themselves without your intervention, you'd better find an alternative to gettext.