Open In App

How to Insert JSON data into MySQL database using PHP ?

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we are going to see how to store JSON data into MySQL database using PHP through the XAMPP server.

Requirements: XAMPP Server

Introduction: PHP is an open-source scripting language used to connect with databases and servers. MySQL is a database query language that is used to manage the data in the database to communicate with PHP. JSON stands for JavaScript object notation which stores data in narrow format.

Structure:

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

 

Example: The following is the content for 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"  
  }
]
  • MySQL INSERT statement: It is used to insert data into the database.

Syntax:

INSERT INTO table_name(col1, col2, …, col n) 
    VALUES(value1, value2, …, value n);

Used Methods:

json_decode() Method: This function is used to decode or convert a JSON object to a PHP object.

Syntax:

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"  
  }
]

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

PHP




<!DOCTYPE html>
<html>
  
<head>
    <script src=
    </script>
  
    <link rel="stylesheet" href=
  
    <script src=
    </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>


Output:

  • Open browser and type localhost/base.php

DB table

  • View the inserted data in the table.

Execution Video:



Last Updated : 13 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads