Localize output of SQL query

Question

I make a query directly on the database like this:

SELECT title,id, DATE_FORMAT(publish_up, "%d %M %Y") AS 'GEORGE' FROM #__k2_items WHERE published=1 AND trash=0 AND created_by=[id] ORDER BY publish_up DESC;

and then display it like that:

<span style="font-weight:normal;">[column_title]</span>&nbsp&nbsp&nbsp&nbsp<span>[column_GEORGE]</span>

The output is in English but I want it in my language.

This query is done within Community Builder with the help of a plugin.

I have search all the files and I have changed for example "January" with my countries word but with no result.

Is it a localize thing of mysql database?How can I fix that?

Thank you

Answer 1

If we're not talking about many values that you need to translate, then you can have a file called gr_GR.plg_content_your_plugin.ini in your languages/gr_GR folder (assuming the language that you want to translate is Greek). In that file, you can have something like:

VALUE_GEORGE=George in Greek
VALUE_JOHN=John in Greek

And then, when you want to print the translate value of a variable anywhere, you can have something like:

<?php echo JText::_('VALUE_GEORGE'); ?>

Hope this helps!

Answer 2

Use HTMLHelper or Date API to format the date. Month and day names will be translated using strings from global language files (e.g. language/el-GR/el-GR.ini).

use Joomla\CMS\HTML\HTMLHelper;

echo HTMLHelper::_('date', $dateFromDb, 'd M Y');

or

use Joomla\CMS\Date\Date;

$date = new Date($dateFromDb);
echo $date->format('d M Y');

Where $dateFromDb is the date retrieved from database. Note, since date is formatted in PHP, you can remove formatting from the query:

SELECT title,id,publish_up AS 'GEORGE'