How to Insert JSON data into MySQL database using PHP?

Last Updated : 31 May, 2024

To insert JSON data into MySQL database using PHP, use the json_decode function in PHP to convert JSON object into an array that can be inserted into the database.

Here, we are going to see how to insert JSON data into MySQL database using PHP through the XAMPP server in a step-by-step way.

JSON Structure:

[{
    "data1": "value1",
    "data2": "value2",
    . . .
    "datan": "value n"
}]

Example: The following is the content for the student.json file.

[  
  {  
    "name": "sravan kumar",  
    "gender": "Male",  
    "subject": "Software engineering"  
  },  
  {  
    "name": "sudheer",  
    "gender": "Male",  
    "subject": "deep learning"    
  },  
  {  
    "name": "radha",  
    "gender": "Female",  
    "subject": "Software engineering"  
  },  
  {  
    "name": "vani",  
    "gender": "Female",  
    "subject": "Software engineering"  
  }
]

Insert JSON data into MySQL database using PHP

Use the json_decode() Method to decode or convert a JSON object to a PHP object.

Syntax:

The json_decode function syntax is:

json_decode(string, assoc)

The string is the JSON data and the assoc is the boolean value.
If assoc is true, data is converted into an associative array.
false is the default value.

Approach:

Start XAMPP server
Create a table named student under database test.
Write PHP code to insert JSON data into student table.

Steps:

Start XAMPP server.

Create a table named student under test database.

Create JSON data and save it as college_subjects.json under xampp-htdocs folder.

[  
  {  
    "name": "sravan kumar",  
    "gender": "Male",  
    "subject": "Software engineering"  
  },  
  {  
    "name": "sudheer",  
    "gender": "Male",  
    "subject": "deep learning"    
  },  
  {  
    "name": "radha",  
    "gender": "Female",  
    "subject": "Software engineering"  
  },  
  {  
    "name": "vani",  
    "gender": "Female",  
    "subject": "Software engineering"  
  }
]

The following is the PHP code to store JSON data and display it on the web page.

<!DOCTYPE html>
<html>

<head>
    <script src=
"https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js">
    </script>

    <link rel="stylesheet" href=
"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />

    <script src=
"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js">
    </script>

    <style>
        .box {
            width: 750px;
            padding: 20px;
            background-color: #fff;
            border: 1px solid #ccc;
            border-radius: 5px;
            margin-top: 100px;
        }
    </style>
</head>

<body>
    <div class="container box">
        <h3 align="center">
            Geeks for Geeks Import JSON 
            data into database
        </h3><br />
        
        <?php
        
            // Server name => localhost
            // Username => root
            // Password => empty
            // Database name => test
            // Passing these 4 parameters
            $connect = mysqli_connect("localhost", "root", "", "test"); 
            
            $query = '';
            $table_data = '';
          
            // json file name
            $filename = "college_subjects.json";
          
            // Read the JSON file in PHP
            $data = file_get_contents($filename); 
          
            // Convert the JSON String into PHP Array
            $array = json_decode($data, true); 
          
            // Extracting row by row
            foreach($array as $row) {

                // Database query to insert data 
                // into database Make Multiple 
                // Insert Query 
                $query .= 
                "INSERT INTO student VALUES 
                ('".$row["name"]."', '".$row["gender"]."', 
                '".$row["subject"]."'); "; 
             
                $table_data .= '
                <tr>
                    <td>'.$row["name"].'</td>
                    <td>'.$row["gender"].'</td>
                    <td>'.$row["subject"].'</td>
                </tr>
                '; // Data for display on Web page
            }

            if(mysqli_multi_query($connect, $query)) {
                echo '<h3>Inserted JSON Data</h3><br />';
                echo '
                <table class="table table-bordered">
                <tr>
                    <th width="45%">Name</th>
                    <th width="10%">Gender</th>
                    <th width="45%">Subject</th>
                </tr>
                ';
                echo $table_data;  
                echo '</table>';
            }
          ?>
        <br />
    </div>
</body>

</html>