如何让实时更新的XML文件同步更新SQL表？涉及FileMaker与MySQL

Got it, let's tackle your problem step by step. You're already exporting updated FileMaker data to XML and know how to parse it with PHP—now you just need to shift from inserting new records to updating existing ones, plus set up real-time sync. Here's how to make that happen:

1. First, Define a Unique Identifier for Matching Records #

Before you can update records, you need a reliable way to match XML entries to existing MySQL rows. This should be a unique, unchanging field present in both systems—like a customer ID, order number, or a dedicated UUID field.

Make sure this field is set as a UNIQUE constraint in your MySQL table. That way, you can always confirm if a record exists before deciding to update it.

2. Parse XML and Extract Key Data #

Keep using your existing PHP XML parsing workflow, but modify it to capture two critical pieces of data for each record:

• The unique identifier (e.g., customer_id)
• All the updated fields you want to sync to MySQL

For example, if your XML looks like this:

<records>
  <record>
    <customer_id>12345</customer_id>
    <name>John Doe</name>
    <email>john.new@example.com</email>
    <last_updated>2024-05-20 14:30:00</last_updated>
  </record>
</records>

Your parser might output an array like:

$record = [
  'customer_id' => 12345,
  'name' => 'John Doe',
  'email' => 'john.new@example.com',
  'last_updated' => '2024-05-20 14:30:00'
];

3. Choose Between Explicit UPDATE Statements or UPSERT #

You have two solid options to handle updates instead of constant inserts:

Option A: Explicit UPDATE for Existing Records #

First, check if the record exists in MySQL using the unique ID. If it does, run an UPDATE statement; if not, you can choose to insert it (or skip it, depending on your needs).

PHP code example:

// Assume $pdo is your MySQL PDO connection instance
$customerId = $record['customer_id'];

// Check if the record exists in MySQL
$stmt = $pdo->prepare("SELECT 1 FROM customers WHERE customer_id = ?");
$stmt->execute([$customerId]);
$recordExists = $stmt->fetchColumn();

if ($recordExists) {
  // Build the UPDATE statement dynamically
  $updateFields = [];
  $params = [];
  // Exclude the unique ID from update fields
  foreach ($record as $key => $value) {
    if ($key !== 'customer_id') {
      $updateFields[] = "$key = ?";
      $params[] = $value;
    }
  }
  $params[] = $customerId; // Add ID for the WHERE clause

  $sql = "UPDATE customers SET " . implode(', ', $updateFields) . " WHERE customer_id = ?";
  $stmt = $pdo->prepare($sql);
  $stmt->execute($params);
} else {
  // Optional: Insert new record if needed
  $insertFields = array_keys($record);
  $placeholders = array_fill(0, count($insertFields), '?');
  $sql = "INSERT INTO customers (" . implode(', ', $insertFields) . ") VALUES (" . implode(', ', $placeholders) . ")";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(array_values($record));
}

Option B: Use MySQL's UPSERT (INSERT ... ON DUPLICATE KEY UPDATE) #

This is a more efficient approach that handles both inserts and updates in a single query. Thanks to your unique constraint, MySQL will automatically update the record if it exists, or insert it if it doesn't.

Example code:

$fields = array_keys($record);
$placeholders = array_fill(0, count($fields), '?');
// Build the update clause for duplicate records
$updateClauses = [];
foreach ($fields as $field) {
  if ($field !== 'customer_id') { // Don't update the unique key
    $updateClauses[] = "$field = VALUES($field)";
  }
}

$sql = "INSERT INTO customers (" . implode(', ', $fields) . ") 
        VALUES (" . implode(', ', $placeholders) . ")
        ON DUPLICATE KEY UPDATE " . implode(', ', $updateClauses);

$stmt = $pdo->prepare($sql);
$stmt->execute(array_values($record));

This cuts down on database round-trips and keeps your code cleaner.

4. Implement Real-Time Sync #

Since your FileMaker script exports XML on a schedule, you have two ways to trigger the PHP sync automatically:

• Schedule parallel tasks: Use Windows Task Scheduler (since you're on IIS) to run your PHP script at the same interval as FileMaker's export. For example, if FileMaker exports every 15 minutes, set the scheduler to run php.exe C:\path\to\your\sync-script.php every 15 minutes.

• Watch for XML file changes: Use a file monitoring tool or persistent PHP script that checks if the XML file has been modified since the last sync. On Windows, you can use a PowerShell script with FileSystemWatcher to trigger the PHP script when the file updates, or use a PHP library like spatie/file-system-watcher (if you can install Composer packages on your IIS server).

A basic persistent PHP watcher example:

$xmlPath = 'C:\path\to\filemaker-export.xml';
$lastModified = filemtime($xmlPath);

while (true) {
  clearstatcache();
  $currentModified = filemtime($xmlPath);
  if ($currentModified > $lastModified) {
    echo "XML updated—syncing to MySQL...\n";
    syncXmlToMysql($xmlPath);
    $lastModified = $currentModified;
  }
  sleep(60); // Check every 60 seconds
}

function syncXmlToMysql($xmlPath) {
  // Your XML parsing and update/upsert logic goes here
}

Run this as a background process (use a Windows service wrapper or WSL to keep it running persistently).

5. Handle Edge Cases #

• Partial updates: If your XML only includes changed fields (not full records), adjust your parsing to only update those specific fields instead of overwriting all values.
• Timestamp checks: Add a last_updated field in both FileMaker and MySQL. Only sync records where the FileMaker timestamp is newer than the MySQL one to avoid unnecessary updates.
• Error logging: Wrap your sync logic in try/catch blocks to log failed attempts—this helps you debug issues without missing critical updates.

内容的提问来源于stack exchange，提问作者Julian Block