May 16, 2013 4025 Views 1 Comment

Working With JSON, JQuery, PHP and Mysql

SHARE THIS ARTICLE

JSON stands for JavaScript Object Notation and is an excellent alternative to XML. Storing data with JSON creates JavaScript objects which can be easily parsed and manipulated. Learn how to pass JSON output to JQuery, parse JSON data with JQuery and finally inserting it into the DOM.

logstable

Getting data from MYSQL Database and returning as JSON Object using PHP

The magic here is the json_encode function. This nifty little php function is used to transform our array to JSON.

<?php
$db = mysqli_connect("localhost","root","","mylogs");
//MSG
$query = "SELECT * FROM logs LIMIT 20";
$result = mysqli_query($db, $query);
//Add all records to an array
$rows = array();
while($row = $result->fetch_array()){
$rows[] = $row;
}
//Return result to jTable
$qryResult = array();
$qryResult['logs'] = $rows;
echo json_encode($qryResult);

mysqli_close($db);
?>

Parsing JSON Using Jquery

Below is the jQuery code which parses JSON string using $.parseJSON(); method to Javascript Object. Next, we iterate over the javascript fetching each value using the corresponding index.

$(document).ready(function(){
var formhtml = "logreq=1";
var postURL= "process.php";
$.ajax({
type: "POST",
url: postURL,
data: formhtml,
success: function(html){
var output= "<table><tbody><thead><th>Log</th><th>Username</th><th>Date</th><th>Event</th></thead>";
var logsData = $.parseJSON(html);
for (var i in logsData.logs){
output+="<tr><td>" + logsData.logs[i].title + "</td><td>" + logsData.logs[i].user + "</td><td>" + logsData.logs[i].date+ "</td><td>" + logsData.logs[i].log+"</td></tr>";
}
$("#log_container").html(output);
},
error: function (html) {
alert("Oops...Something went terribly wrong");
}
});
});

The Front-End

Jquery is used to update the contents of the div with id “log_container”

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/style.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
<title>KodeSmart | Working With JSON Tutorial</title>
</head>
<body>
<div id="log_container">

</div>
</body>
</html>
  • Terry

    I generally work with PHP and MySQL. I like how clean the coding can be and what you can make it do for your website. I have never worked with JSON or jQuery other than code that you plugin and is easy to implement.