How to Insert JSON Data into MySQL using PHP

 

 

 

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.

 

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

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

A01.json-file-example.png

 

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.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

<?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.

<?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());
    }
?>

Read:

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

Last Modified: Oct-11-2015

 

[출처] http://www.kodingmadesimple.com/2014/12/how-to-insert-json-data-into-mysql-php.html

 

 

 

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
번호 제목 글쓴이 날짜 조회 수
48 Steps to configure PHP Cron Jobs – Godaddy file 졸리운_곰 2016.12.13 103
47 Creating a simple REST API in PHP 졸리운_곰 2016.11.28 53
46 AngularJS CRUD: Example Using MySQLi Database file 졸리운_곰 2016.11.20 99
45 HTML form 태그, input type 종류 file 졸리운_곰 2016.10.09 358
44 php 와 python 연동 , php 페이지에서 python 사용 졸리운_곰 2016.10.05 97
43 Big data in PHP file 졸리운_곰 2016.09.27 64
42 php에서 Access (엑세스) mdb (accdb) 파일에 연결 file 졸리운_곰 2016.08.11 1417
41 SQLite 소개 졸리운_곰 2016.08.11 872
40 SQLite 와 php 의 연동 졸리운_곰 2016.08.11 820
39 wp 워드프레스 플러그인 만들기 pdf file 졸리운_곰 2016.08.08 55
38 wp 워드프레스 플러그인 만들기 file 졸리운_곰 2016.08.08 1716
37 워드프레스 – 코드플로우(URL에서 페이지까지) 졸리운_곰 2016.07.21 156
36 워드프레스 데이터베이스 들여다보기. file 졸리운_곰 2016.07.21 72
35 워드프레스 웹페이지 구조와 구성요소인 템플릿 파일 이해하기. 졸리운_곰 2016.07.21 352
34 기본적으로 알아야할 워드프레스 파일 구조 및 디렉터리 구조 file 졸리운_곰 2016.07.21 218
33 워드프레스의 기본 구조에 대해 알아보자 file 졸리운_곰 2016.07.21 70
32 php에서 외부 명령어 실행하기 졸리운_곰 2016.05.10 144
31 php함수정리 졸리운_곰 2016.05.10 110
30 10분 안에 PHP 확장 모듈 만들기 file 졸리운_곰 2016.05.10 66
29 How to Call SWI-Prolog from PHP 5 졸리운_곰 2016.05.10 281
대표 김성준 주소 : 경기 용인 분당수지 U타워 등록번호 : 142-07-27414
통신판매업 신고 : 제2012-용인수지-0185호 출판업 신고 : 수지구청 제 123호 개인정보보호최고책임자 : 김성준 sjkim70@stechstar.com
대표전화 : 010-4589-2193 [fax] 02-6280-1294 COPYRIGHT(C) stechstar.com ALL RIGHTS RESERVED