从ERP数据库每日更新WordPress/WooCommerce产品价格需求

Hey there! Let's walk through how to set up daily price sync between your ERP database and WooCommerce—this is a really common task, and we’ve got a few solid options depending on your setup.

First, let’s confirm the core mapping: you’ve got erp_database.part.ID linking directly to the corresponding product ID in WordPress (either the post_id in wp_postmeta, or a custom meta field if you’re storing the ERP ID separately). I’ll cover both scenarios below.

Option 1: Direct MySQL Cross-Database Update (Fastest for Large Datasets) #

If your two MySQL databases are on the same server (or you can establish a remote connection between them), a direct SQL update is the most efficient way to sync thousands of prices.

Assumption #

We’ll assume WordPress product post_id matches erp_database.part.ID. If you use a custom meta field (e.g., _erp_part_id) to store the ERP ID, adjust the JOIN condition accordingly.

Step 1: Test the Sync First #

Always run a SELECT query to verify the mapping before updating:

SELECT 
    pm.post_id AS wp_product_id,
    pm.meta_value AS current_woocommerce_price,
    p.new_price AS erp_new_price
FROM wp_database.wp_postmeta pm
JOIN erp_database.part p 
    ON pm.post_id = p.ID  -- Replace with `pm.meta_key = '_erp_part_id' AND pm.meta_value = p.ID` if using a custom field
WHERE pm.meta_key = '_price';

Step 2: Run the Update #

Once you’ve confirmed the data matches, execute the update:

UPDATE wp_database.wp_postmeta pm
JOIN erp_database.part p 
    ON pm.post_id = p.ID  -- Adjust join condition if needed
SET pm.meta_value = p.new_price
WHERE pm.meta_key = '_price';

Key Notes #

• Permissions: Ensure the MySQL user running this query has SELECT access to erp_database.part and UPDATE access to wp_database.wp_postmeta.
• Remote Databases: If the databases are on separate servers, use MySQL’s remote connection support (via --host flag) or set up a FEDERATED table to bridge them.

Option 2: PHP Script with Cron (Flexible for Extra Logic) #

If you need to add validation, logging, or handle cross-server databases that can’t directly connect, a PHP script paired with a cron job is perfect.

Example Script #

Save this in a secure directory (e.g., wp-content/scripts/price-sync.php—don’t put it in your web root):

<?php
// Database configs
$wp_db = [
    'host' => 'your-wp-db-host',
    'user' => 'your-wp-db-user',
    'pass' => 'your-wp-db-pass',
    'name' => 'wp_database'
];

$erp_db = [
    'host' => 'your-erp-db-host',
    'user' => 'your-erp-db-user',
    'pass' => 'your-erp-db-pass',
    'name' => 'erp_database'
];

// Connect to WordPress DB
$wp_conn = new mysqli($wp_db['host'], $wp_db['user'], $wp_db['pass'], $wp_db['name']);
if ($wp_conn->connect_error) die("WP DB Connection Failed: " . $wp_conn->connect_error);

// Connect to ERP DB
$erp_conn = new mysqli($erp_db['host'], $erp_db['user'], $erp_db['pass'], $erp_db['name']);
if ($erp_conn->connect_error) die("ERP DB Connection Failed: " . $erp_conn->connect_error);

// Fetch ERP prices
$erp_result = $erp_conn->query("SELECT ID, new_price FROM part");
if ($erp_result->num_rows === 0) {
    error_log("No parts found in ERP database");
    exit;
}

// Prepare WP update statement (prevents SQL injection)
$update_stmt = $wp_conn->prepare("UPDATE wp_postmeta SET meta_value = ? WHERE post_id = ? AND meta_key = '_price'");
$update_stmt->bind_param("di", $new_price, $erp_id);

// Sync each price
while ($row = $erp_result->fetch_assoc()) {
    $erp_id = $row['ID'];
    $new_price = $row['new_price'];
    
    if (!$update_stmt->execute()) {
        error_log("Failed to update product {$erp_id}: " . $update_stmt->error);
    } else {
        error_log("Updated product {$erp_id} to price {$new_price}");
    }
}

// Cleanup
$update_stmt->close();
$erp_conn->close();
$wp_conn->close();
?>

Set Up a Cron Job #

To run this daily (e.g., at 2 AM), add this to your server’s crontab (run crontab -e to edit):

0 2 * * * /usr/bin/php /path/to/your/wordpress/wp-content/scripts/price-sync.php

Option 3: WooCommerce REST API (Follows WooCommerce Best Practices) #

If you want to avoid direct database edits and trigger WooCommerce’s internal logic (like cache clearing or price change hooks), use the REST API.

Example Script #

First, generate API credentials in WooCommerce: Go to WooCommerce > Settings > Advanced > REST API and create a new key with "Read/Write" permissions.

<?php
$erp_db = [
    'host' => 'your-erp-db-host',
    'user' => 'your-erp-db-user',
    'pass' => 'your-erp-db-pass',
    'name' => 'erp_database'
];

$wc_api = [
    'url' => 'https://your-wp-site.com/wp-json/wc/v3',
    'key' => 'your-api-consumer-key',
    'secret' => 'your-api-consumer-secret'
];

// Connect to ERP DB
$erp_conn = new mysqli($erp_db['host'], $erp_db['user'], $erp_db['pass'], $erp_db['name']);
if ($erp_conn->connect_error) die("ERP DB Connection Failed: " . $erp_conn->connect_error);

// Fetch ERP prices
$erp_result = $erp_conn->query("SELECT ID, new_price FROM part");

// Sync via API
while ($row = $erp_result->fetch_assoc()) {
    $product_id = $row['ID'];
    $new_price = strval($row['new_price']);
    
    $ch = curl_init("{$wc_api['url']}/products/{$product_id}");
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'PUT');
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode(['regular_price' => $new_price]));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Authorization: Basic ' . base64_encode("{$wc_api['key']}:{$wc_api['secret']}")
    ]);
    
    $response = curl_exec($ch);
    $status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    
    if ($status !== 200) {
        error_log("API Update Failed for {$product_id}: HTTP {$status}, Response: {$response}");
    } else {
        error_log("API Updated {$product_id} to {$new_price}");
    }
    
    curl_close($ch);
}

$erp_conn->close();
?>

Key Recommendations #

• For thousands of products, stick to Option 1 or 2—they’re far faster than the API.
• Always back up your WordPress database before running bulk updates.
• Test any sync logic in a staging environment first to avoid breaking your live site.

内容的提问来源于stack exchange，提问作者Jim VanPetten