Friday, 19 April 2013

jquery.dataTables and jquery.jeditable

By Tech Zone 4 IT at 12:57    No comments

Example on how to use jquery.dataTables and jquery.jeditable

Recently I was asked to help on a private project of making a database accessible and searchable in a HTML frontend using HTML tables. The database was migrated from Microsoft Access using mdbtools, although you could export the Microsoft Access database to Microsoft Excel and use phpMyAdmin to import the data. Depending on the target environment each method has its advantages.
For this example, the database will be set up manually and with only a few columns although the examples shown here should work with a lot more columns too.
Spoiler: This posting contains a ton of PHP source code.
Disclaimer: I am not a PHP programmer and the examples provided are written to the best of my PHP programming abilities. Even worse, I can not program Javascript and all Javascript shown was copied/modified from the respective documentation/examples of the Javascript libraries. Also, I have not implemented any type of security against MySQL injection attacks, this is left as an excersise to the reader :)

Create the database, the table and the user for the table:
create database mybooks;
use mybooks;
create table books ( id INT auto_increment not NULL, primary key (id), surname TINYTEXT, name TINYTEXT, title TINYTEXT, published INT, whenread INT);
grant all privileges on mybooks.* TO nabaat@localhost identified by 'serenity';
flush privileges;

What you should see now is:
describe books;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| surname   | tinytext | YES  |     | NULL    |                |
| name      | tinytext | YES  |     | NULL    |                |
| title     | tinytext | YES  |     | NULL    |                |
| published | int(11)  | YES  |     | NULL    |                |
| whenread  | int(11)  | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+

If you want, you could add “comments”, “rating”, “genre” or “url” as columns.
Time to fill the database with content.
insert into books (surname, name, title, published, whenread) values ("Asimov", "Isaac", "Nightfall", "1941", "1999");
insert into books (surname, name, title, published, whenread) values ("Barker", "Clive", "Weaveworld", "1987", "1994");
insert into books (surname, name, title, published, whenread) values ("Heinlein", "Robert A.", "Starship Troopers", "1959", "2000");
insert into books (surname, name, title, published, whenread) values ("Stephenson", "Neal", "Snow Crash", "1992", "2004");
insert into books (surname, name, title, published, whenread) values ("Suarez", "Daniel", "Daemon", "2006", "2010");
insert into books (surname, name, title, published, whenread) values ("Bamford", "James", "The Puzzle Palace", "1982", "1990");
At this point we have a nice database table, some content and all we are missing is a nice front end to display the data. To test if things really work, the first step is to create a simple PHP script to display the contents of the books table in a simple HTML table.




The simple PHP HTML table script ‘books_list.php’:
<?php
 
// The HTML header
echo '
<!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>
</head>
<body>
<p>Books</p>
<table cellpadding="3" cellspacing="3" border="0" class="display" id="example">
';
 
// Database access data
$database="mybooks";
$host="localhost";
$user="nabaat";
$password="serenity";
$table="books";
 
// Database access
mysql_connect($host,$user,$password);
mysql_select_db($database) or die( "Unable to select database");
 
// HTML table header
echo '
<thead align="left">
<tr>
';
 
// Obtain the column names from the table
$query  = "show columns from $table";
$result = mysql_query($query);
 
// And print them into a theader structure
while ( $row = mysql_fetch_assoc($result) )
{
    $fieldname = $row['Field'];
 
    // No need to display the 'id' field
    if ( $fieldname == 'id' ) continue;
 
    echo "<th>$fieldname</th>";
}
 
// HTML table header end, begin table body
echo '
</tr>
</thead>
<tbody>
';
 
// And now for the actual content
$query  = "select * from $table";
$result = mysql_query($query);
 
// And print the content into the tbody structure
while ( $row = mysql_fetch_assoc($result) )
{
 
    // Remove the id column
    unset($row['id']);
 
    echo "<tr>";
    foreach ($row as $key => $value)
    {
        echo "<td>$value</td>";
    }
    echo "</tr>";
}
 
// HTML table body end
echo '
</tbody>
</table>
</body>
</html>
';
?>
Not very pretty but it works. The list is not big so you could use the browser search function to jump to a specific entry. But you could also provide a search box for a user to type into and this is where jQuery and the jQuery plugin comes into play. This is the script:
Using jquery and jquery.dataTables ‘books_list_js.php’:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
<?php
 
echo '
<!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>
 
<title>My Books</title>
<style type="text/css" title="currentStyle">
    @import "http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.0/css/jquery.dataTables.css";
</style>
 
<script type="text/javascript" language="javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript" language="javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.0/jquery.dataTables.js"></script>
 
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
    $(\'#example\').dataTable( {
        "bPaginate": false,
    } );
} );
 
</script>
</head>
 
<body>
<p>Books</p>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
';
 
 
$database="mybooks";
$host="localhost";
$user="nabaat";
$password="serenity";
$table="books";
 
mysql_connect($host,$user,$password);
mysql_select_db($database) or die( "Unable to select database");
 
echo '
<thead>
<tr>
';
 
$query  = "show columns from $table";
$result = mysql_query($query);
 
while ( $row = mysql_fetch_assoc($result) )
{
    $fieldname = $row['Field'];
    if ( $fieldname == 'id' ) continue;
    echo "<th>$fieldname</th>";
}
 
echo '
</tr>
</thead>
<tbody>
';
 
$query  = "select * from $table";
$result = mysql_query($query);
 
while ( $row = mysql_fetch_assoc($result) )
{
    unset($row['id']);
    echo "<tr>";
    foreach ($row as $key => $value)
    {
        echo "<td>$value</td>";
    }
    echo "</tr>";
}
 
echo '
</tbody>
</table>
</body>
</html>
';
?>
This is basically the same PHP script with the addition of some Javascript (jquery and jquery.dataTables). Please note that the table has an id called ‘example’ which is used to tell jquery.DataTable on which table to work on. If all went well you should be seeing quite a different table with a search box you can use to search for any entry.
This is nice, especially if you have many more (and similar) entries. But wouldn’t it be nice to be able to edit an entry right there, with a click of the mouse button? This is where jquery.jeditable comes to the rescue. Although this requires a little more work and an additional PHP script (to update the database entry).

0 comments:

Post a Comment