Cara menggunakan mysql read json array

Hi, in this PHP TUTORIAL, we'll see How to insert JSON Data into MySQL using PHP. Check out its reverse process of Converting Data from MySQL to JSON Format in PHP here. Converting json to mysql using php includes several steps and you will learn things like how to read json file, convert json to array and insert that json array into mysql database in this tutorial. For those who wonder what is JSON, let me give a brief introduction.

Table of Contents

• What is JSON File Format?
• Example of a JSON File
• Step 1: Connect PHP to MySQL Database
• Step 2: Read the JSON file in PHP
• Step 3: Convert JSON String into PHP Array
• Step 4: Extract the Array Values
• Step 5: Insert JSON to MySQL Database with PHP Code
• Complete PHP Script
• empdetails.json
• Is it OK to store JSON in MySQL?
• Can you import JSON into MySQL?
• How do I use JSON in MySQL?
• What is the drawback of JSON in MySQL?

Table of Contents

• What is JSON File Format?
• Example of a JSON File
• Step 1: Connect PHP to MySQL Database
• Step 2: Read the JSON file in PHP
• Step 3: Convert JSON String into PHP Array
• Step 4: Extract the Array Values
• Step 5: Insert JSON to MySQL Database with PHP Code
• Complete PHP Script
• empdetails.json
• Is it OK to store JSON in MySQL?
• Can you import JSON into MySQL?
• How do I use JSON in MySQL?
• What is the drawback of JSON in MySQL?

What is JSON File Format?

JSON file contains information stored in JSON format and has the extension of "*.json". JSON stands for JavaScript Object Notation and is a light weight data exchange format. Being less cluttered and more readable than XML, it has become an easy alternative format to store and exchange data. All modern browsers supports JSON format.

Example of a JSON File

Read Also:

• How to Get JSON from Remote URL in PHP
• How to Write JSON String to File in PHP
• How to Convert JSON to CSV File in PHP

Do you want to know how a JSON file looks like? Well here is the sample.

As you can see by yourself, the JSON format is very human readable and the above file contains some employee details. I'm going to use this file as an example for this tutorial and show you how to insert this JSON object into MySQL database in PHP step by step.

Step 1: Connect PHP to MySQL Database

As the first and foremost step we have to connect PHP to the MySQL database in order to insert JSON data into MySQL DB. For that we use mysql_connect() function to connect PHP with MySQL.

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

Here "employee" is the MySQL Database name we want to store the JSON object. Learn more about using mysqli library for php and mysql database connection here.

Step 2: Read the JSON file in PHP

Next we have to read the JSON file and store its contents to a PHP variable. But how to read json file in php? Well! PHP supports the function file_get_contents() which will read an entire file and returns it as a string. Let’s use it to read our JSON file.

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

Here "empdetails.json" is the JSON file name we want to read.

Step 3: Convert JSON String into PHP Array

The next step for us is to convert json to array. Which is likely we have to convert the JSON string we got from the above step to PHP associative array. Again we use the PHP json decode function which decodes JSON string into PHP array.

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

The first parameter $jsondata contains the JSON file contents.

The second parameter true will convert the string into php associative array.

Step 4: Extract the Array Values

Next we have to parse the above JSON array element one by one and store them into PHP variables.

<?php
    //get the employee details
    $id = $data['empid'];
    $name = $data['personal']['name'];
    $gender = $data['personal']['gender'];
    $age = $data['personal']['age'];
    $streetaddress = $data['personal']['address']['streetaddress'];
    $city = $data['personal']['address']['city'];
    $state = $data['personal']['address']['state'];
    $postalcode = $data['personal']['address']['postalcode'];
    $designation = $data['profile']['designation'];
    $department = $data['profile']['department'];
?>

Step 5: Insert JSON to MySQL Database with PHP Code

Using the above steps, we have extracted all the values from the JSON file. Finally let's insert the extracted JSON object values into the MySQL table.

<?php
    //insert into mysql table
    $sql = "INSERT INTO tbl_emp(empid, empname, gender, age, streetaddress, city, state, postalcode, designation, department)
    VALUES('$id', '$name', '$gender', '$age', '$streetaddress', '$city', '$state', '$postalcode', '$designation', '$department')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>

We are done!!! Now we have successfully imported JSON data into MySQL database.

Here is the complete php code snippet I have used to insert JSON to MySQL using PHP.

Complete PHP Script

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

    //read the json file contents
    $jsondata = file_get_contents('empdetails.json');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
    //get the employee details
    $id = $data['empid'];
    $name = $data['personal']['name'];
    $gender = $data['personal']['gender'];
    $age = $data['personal']['age'];
    $streetaddress = $data['personal']['address']['streetaddress'];
    $city = $data['personal']['address']['city'];
    $state = $data['personal']['address']['state'];
    $postalcode = $data['personal']['address']['postalcode'];
    $designation = $data['profile']['designation'];
    $department = $data['profile']['department'];
    
    //insert into mysql table
    $sql = "INSERT INTO tbl_emp(empid, empname, gender, age, streetaddress, city, state, postalcode, designation, department)
    VALUES('$id', '$name', '$gender', '$age', '$streetaddress', '$city', '$state', '$postalcode', '$designation', '$department')";
    if(!mysql_query($sql,$con))
    {
        die('Error : ' . mysql_error());
    }
?>

empdetails.json

{
    "empid": "SJ011MS",
    "personal": {
        "name": "Smith Jones",
        "gender": "Male",
        "age": "28",
        "address": {
            "streetaddress": "7 24th Street",
            "city": "New York",
            "state": "NY",
            "postalcode": "10038"
        }
    },
    "profile": {
        "designation": "Deputy General",
        "department": "Finance"
    }
}

Read:

• How to Insert Multiple JSON Array into MySQL in PHP
• How to Convert MySQL to JSON Format using PHP
• How to Pretty Print JSON with PHP

Hope this tutorial helps you to understand how to insert JSON data into MySQL using PHP.

Last Modified: Feb-21-2017

Is it OK to store JSON in MySQL?

MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column: Automatic validation of JSON documents stored in JSON columns.

Can you import JSON into MySQL?

MySQL Shell's JSON import utility util. importJSON() enables you to import JSON documents from a file (or FIFO special file) or standard input to a MySQL Server collection or relational table.

How do I use JSON in MySQL?

Key takeaway for extracting data from a JSON field in MySQL:.

Use $. ... .

Use $[index] to extract the value of an element from a JSON array..

Use -> as a shortcut for JSON_EXTRACT if the value is not a string..

What is the drawback of JSON in MySQL?

The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.