How to Insert JSON File Data Into MySQL Table Using PHP

[
{"name":"John","age":30,"gender":"Male","subject":"PHP"},
{"name":"Robert","age":35,"gender":"Male","subject":"Java"},
{"name":"Sherry","age":30,"gender":"Female","subject":"C#"},
{"name":"Peter","age":40,"gender":"Male","subject":"Python"},
{"name":"","age":40,"gender":"Male","subject":"Python"},
{"name":"Sundar","age":40,"gender":"","subject":"Python"}
]

Create a MySQL table to store data from the JSON file

We will create a table named 'users' in a MySQL database. We will be using the "test" database. The table structure is given below:

The table 'users' has 5 columns in it.

1. id - it is the primary key and auto-incremented
2. name - name of the user
3. age - age of the user
4. gender - gender of the user
5. subject - The subject the user is assigned to.

You can use the below SQL script to create the table.

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` tinyint(4) NOT NULL,
  `gender` varchar(30) NOT NULL,
  `subject` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Write a PHP program to read the JSON file and insert the data into a MySQL table (load_json.php)

Now, we will write a PHP program to read this JSON file and insert the data in the "users" table we created above.

<?php
// Connect to database

$conn = new mysqli("localhost","root","","test");
if ($conn->connect_error){
    die("Database connection failed");
}

$filename = "jason_data.json";
$counter = 0;
$json_data = file_get_contents($filename);
$data_arr = json_decode($json_data,true);

$sql = "insert into users (name,age,gender,subject) values(?,?,?,?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("siss", $name, $age, $gender, $subject);
foreach($data_arr as $row){
    $name = $row['name'];
    $age = $row['age'];
    $gender = $row['gender'];
    $subject = $row['subject'];

    if (!empty($name) && !empty($age) && !empty($gender) && !empty($subject)){
        $stmt->execute();
        $counter++;
    }
   
}
if ($counter == count($data_arr)){
    echo $counter." Rows inserted successfully";
}
else{
    $failed_rows = count($data_arr) - $counter;
    echo nl2br($counter . " Rows inserted successfully\n");
    echo $failed_rows." Rows are not inserted because of error";
}

1. We are using mysqli_connect() function with four parameters. For a successful connection, $conn will be true.
2. Then, we assign the filename for our JSON data. Also, initialize a counter, which will give the number of rows that will be inserted in the database.
3. Then in lines 11 and line 12, we get the content of the file in a string variable and then convert the JSON data into an associative array. json_decode() function does this. Use the second parameter as true for an associative array. If you give the second parameter in this function as false or omit this parameter, it will return a PHP object.
4. Then we write the SQL insert statement using the prepare() method. So, instead of directly putting the values in the SQL statement we use placeholders. Use bind_param() to associate the values with the placeholders.
5. Then using a loop we go through the associative array and execute the statement. Before executing, check for non-empty values.
6. After the LOOP is finished, display the number of rows successfully inserted and also the number of rows which are not inserted due to error.