普通PHP网站实现商品价格多货币实时转换方案咨询

Hey there! Since you're refactoring your OpenCart site to a plain PHP setup and need multi-currency price conversion that updates across the site when users switch, here's a practical, step-by-step approach to make this work smoothly:

First, you'll need a database table to store currency details and their exchange rates relative to your base currency (pick one—like INR, which was likely your OpenCart default). Here's a simple table structure you can use:

CREATE TABLE currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    currency_code VARCHAR(3) NOT NULL UNIQUE, -- e.g., 'INR', 'USD', 'EUR'
    currency_name VARCHAR(50) NOT NULL, -- e.g., 'Indian Rupee', 'US Dollar'
    exchange_rate DECIMAL(10,4) NOT NULL, -- Rate from base currency to this one (e.g., 0.012 for 1 INR = 0.012 USD)
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial data (replace rates with real values)
INSERT INTO currencies (currency_code, currency_name, exchange_rate, is_default)
VALUES 
('INR', 'Indian Rupee', 1.0000, TRUE),
('USD', 'US Dollar', 0.0120, FALSE),
('EUR', 'Euro', 0.0110, FALSE);

You have two options here, depending on your site size:

• Manual updates: For small stores, just edit the exchange_rate values in the database periodically.
• Automated updates: For larger sites, use a free public exchange rate API to pull rates on a schedule. Write a simple PHP script to fetch data and update your table, then set it up with a cron job to run daily/hourly. Example snippet:

// update_rates.php
$apiUrl = "https://api.example.com/latest?base=INR"; // Replace with a valid API endpoint
$response = file_get_contents($apiUrl);
$rates = json_decode($response, true)['rates'];

$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'db_user', 'db_pass');

foreach ($rates as $code => $rate) {
    if (in_array($code, ['INR', 'USD', 'EUR'])) { // Only update currencies you support
        $stmt = $pdo->prepare("UPDATE currencies SET exchange_rate = ? WHERE currency_code = ?");
        $stmt->execute([$rate, $code]);
    }
}

Pro tip: Add a fallback—if the API call fails, keep using the last stored rate to avoid breaking price displays.

Store the user's chosen currency so it persists across pages. Use session for temporary storage, plus cookie to remember their choice on future visits.

First, create a currency switch handler:

// currency_switch.php
session_start();

// Validate the requested currency
$allowedCurrencies = ['INR', 'USD', 'EUR'];
if (isset($_GET['currency']) && in_array($_GET['currency'], $allowedCurrencies)) {
    $_SESSION['selected_currency'] = $_GET['currency'];
    // Save to cookie for 30 days
    setcookie('selected_currency', $_GET['currency'], time() + (3600 * 24 * 30), "/");
}

// Redirect back to the page the user was on
header("Location: " . $_SERVER['HTTP_REFERER']);
exit;

Then add a switcher to your frontend template:

<div class="currency-switcher">
    <span>Change Currency:</span>
    <a href="currency_switch.php?currency=INR">INR</a>
    <a href="currency_switch.php?currency=USD">USD</a>
    <a href="currency_switch.php?currency=EUR">EUR</a>
</div>

Create a reusable function to convert product prices from your base currency to the user's selected currency. This will keep your code clean and consistent.

function convertPrice($basePrice, $targetCurrency) {
    global $pdo; // Or pass your PDO instance as a parameter
    
    // Get the exchange rate for the target currency
    $stmt = $pdo->prepare("SELECT exchange_rate FROM currencies WHERE currency_code = ?");
    $stmt->execute([$targetCurrency]);
    $rate = $stmt->fetchColumn();

    // Fallback to base currency if rate isn't found
    if (!$rate) {
        return $basePrice;
    }

    // Calculate and return converted price (rounded to 2 decimal places)
    return round($basePrice * $rate, 2);
}

In all your product templates (list pages, detail pages, cart, etc.), fetch the user's selected currency and apply the conversion:

// Initialize session and currency
session_start();
$selectedCurrency = $_SESSION['selected_currency'] ?? ($_COOKIE['selected_currency'] ?? 'INR');

// Example: Fetch product from database
$productId = 123;
$stmt = $pdo->prepare("SELECT name, price FROM products WHERE id = ?");
$stmt->execute([$productId]);
$product = $stmt->fetch(PDO::FETCH_ASSOC);

// Convert price
$convertedPrice = convertPrice($product['price'], $selectedCurrency);

// Display the price
echo "<div class='product-card'>
        <h3>{$product['name']}</h3>
        <p class='price'>{$convertedPrice} {$selectedCurrency}</p>
      </div>";

• Performance optimization: If you're displaying dozens of products, fetch all exchange rates once at the top of the page instead of querying the database for each product.
• Decimal precision: Some currencies use 3 decimal places—adjust the round() function or use number_format() to match each currency's standards.
• Default currency: Always fall back to your base currency if the user hasn't selected one or their selected currency isn't supported.

内容的提问来源于stack exchange，提问作者Parth Sureliya