Tuesday 2 October 2012

Presenting MySQL table data through PHP, JSON and jQuery

This may seem like a long way round to do something, but believe me, it has its benefits if you are trying to deliver an open systems approach to your development framework.

Let's say I have a MySQL database with table called chat. The chat table has 3 fields; userid, entry and datesaved.

I create a PHP script called jsonout.php which returns all rows of the chat table, thus:

<?php
$con = mysql_connect('localhost','root','');
mysql_select_db('test', $con);
$result = mysql_query('SELECT * FROM `chat`');
while($rows[] = mysql_fetch_assoc($result));
array_pop($rows);
mysql_close($con);
echo json_encode($rows);
?>

Notice the final line (in red) where I return a JSON encoded version of the resulting array.

Now my HTML page (below) can collect the JSON through jQuery and present the results on the page.


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Simple JSON retrieval through jQuery</title>
<!--[if IE]>
 <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<link rel="stylesheet" href="http://meyerweb.com/eric/tools/css/reset/reset.css" />
<style>
body
{
 font:10px/15px Sans-serif;
}
</style>
<script src="http://www.google.com/jsapi"></script>
<script>
 google.load("jquery", "1");
</script>
</head>
<body>
<script>
(function()
{
 $.getJSON('jsonout.php', function(data)
 {
    $.each(data, function(i, items)
    {
      $('body').append('<p>'+items.userid+'</p>'+items.entry+'<p>'+items.datesaved+'</p>');
    });
 });
})();
</script>
</body>
</html>

Monday 1 October 2012

Simple JSON retrieval through jQuery

This page explains how to retrieve some simple JSON data and append it to a page. First the JSON through a script called jsonout.php

{
  "one": "Singular sensation",
  "two": "Beady little eyes",
  "three": "Little birds pitch by my doorstep"
}


Now the HTML with jQuery to retrieve and display it within the page.


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Simple JSON retrieval through jQuery</title>
<!--[if IE]>
 <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<link rel="stylesheet" href="http://meyerweb.com/eric/tools/css/reset/reset.css" />
<style>
body
{
font:10px/15px Sans-serif;
}
</style>
<script src="http://www.google.com/jsapi"></script>
<script>
google.load("jquery", "1"); 
</script>
</head>
<body>
<script>
(function()
{
$.getJSON('jsonout.php', function(data)
{
  $.each(data, function(key, val)
  {
    $('body').append('<p>'+key+':'+val+'</p>');
  });
});
})();
</script>
</body>
</html>