How to Insert the large json file into mysql with php?

user6476237

I want to read from large json file and store it in database with php.

I wrote this code for parsing and insert file but the last query does'nt work.

public function insert_from_json() {
    $json = '
            [            
            {
            "name": "The Adventurer",
            "year": 1917,
            "country": "USA",
            "durationMinutes": 24,
            "director": "Charles Chaplin"
            },
            {
            "name": "Mest kinematograficheskogo operatora",
            "year": 1912,
            "country": "Russia",
            "durationMinutes": 12,
            "director": "Wladyslaw Starewicz"
            }
            ]';

    $result = json_decode($json);

    $vals = '';
    foreach ($result as $key => $value) {

        if ($value) {
            $vals.="('$value->name','$value->year','$value->country','$value->durationMinutes','$value->director'),";
        }
    }
    $vals = trim($vals, ',');
    $stmt = $this->conn->prepare("INSERT INTO film (name,year,country,durationMinutes,director) VALUES ($vals)");
    $stmt->execute();
    echo 'successfully';
}

please help me for make it.

Mikey

Try this:

$json = '[            
    {
        "name": "The Adventurer",
        "year": 1917,
        "country": "USA",
        "durationMinutes": 24,
        "director": "Charles Chaplin"
    },
    {
        "name": "Mest kinematograficheskogo operatora",
        "year": 1912,
        "country": "Russia",
        "durationMinutes": 12,
        "director": "Wladyslaw Starewicz"
    }
]';

$result = json_decode($json);

$stmt = $this->conn->prepare("
    INSERT INTO film (name, year, country, durationMinutes, director) 
    VALUES (:name, :year, :country, :durationMinutes, :director)
");

// loop through each object
foreach ($result as $r) {
    // and insert it into the database
    $stmt->execute([
        ':name' => $r->name,
        ':year' => $r->year, 
        ':country' => $r->country, 
        ':durationMinutes' => $r->durationMinutes, 
        ':director' => $r->director
    ]);
}

The main difference here is that you are doing multiple inserts instead of one big insert.

Collected from the Internet

Please contact [email protected] to delete if infringement.