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"
    },
    {
        "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"
    }
]

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 wgoing 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);
?>