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 showing you how to read and write JSON file with php and mysql.

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. {Name:Rahul, Age:22}. JSON is a human-readable text format that is completely language independent but uses conventions of programming language like C, C++, JavaScript. See how JSON data looks like.

[
    {
        "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 cricketer.json. Let’s parse it 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"
    }, 
 ]

 



To parse JSON data to array check the code below. 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/write to json file

Now going to fetch some records from a database table and then save/writes to a JSON file.

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 in 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/write it to the file. 

<?php
$fp = fopen('cricketer.json', 'w+');
fwrite($fp, json_encode($returnArray));
fclose($fp);
?>

Tags :: create dynamic json file in php mysql,how to insert multiple json data into mysql database in php,how to fetch data from database using json in php,create json object in php dynamically,create json file using php from mysql database data,create json file from mysql database,php create json file from mysql,how to store json data in mysql using php