Export data from MySQL to JSON using PHP

In this tutorial, we are going to learn, how to extract data from MySQL database and export it to a file in JSON format, using PHP.

What is JSON?

JSON (JavaScript Object Notation) is a lightweight format, used to format data for interchanging. It is based on a subset of JavaScript. The reason for the JSON’s popularity is its usage in APIs of many applications like Facebook, and Twitter etc.

JSON is built on a collection of name/value pairs. In JavaScript, JSON is represented as a string.

Let me explain this to you, with an example.

var x1 = {x:y} is an object. // In JavaScript
var x2 =’{“x”:”y”}’ // is an object of type string. This is a JSON equivalent of the upper object.

To convert it into a JavaScript object, you have to parse it like shown below:

var x = JSON.parse(x2);

A JSON array is wrapped inside two brackets. Between these brackets, you can insert JSON objects separated by “,”. JSON object starts with { and ends with }. The name and value pair is separated by a colon.

[
{“id”:”1”, “name”:”John Doe”, “nationality”:”American”}
]

Create the Database

Now, coming back to our main topic, let’s create a database “library” and a table “books”.
The following is a structure for the “books” table.

Now Insert few records into the newly created table.

Create the php File

Now let’s create a file “json_data.php”

<?php

$dbhost ="localhost";
$dbuser = "root";
$dbpass = "";
$dbname ="library";

//connect to database
$conn = @mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die("Couldn't connet to database.");

//get the data from table ‘books’
$query = "select * from books";

//execute the query
$result = mysqli_query($conn, $query);

if(!$result){ echo "Couldn't execute the query"; die();}
else{

 //creates an empty array to hold data
 $data = array();
  while($row = mysqli_fetch_assoc($result)){
    $data[]=$row;
  }

//  echo json_encode($data, JSON_PRETTY_PRINT);

//it will create file results.json with writing mode.
//you can read more about file handling in PHP here. 
$fp = fopen('results.json', 'w');

//json_enconde($array, $options(optional) is the method to convert array into JSON
fwrite($fp, json_encode($data, JSON_PRETTY_PRINT));

//close the file
fclose($fp);
}

?>

Execute the code

After the executing of json_data.php using http://localhost/json_data.php
You will see the results.json file in the same directory where json_data.php resides.
Type http://localhost/results.json in your browser, assuming results.json and json_data.php exist in your webserver’s root directory.

Related Articles

Paging and Sorting Grid in ASP.NET MVC Using JQuery and EF

Block/Unblock USB Ports in windows using VB.NET

Convert int to BigInteger in Java