Skip to content


PHP 5, MySQL and showResults jQuery Plugin

In this article you will see an example of pushing JSON encoded data to the browser for use in the showResults jQuery plugin.

Overview

Web developers are constantly charged with displaying data, often times requiring the ability to sort and paginate. A Typical example might be a transactions report, a list of products or even something as simple as displaying images aka a gallery.

In this example I will show you how to use the showResults jQuery plugin to show a list of all users in a database, showing their name, age and birthday. Let’s assume that the database table has the following columns, User ID, Username, Password, First Name, Last Name, Age and Birthday.

First, we want to build the result set, the query and resulting data building may look something like:

$sql = mysql_query("SELECT FirstName, Age, Birthday FROM users");
$data = array();
while($row = mysql_fetch_array($sql)) {
   $data[] = $row;
}

Next, we will want to json_encode our data. json_encode is a built in PHP function included in PHP 5.2.6 or greater.

$data = json_encode($data);

Now, I don’t promote using inline php/html, however I will make an exception for the sake of this example.

<html>
<head>
<script language="javascript" src="/demos/js/jquery.js"></script>
<script language="javascript" src="/demos/js/plugins/history.js"></script>
<script language="javascript" src="/demos/js/plugins/showResults.2.0.5.js"></script>
<link type="text/css" href="/demos/css/ui/ui.all.css" rel="Stylesheet" />
<link rel="stylesheet" href="/demos/css/showResults.css" />
<script language="javascript">
var data = <?= $data ?>;
$(document).ready(function() {
    $("myContainer").showResults(data, {
		callback: callback,
		resultTarget: '#searchResults',
		pagesTarget: '.pagination',
		bgColors: ['trCell1','trCell2'],
		arrows: ['ASC','DSC"></span>']
	});		
});
 
function callback() {
	$("#searchResults > tr").click(function() {
		$(this).find("td").toggleClass("background");
	});
}
</script>
</head>
<body>
<div id="myTableContainer">
  <table style='display: none;'>
    <tbody class="showResults template">
      <tr class="%bgcolor%">
        <td>%FirstName%</td>						
        <td>%Age%</td>
        <td>%Birthday%</td>
      </tr>
    </tbody>
   </table>
   <div class="pagination"></div>
   <table cellpadding="4" cellspacing="1" width="450"  id="searchResult">
    <thead>
      <tr>
        <th width="45"><a href="#sort-FirstName" class="sort">NAME</a></th>
        <th width="60"><a href="#sort-Age" class="sort">AGE</a></th>						
        <th width="60"><a href="#sort-Birthday-date" class="sort">BIRTHDAY</a></th>						
      </tr>
     </thead>
     <tbody id="searchResults"></tbody>
</table>
<div class="pagination"></div>	
</div>
</body>
</html>

The result should look something like this:
showResults example

Add Sorting

Now, very easily we can add sorting. if you review the example html above you will notice that I placed links in the table header. To add column sorting simply add “#-sort” followed be “-column”, column being the name of the column you wish to sort by. If the column happens to be a date you will add “-date” after column. The end result for Birthday looks like “#sort-Birthday-date”.
showResults sort example

Live Demo

http://www.stephenrhoades.com/index.php?p=8

Posted in jQuery Plugins & Framework. Tagged with .

3 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Raj said

    Looks good as a demo!!
    But somehow not working at my end.
    Not sure where to put the $sql and $data part, rather how
    does the javascript get the $data.
    What am i missing ?
    Do we need to create a seperate php file for the running the $sql ?

  2. Steve Rhoades said

    @Raj – The first parameter passed to showResults is your data, This data needs to be a json encoded string from PHP or your programming language of choice.
    var data = <? json_encode($data) >;

    Would look something like this:
    var data = [{"name" : "Steve", "age" : "31", "birthday" : "12/1/1977"}]; //Javascript Array of objects

  3. Steve Rhoades said

    Hello D.K.,

    I checked your website, however I do not see the showResults plugin being used.

    Regards,

    Steve

Some HTML is OK

(required)

(required, but never shared)

or, reply to this post via trackback.