Skip to content
Advertisement

Import JSON data into database(MySQL) using PHP

I am writing a script in PHP to directly import data from a JSON file directly into a MySQL database. My sample data at the moment is pretty small. I have a few questions about it. I would like to do this without external packages. If you know or have a code sample I can look at that would be very helpful.

  1. How can I make it work with very large data sets? Break it up in chunks? Import it line by line? Use fopen?
  2. If during it’s run the script get interrupted for some reason, is there a way to make sure it continues where it left of? How would you do this?
  3. If I want to remove duplicates with the same emails should I do it during the import or maybe after with a SQL query?
  4. Would this work with XML and CSV as well?

DATA

[
  {
  "id":1,
  "name":"Mike",
  "email":"mike@businessweek.com"
  },
  {
  "id":2,
  "name":"Nick",
  "email":"nick@businessweek.com"
  }
]

$jsondata = file_get_contents('data.json');

$data = json_decode($jsondata, true);

foreach ($data as $row) {

    $query = 'INSERT INTO `member` (`name`, `email`) VALUES (?, ?)';

    $statement = $pdo->prepare($sql);

    $statment->execute([$row['name'], $row['email']]);

}

Advertisement

Answer

This will probably be closed, but to give you some information:

  1. The only way to break it up is if you had individual JSON objects per line, or individual JSON objects, or grab every X lines (5 in your example). Then you could just fopen and read it line by line. You could get from { to } (not recommended).
  2. The only way that I can think of is to use transactions, and/or track the number of rows total and decrement each insert in a separate DB table.
  3. If you import the entire file you can do it then, maybe with array_column to reindex on email which will remove duplicates. If not then it would have to be after with SQL.
  4. Yes, you would do it the same way, just parsing the data is different. CSV would be fgetcsv or str_getcsv and XML would be Simple XML or something similar. After the data is in an array of arrays it will be the same inserting.
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement