Read also : Convert MySQL Data into JSON
Demo Data
following is the demo mysql data for the tutorial purpose, we will convert these mysql rows into json format, so copy paste the following data into your PHPMyAdmin.
--
-- Database: `codingcage`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_posts`
--
CREATE TABLE IF NOT EXISTS `tbl_posts` (
`postID` int(11) NOT NULL AUTO_INCREMENT,
`postTitle` varchar(255) NOT NULL,
`postUrl` varchar(255) NOT NULL,
PRIMARY KEY (`postID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `tbl_posts`
--
INSERT INTO `tbl_posts` (`postID`, `postTitle`, `postUrl`) VALUES
(1, 'Simple jQuery Add, Update, Delete with PHP and MySQL', 'http://goo.gl/IL6NTr'),
(2, '15 Free Bootstrap Admin Themes Demo and Download', 'http://goo.gl/1dBwEy'),
(3, 'Easy Ajax Image Upload with jQuery, PHP', 'http://goo.gl/jXZ6LY'),
(4, 'How to Send HTML Format eMails in PHP using PHPMailer', 'http://goo.gl/kQrzJP'),
(5, 'Ajax Bootstrap Signup Form with jQuery PHP and MySQL', 'http://goo.gl/yxKrha'),
(6, 'Submit PHP Form without Page Refresh using jQuery, Ajax', 'http://goo.gl/14vlBe'),
(7, 'How to Convert MySQL Rows into JSON Format in PHP', 'http://goo.gl/qgOiwB'),
(8, 'Designing Bootstrap Signup Form with jQuery Validation', 'http://goo.gl/nECERc'),
(9, 'Upload, Insert, Update, Delete an Image using PHP MySQL', 'http://goo.gl/HRJrDD'),
(10, 'Login Registration with Email Verification, Forgot Password using PHP', 'http://goo.gl/O9FKN1');JSON jQuery Syntax
this is the JSON Parsing jQuery syntax which has three parameters, first one is url for calling and getting PHP or JSON file, and the data is object or a string that is sent to the server with the request, and the success is just a callback executed when request succedd, hence we can also use $.ajax() method to get the same thing.
$.getJSON(url, data, success);Sample JavaScript
the following code is the complete javascript code which calls php file and and parse the json data into html table. as i explained above $.getJSON() methods takes the url parameter and calls the getjson.php file, and php file contains JSON data.
$(document).ready(function(){
var url="getjson.php";
$.getJSON(url,function(data){
console.log(data);
$.each(data.tutorials, function(i,post){
var newRow =
"<tr>"
+"<td>"+post.postTitle+"</td>"
+"<td><a href='"+post.postUrl+"'>Visit</a></td>"
+"</tr>" ;
$(newRow).appendTo("#json-data");
});
});
});HTML table
HTML table, here in this table JSON data are going to be parsed and displayed. we need to give the id of this table in the javascript part id=”json-data”
<table id="json-data" class="table table-bordered table-hover">
<tr>
<th>Post Title</th>
<th>Post Url</th>
</tr>
</table>Sample PHP Code
this is simple PHP code which converts MySQL rows into JSON format, we have already posted this tutorial. using json_encode() we can convert it easily. and i have taken here the main root object as “tutorial”.
<?php
require_once 'db.php';
$posts = array();
$query = "SELECT * FROM tbl_posts";
$stmt = $db_con->prepare($query);
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
$posts['tutorials'][] = $row;
}
echo json_encode($posts);Database Connection
<?php
$db_hostname = "localhost";
$db_user = "root";
$db_password = "";
$db_name = "codingcage";
try{
$db_con = new PDO("mysql:host={$db_hostname};dbname={$db_name}",$db_user,$db_password);
}catch(PDOException $x){
die($x->getMessage());
}Read JSON file
following is the sample data of above created table converted into JSON file as “getposts.json”, now let’s have a look how to read json files. simple just replace the php file with json file, that’s it.
you can validate your JSON Format using : JSON Validator
{
"tutorials": [{
"postID": "1",
"postTitle": "Simple jQuery Add, Update, Delete with PHP and MySQL",
"postUrl": "http://goo.gl/IL6NTr"
}, {
"postID": "2",
"postTitle": "15 Free Bootstrap Admin Themes Demo and Download",
"postUrl": "http://goo.gl/1dBwEy"
}, {
"postID": "3",
"postTitle": "Easy Ajax Image Upload with jQuery, PHP",
"postUrl": "http://goo.gl/jXZ6LY"
}, {
"postID": "4",
"postTitle": "How to Send HTML Format eMails in PHP using PHPMailer",
"postUrl": "http://goo.gl/kQrzJP"
}, {
"postID": "5",
"postTitle": "Ajax Bootstrap Signup Form with jQuery PHP and MySQL",
"postUrl": "http://goo.gl/yxKrha"
}, {
"postID": "6",
"postTitle": "Submit PHP Form without Page Refresh using jQuery, Ajax",
"postUrl": "http://goo.gl/14vlBe"
}, {
"postID": "7",
"postTitle": "How to Convert MySQL Rows into JSON Format in PHP",
"postUrl": "http://goo.gl/qgOiwB"
}, {
"postID": "8",
"postTitle": "Designing Bootstrap Signup Form with jQuery Validation",
"postUrl": "http://goo.gl/nECERc"
}, {
"postID": "9",
"postTitle": "Upload, Insert, Update, Delete an Image using PHP MySQL",
"postUrl": "http://goo.gl/HRJrDD"
}, {
"postID": "10",
"postTitle": "Login Registration with Email Verification, Forgot Password using PHP",
"postUrl": "http://goo.gl/O9FKN1"
}]
}replace php file with json file like this. url=”getposts.json”;
var url="getposts.json";
$.getJSON(url,function(data){
console.log(data);
$.each(data.tutorials, function(i,post){
var newRow =
"<tr>"
+"<td>"+post.postTitle+"</td>"
+"<td><a href='"+post.postUrl+"'>Visit</a></td>"
+"</tr>" ;
$(newRow).appendTo("#json-data");
});
});JSON Parsed HTML Output
here’s JSON data are parsed into HTML table data.
Read using $.ajax()
as i told we can parse it using $.ajax() also, so following is the example code using $.ajax(), it will give the same output as above.
$.ajax({
url: 'getjson.php',
dataType: 'json',
})
.done(function(data){
console.log(data);
$.each(data.tutorials, function(i,post){
var newRow =
"<tr>"
+"<td>"+post.postTitle+"</td>"
+"<td><a href='"+post.postUrl+"'>Visit</a></td>"
+"</tr>" ;
$(newRow).appendTo("#json-data");
});
})
.fail(function(){
alert('fail parsing');
})Hope, you guys like this post please do share with your friends, and do let me know if you have any question related any tutorials.
that’s it, Happy Coding 🙂