Efficient way to translate products in database

Question

I'm looking for the fast and efficient way to translate all products in osCommerce by phrases.

Currently, there are several language files where all phrases are being kept and then it simply replaces updates fields. Annoying thing is that its old system and it isn't using UTF-8 encoding so whenever I want to translate something I have to open a file, change the encoding, add translation and then encode it back to the previous encoding. Also, it determines which phrases by line number so for example if I would add Russian phrase but not Spanish whole translations would mess up.

What I came up with for now is to put everything into the database for easy access. There are two tables:

1. Main language phrases: translate_id, phrase

2. Other languages: translate_id, translation, language_id

include('mysql.php'); set_time_limit(600);

$query=$db->prepare("SELECT phrase, translation, language_id FROM phrases AS f
LEFT JOIN translations AS v
ON f.translate_id = v.translate_id
WHERE v.language_id=9"); 
$query->execute(); 
$result = $query -> fetchAll();

for($n = 0; $n < count($result); $n++) {
    $phrase = htmlspecialchars($result[$n]['phrase']);
    $translation = htmlspecialchars($result[$n]['translation']);
    $query=$db->prepare('
    UPDATE products_description AS pd 
    LEFT JOIN products as p
    ON p.products_id = pd.products_id
    SET pd.products_name = REPLACE(pd.products_name, "'. $phrase .'","'. $translation .'")
    WHERE language_id = 9 AND p.products_status = 1
    '); 
$query->execute();

}

This is what I came up with so far. For some reason, it doesn't replace anything (something wrong somewhere) but the biggest issue is I'm testing it with only one language and it even slower than files based translation.

How to make it as quick as possible? What is the best way to do it? Maybe there's some kind of class or something?

Answer 1

Can you specify your osCommerce version?

I'm looking for the fast and efficient way to translate all products in osCommerce by phrases.

Actually product description translations are on products_description database table: Considering that, I do not quite understand what you lack, what your need is. What's the meaning of "by phrases"?

Moreover, admin side offers a form to fill in translations which URL is like that:

http://[you host]/catalog/admin/categories.php?cPath=55_129&pID=1000&action=new_product

This page:

• Use the internal enconding of your osC version
• It's a friendly interface to edit product descriptions
• Use de database, not files.