如何将json_decode解析的数据按从旧到新存入MySQL数据库？附PHP代码

Hey there! Let's tweak your code to save that JSON data into MySQL in oldest to newest order. I'll walk you through the key changes, building right on top of your existing code:

1. First: Pinpoint the Sorting Field #

First up, you need to know which field in your JSON items tracks the creation/published date. Let's assume it's something like published_at (swap this with the actual timestamp/date field from your JSON—common alternatives are created_at or timestamp). This is what we'll use to sort entries.

2. Sort the Items Array #

Before processing images and saving to the database, we'll sort the $rows array using PHP's usort() function. This lets us define a custom rule to order entries from oldest to newest.

3. Add MySQL Insert Logic #

Since you're using Intervention Image, I'll assume you're working with Laravel (a common setup for this library). I'll use Eloquent for the database insert, but I'll also include a raw PDO alternative if that's your setup.

Modified Full Code #

<?php
$url = 'http://example.com/523223.json?';
$json = file_get_contents($url);
$data = json_decode($json);
$rows = $data->items; // Curly braces aren't needed here unless the key has special characters

// Step 1: Sort rows from oldest to newest
// Replace 'published_at' with your actual date/timestamp field from the JSON items
usort($rows, function($a, $b) {
    // Convert date/timestamp to Unix timestamp for easy comparison
    $dateA = strtotime($a->published_at);
    $dateB = strtotime($b->published_at);
    
    // Return values to sort in ascending order (oldest first)
    return $dateA <=> $dateB;
});

foreach ($rows as $row) {
    echo '<p>';
    $title = $row->name;
    $description = $row->description;
    $link = $row->link;
    $image_link = $row->thumbnails;
    $path = $image_link->large;
    $filename = basename($path);

    // Process and save the image (fixed the incomplete catch block)
    try {
        Image::make($path)->save(public_path('storage/posts/' . $filename));
    } catch (\Intervention\Image\Exception\NotReadableException $e) {
        // Handle errors—log them, skip the entry, etc.
        error_log('Image processing failed: ' . $e->getMessage());
        continue; // Move to the next item if image fails
    }

    // Step 2: Insert into MySQL (Laravel Eloquent example)
    // Assume you have a Post model with columns: title, description, link, image_filename
    try {
        \App\Models\Post::create([
            'title' => $title,
            'description' => $description,
            'link' => $link,
            'image_filename' => $filename,
            // Add any other fields you need to store
        ]);
    } catch (\Illuminate\Database\QueryException $e) {
        error_log('Database save failed: ' . $e->getMessage());
        continue;
    }

    echo 'Processed: ' . $title . '</p>';
}
?>

Raw PDO Alternative (If Not Using Laravel) #

Replace the Eloquent create() section with this PDO code (make sure you have a valid PDO connection set up):

// Assume $pdo is your existing PDO connection object
$stmt = $pdo->prepare("INSERT INTO posts (title, description, link, image_filename) VALUES (?, ?, ?, ?)");
$stmt->execute([$title, $description, $link, $filename]);

Quick Reminders #

• Verify the date field: Double-check that the field you use in usort() exists in your JSON and is a valid date/timestamp. If it's already a Unix timestamp, skip the strtotime() call and compare directly.
• Duplicate prevention: If you'll run this script multiple times, add checks to avoid duplicates (use firstOrCreate() in Eloquent, or add a unique constraint on a field like link in your database table).
• Error handling: The added logging and skip logic is basic—adjust it to fit your needs (like sending alerts or writing to a dedicated log file).

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