How to Insert JSON data into MySQL database using PHP ?

0 0 2 minutes read

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= 
"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> 