What are best practices for handling language-specific data updates in a custom CMS using PHP?

When handling language-specific data updates in a custom CMS using PHP, it is best practice to store language translations in a separate table or file to keep the data organized and easily manageable. This allows for easy retrieval and updates of language-specific content without affecting the structure of the main data table. Additionally, using a language identifier in the data structure can help in efficiently querying and displaying the correct language content. 

// Example of storing language-specific data in a separate table

// Main data table structure
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT
);

// Language table structure
CREATE TABLE product_translations (
    id INT PRIMARY KEY,
    product_id INT,
    language_code VARCHAR(2),
    name VARCHAR(255),
    description TEXT
);

// Retrieve product data in a specific language
$product_id = 1;
$language_code = 'en';

$query = "SELECT p.id, COALESCE(pt.name, p.name) AS name, COALESCE(pt.description, p.description) AS description
          FROM products p
          LEFT JOIN product_translations pt ON p.id = pt.product_id AND pt.language_code = :language_code
          WHERE p.id = :product_id";

$stmt = $pdo->prepare($query);
$stmt->bindParam(':language_code', $language_code);
$stmt->bindParam(':product_id', $product_id);
$stmt->execute();

$product = $stmt->fetch(PDO::FETCH_ASSOC);

// Update product data in a specific language
$product_id = 1;
$language_code = 'en';
$name = 'New Product Name';
$description = 'New Product Description';

$query = "INSERT INTO product_translations (product_id, language_code, name, description)
          VALUES (:product_id, :language_code, :name, :description)
          ON DUPLICATE KEY UPDATE name = :name, description = :description";

$stmt = $pdo->prepare($query);
$stmt->bindParam(':product_id', $product_id);
$stmt->bindParam(':language_code', $language_code);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->execute();

Keywords

multilingual localization PHP database content management

Related Questions