PHP实现无客户端自动插入定时更新的XML数据至MySQL数据库

Hey there! I’ve dealt with exactly this kind of issue before—let’s get your auto-import working smoothly without duplicates. Here’s a step-by-step solution:

1. Ditch Client Triggers: Use Server-Side Scheduled Tasks #

The core problem right now is relying on user visits to trigger the import. Instead, we’ll set up a server-side cron job (Linux) or Task Scheduler (Windows) to run your import script automatically every 30 minutes.

For Linux Servers (Most Common for PHP Sites) #

• Create a standalone PHP script (let’s call it xml_to_db_import.php) that handles XML parsing and database insertion (we’ll build this in the next section).
• Open your server’s crontab with crontab -e (use the user that runs your web server, usually www-data or your hosting account user).
• Add this line to run the script every 30 minutes:

  */30 * * * * /usr/bin/php /var/www/html/your-site/xml_to_db_import.php
  

  • Replace /usr/bin/php with the absolute path to your PHP binary (find it with which php).
  • Replace /var/www/html/your-site/xml_to_db_import.php with the full path to your import script.
• Save and exit—cron will handle the rest!

For Windows Servers #

• Use Task Scheduler to create a basic task that runs php.exe with the path to your import script as an argument, set to trigger every 30 minutes.

2. Stop Duplicate Inserts: Two Reliable Methods #

Combine these for maximum safety:

Option A: Database-Level Unique Constraints #

First, identify a unique identifier in your XML (like an item_id, guid, or any field that’s unique per entry). Then, add a UNIQUE constraint to that column in your MySQL table:

ALTER TABLE your_table ADD UNIQUE KEY unique_item_id (item_id);

Now, if your script tries to insert a duplicate entry, MySQL will throw an error instead of creating a duplicate.

Option B: Use INSERT ... ON DUPLICATE KEY UPDATE #

Instead of just inserting, use this MySQL syntax to either insert a new record or update the existing one if the unique key matches. This is perfect if your XML updates existing entries too.

Here’s a PDO example of how to implement this in your script:

// Assume $pdo is your database connection
$stmt = $pdo->prepare("
    INSERT INTO your_table (item_id, title, description, publish_date)
    VALUES (:item_id, :title, :description, :publish_date)
    ON DUPLICATE KEY UPDATE
        title = :title,
        description = :description,
        publish_date = :publish_date
");

// Loop through XML entries and execute the statement
foreach ($xml->item as $item) {
    $stmt->execute([
        ':item_id' => (string)$item->id,
        ':title' => (string)$item->title,
        ':description' => (string)$item->description,
        ':publish_date' => (string)$item->pubDate
    ]);
}

3. Example Import Script Structure #

Here’s a full, simplified version of xml_to_db_import.php that ties it all together:

<?php
// Increase script timeout (in case XML is large)
set_time_limit(0);

// Connect to MySQL using PDO (more secure and flexible)
try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'db_user', 'db_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Parse XML file (use absolute path to avoid CLI path issues)
$xmlPath = '/var/www/html/your-site/feed.xml';
if (!file_exists($xmlPath)) {
    die("XML file not found at: " . $xmlPath);
}

$xml = simplexml_load_file($xmlPath);
if (!$xml) {
    die("Failed to parse XML file");
}

// Prepare the insert/update statement
$stmt = $pdo->prepare("
    INSERT INTO your_table (item_id, title, description, publish_date)
    VALUES (:item_id, :title, :description, :publish_date)
    ON DUPLICATE KEY UPDATE
        title = :title,
        description = :description,
        publish_date = :publish_date
");

// Process each XML item
foreach ($xml->channel->item as $item) {
    // Extract data from XML (adjust based on your XML structure)
    $itemId = (string)$item->guid;
    $title = (string)$item->title;
    $description = (string)$item->description;
    $pubDate = (string)$item->pubDate;

    // Execute the statement
    try {
        $stmt->execute([
            ':item_id' => $itemId,
            ':title' => $title,
            ':description' => $description,
            ':publish_date' => $pubDate
        ]);
    } catch (PDOException $e) {
        error_log("Failed to process item $itemId: " . $e->getMessage());
        continue; // Skip this item and move to the next
    }
}

echo "Import completed successfully!";
?>

Quick Notes for Success #

• Test the script first: Run it manually via CLI (php xml_to_db_import.php) to make sure it works before setting up cron.
• Log errors: Add error logging to catch issues that might happen when cron runs (since you won’t see the output directly).
• Use absolute paths: CLI scripts don’t have the same working directory as your web server, so always use full paths for files and PHP binaries.

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