how to store json data in mysql using php?

Today, We want to share with you how to store json data in mysql using php.In this post we will show you json to mysql php, hear for fetch json data and insert into mysql table in php we will give you demo and example for implement.In this post, we will learn about php mysql store json with an example.

How to Insert JSON Data into MySQL using PHP?

What is JSON File Format?

JSON file simple data contains details stored in JSON format as well as has the extension of “*.json”. JSON stands for JavaScript Object Notation as well as is a light weight data interchange format. Being less cluttered as well as more readable than XML, it has become an simply alternative main json data format to store as well as interchange data. All latest browsers supports like a fresh JSON format.

Read Also:  Email Validation in pure JavaScript | Validate email address

here bellow we will display simple Example of a JSON File.

memberdetails.json

{
    "memberid": "SJ011MS",
    "memberdtl": {
        "name": "Virat Kohali",
        "gender": "Male",
        "age": "29",
        "location": {
            "streetlocation": "7 24th Street",
            "city": "India",
            "state": "IN",
            "zipcode": "20038"
        }
    },
    "profile": {
        "appointment": "Dipti Shah",
        "sector": "Computer"
    }
}

Step 1: Connect PHP to MySQL Database

<?php
    $con = mysql_connect("username","password","") or die('Could not connect: ' . mysql_error());
    mysql_select_db("members", $con);
?>

Step 2: Read the JSON file in PHP

<?php
    //read the json file contents
    $jsondata = file_get_contents('memberdetails.json');
?>

Step 3: Convert JSON String into PHP Array

<?php
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
?>

Step 4: Extract the Array Values

<?php
    //get the members details
    $id = $data['memberid'];
    $name = $data['memberdtl']['name'];
    $gender = $data['memberdtl']['gender'];
    $age = $data['memberdtl']['age'];
    $streetlocation = $data['memberdtl']['location']['streetlocation'];
    $city = $data['memberdtl']['location']['city'];
    $state = $data['memberdtl']['location']['state'];
    $zipcode = $data['memberdtl']['location']['zipcode'];
    $appointment = $data['profile']['appointment'];
    $sector = $data['profile']['sector'];
?>

Step 5: Insert JSON to MySQL Database with PHP Code

<?php
    //insert into mysql table
    $sql = "INSERT INTO tbl_member(memberid, membername, gender, age, streetlocation, city, state, zipcode, appointment, sector)
    VALUES('$id', '$name', '$gender', '$age', '$streetlocation', '$city', '$state', '$zipcode', '$appointment', '$sector')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>

Full PHP Script

index.php

<?php
    //connect to mysql db
    $con = mysql_connect("username","password","") or die('Could not connect: ' . mysql_error());
    //connect to the members database
    mysql_select_db("members", $con);

    //read the json file contents
    $jsondata = file_get_contents('memberdetails.json');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    //get the members details
    $id = $data['memberid'];
    $name = $data['memberdtl']['name'];
    $gender = $data['memberdtl']['gender'];
    $age = $data['memberdtl']['age'];
    $streetlocation = $data['memberdtl']['location']['streetlocation'];
    $city = $data['memberdtl']['location']['city'];
    $state = $data['memberdtl']['location']['state'];
    $zipcode = $data['memberdtl']['location']['zipcode'];
    $appointment = $data['profile']['appointment'];
    $sector = $data['profile']['sector'];
    
    //insert into mysql table
    $sql = "INSERT INTO tbl_member(memberid, membername, gender, age, streetlocation, city, state, zipcode, appointment, sector)
    VALUES('$id', '$name', '$gender', '$age', '$streetlocation', '$city', '$state', '$zipcode', '$appointment', '$sector')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>

I hope you get an idea about php mysql store json.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.