Read and write json file with php and mysql

by  
Share:
Read and write json file with php and mysql

JavaScript Object Notation or JSON is a lightweight data-interchange format which is very easy to read and write, we use JSON to transfer data from server to web-application or vice versa as an alternative to XML. In this post I will be explaining you how to read and write JSON file with PHP and MySQL.

View Demo

What is json? How does it looks?

JSON is nothing but a data format similar like arrays. It comes in key value pair e.g. {color:red, size:12}. JSON is a human-readable text format that is completely language independent but uses conventions of programming language like C, C++, JavaScript. Take a look at a sample JSON data.

[
    {
        "player_name": "Sachin Tendulkar",
        "country": "India",
        "sports": "Cricket"
    },
    {
        "player_name": "Roger Federer",
        "country": "Switzerland",
        "sports": "Tennis"
    },
    {
        "player_name": "David Beckham",
        "country": "England",
        "sports": "Football"
    },
]

Read from Json file

I have a file name file1.json. Let’s parse it and and display it in browser. The file content is given below.

[
    {
        "player_name": "Sachin Tendulkar",
        "country": "India",
        "sports": "Cricket"
    },
    {
        "player_name": "Roger Federer",
        "country": "Switzerland",
        "sports": "Tennis"
    },
    {
        "player_name": "David Beckham",
        "country": "England",
        "sports": "Football"
    },
    {
        "player_name": "Tiger Woods",
        "country": "USA",
        "sports": "Golf"
    },
    {
        "player_name": "Sebastian Vettel",
        "country": "Germany",
        "sports": "Formula One"
    },
    {
        "player_name": "Maria Sharapova",
        "country": "Russia",
        "sports": "Tennis"
    },
    {
        "player_name": "Viswanathan Anand",
        "country": "India",
        "sports": "Chess"
    },
    {
        "player_name": "Mahendra Singh Dhoni",
        "country": "India",
        "sports": "Cricket"
    },
    {
        "player_name": "Donald Bradman",
        "country": "Australia",
        "sports": "cricket"
    }
]

Have a look at the code below to convert JSON data to Array. After parsing, display the data on browser. You can also use this technique for stuff like inserting/updating records to database.
<?php
$string = file_get_contents("file1.json");
$jsonRS = json_decode ($string,true);
foreach ($jsonRS as $rs) {
  echo stripslashes($rs["player_name"])." ";
  echo stripslashes($rs["country"])." ";
  echo stripslashes($rs["sports"])."<br>";
}
?>

Fetch data from database and save to json file

Now we are going to fetch some records from a database table and then save to a JSON file. To learn how to connect with database, fetch records and other activities please refer to my other PHP MYSQL tutorials.

Create a table “tbl_products” and add some dummy records

CREATE TABLE IF NOT EXISTS `tbl_products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_name` varchar(255) NOT NULL,
  `products_quantity` int(11) NOT NULL,
  `products_model` varchar(255) NOT NULL,
  `products_price` double NOT NULL,
  `products_weight` double NOT NULL,
  `products_status` enum('A','I') NOT NULL default 'A',
  PRIMARY KEY  (`products_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `tbl_products` (`products_id`, `products_name`, `products_quantity`, `products_model`, `products_price`, `products_weight`, `products_status`) VALUES
(1, 'Peter England', 125, 'XP123', 400, 10, 'A'),
(2, 'Arrow', 360, 'PP123', 900, 12, 'A'),
(3, 'Allen Solly', 456, 'OP78456', 520, 3, 'A'),
(4, 'Raymond', 756, 'SS789465', 1022, 36, 'A'),
(5, 'Grasim', 899, 'GS132645', 640, 55, 'A'),
(6, 'Levis', 885, 'LL123465', 1500, 36, 'A'),
(7, 'Lee', 74, 'Lee4556', 960, 44, 'A');

Connect to the database using PDO class and set the error reporting type.
<?php
// display all error except deprecated and notice  
error_reporting( E_ALL & ~E_DEPRECATED & ~E_NOTICE );
// turn on output buffering 
ob_start();

define('DB_DRIVER', 'mysql');
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "sampletest");

// basic options for PDO 
$dboptions = array(
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

//connect with the server
try {
    $DB = new PDO(DB_DRIVER . ':host=' . DB_HOST . ';dbname=' . DB_DATABASE, DB_USER, DB_PASSWORD, $dboptions);
} catch (Exception $ex) {
    echo($ex->getMessage());
    die;
}

?>

Fetch the records from database table as an associative array type.
<?php
$sql = "SELECT products_id, products_name, products_quantity, products_model, products_price, products_weight, products_status FROM tbl_products WHERE 1 ";
try {
    $stmt = $DB->prepare($sql);
    $stmt->execute();
    $results = $stmt->fetchAll();
} catch (Exception $ex) {
    printErrorMessage($ex->getMessage());
}
$returnArray = array();
if (count($results) > 0) {
	 foreach ($results as $rs) {
	 	$returnArray[] = $rs; 	
	 }
}
?>

Once you fetched all the records in the associative array, encode the data to JSON format and save it to the file.
<?php
$fp = fopen('file4.json', 'w+');
fwrite($fp, json_encode($returnArray));
fclose($fp);
?>

If you want to force download the file then use this code.
<?php
$file = 'file2.json';
file_put_contents($file, json_encode($returnArray));
header("Content-type: application/json");
header('Content-Disposition: attachment; filename="'.basename($file).'"'); 
header('Content-Length: ' . filesize($file));
readfile($file);
?>

View Demo
Share to download the Source Codes for FREE!
We're glad to give free downloads, but we need your love to carry on making that.
Please support us by sharing the page.

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Raihan on

Great sharukh,keep it up

Reply
Raihan on

Great work..

Reply
Shahrukh Khan on

Thanks bro..

nikhat on

good job

Reply
Sunny on

Hello Mr Khan

Great job….

Many Thanks

Reply
amit on

how to fetch all data from mysql in php using for loops and convert it into json array format.when i use for loop then it make multiple array.help me for put all the data in one array.

Reply
Shahrukh Khan on

try to traverse the array and set the array into the main array using numeric key.

Reply
shiva manhar on

json file and js file open in client side how we can protect this file . I don’t want to open anyone my json file or json data if have a solutions so please post me. thank you.

Reply
Philippe on

thank you very much bro you saved my day keep it up.

Reply
Shahrukh Khan on

thanks

Reply
Jenisha S on

PHP code to upload image to Folder and display in Json

Reply
Shahrukh Khan on

can you please be more specific?

Reply
pakdhetimin on

how can i set it up toautomatically change the data and update the json file, whenever i change data in mysql database? could u help me? thanks

Reply
Shahrukh Khan on

you need to set a page and do it manually or use a cron job

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.