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">
<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’:
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).
This is how the frontend
looks like. Again, the same basic script, just a lot more data in the header.
Also, another PHP script is referenced named ‘books_edit.php’.
‘books_edit_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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
<?php
echo '
<!DOCTYPE
html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<title>Books</title>
<style
type="text/css" title="currentStyle">
</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" language="javascript" src="http://www.appelsiini.net/download/jquery.jeditable.mini.js"></script>
<script
type="text/javascript">
$(document).ready(function()
{
$(\'#example\').dataTable(
{
"bPaginate":
false,
"bStateSave":
true
}
);
/*
Init DataTables */
var oTable = $(\'#example\').dataTable();
/*
Apply the jEditable handlers to the table */
oTable.$(\'td\').editable(
\'./books_edit.php\', {
"callback":
function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate(
sValue, aPos[0], aPos[1] );
window.location.reload();
},
"submitdata":
function ( value, settings ) {
return {
"row_id":
this.parentNode.getAttribute(\'id\'),
"column":
oTable.fnGetPosition( this )[2]
};
},
"height":
"14px",
"width":
"100%"
}
);
} );
</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) )
{
$id = $row['id'];
echo "<tr id=\"$id\">";
unset($row['id']);
foreach ($row as $key => $value)
{
echo "<td>$value</td>";
}
echo "</tr>";
}
echo '
</tbody>
</table>
</body>
</html>
';
?>
|
And this is the
corresponding ‘books_edit.php’ script:
|
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
|
<?php
// This
script gets data via POST from books_edit_js.php
// The
POST request supplies the following data:
//
//
row_id => corresponds to the id in the database table
//
value => the actual (changed) content to be written
//
column => which column number(!)
$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");
$rawdata = $_POST;
// as
only the column number is provided
// map
the column number to the column name
$query = "show columns
from $table";
$result=mysql_query($query);
// grab
the field names and pack them into an array
$fields = array();
while ( $row = mysql_fetch_assoc($result) )
{
$feldname = $row['Field'];
array_push($fields,
$feldname);
}
// Grab
the data from the $_POST request
$id
=
$rawdata['row_id'];
$value
=
$rawdata['value'];
$column
=
$rawdata['column'];
// As id
was "stripped" from the first table
// The
index to the column name in the array will be wrong
// We
need to add 1 to the column number
$column
=
$column + 1;
// Get
the column name by index number
$fieldname = $fields[$column];
// The
database was created in ISO-8859-x
// so
better convert the UTF-8 input from the browser
$value = utf8_decode($value);
$query = "update $table set $fieldname =
'$value' where id = '$id'";
$result = mysql_query($query);
//
Provide feedback to the entry field
if (!$result) { echo "Update failed"; }
else
{ echo "UPD: $value"; }
// Close
the connection
mysql_close();
?>
|
So far, so good. The
table contents can now be edited. But how about adding or deleting entries?
This time, four files are
required:
§ books_manip_js.php
§ books_edit.php (from the
edit only version, see above)
§ add.php
§ delete.php
So here are three new
files:
‘books_manip_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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
<?php
echo '
<!DOCTYPE
html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<title>Books</title>
<style
type="text/css" title="currentStyle">
@import
"http://jquery-datatables-editable.googlecode.com/svn/trunk/media/css/themes/base/jquery-ui.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.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"></script>
<script
type="text/javascript" language="javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.validate/1.9/jquery.validate.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" language="javascript" src="http://jquery-datatables-editable.googlecode.com/svn-history/r122/trunk/media/js/jquery.dataTables.editable.js"></script>
<script
type="text/javascript" language="javascript" src="http://www.appelsiini.net/download/jquery.jeditable.mini.js"></script>
<script
type="text/javascript">
$(document).ready(function()
{
$(\'#example\').dataTable(
{
"bPaginate":
false,
"bStateSave":
true
}
);
/*
Init DataTables */
var oTable = $(\'#example\').dataTable();
/*
Apply the jEditable handlers to the table */
oTable.$(\'td\').editable(
\'./books_edit.php\', {
"callback":
function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate(
sValue, aPos[0], aPos[1] );
window.location.reload();
},
"submitdata":
function ( value, settings ) {
return {
"row_id":
this.parentNode.getAttribute(\'id\'),
"column":
oTable.fnGetPosition( this )[2]
};
},
"height":
"14px",
"width":
"100%"
}
);
$(\'#example\').dataTable().makeEditable({
"sAddURL":
"./add.php",
"sDeleteURL":
"./delete.php",
"sAddNewRowFormId":
"addme",
}
);
} );
</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) )
{
$id = $row['id'];
echo "<tr id=\"$id\">";
unset($row['id']);
foreach ($row as $key => $value)
{
echo "<td>$value</td>";
}
echo "</tr>";
}
echo '
</tbody>
</table>
<div
class="add_delete_toolbar" />
<form
id="addme" action="#" title="Add new record">
<label
for="surname">Surname</label><br />
<input
type="text" name="surname" id="surname" class="required" rel="0" />
<br
/>
<label
for="name">Name</label><br />
<input
type="text" name="name" id="name" class="required" rel="1" />
<br
/>
<label
for="title">Title</label><br />
<input
type="text" name="title" id="title" class="required" rel="2" />
<br
/>
<label
for="published">Published</label><br />
<input
type="text" name="published" id="published" rel="3" />
<br
/>
<label
for="whenread">When Read</label><br />
<input
type="text" name="whenread" id="whenread" rel="4" />
<br
/>
</form>
</body>
</html>
';
?>
|
‘add.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
|
<?php
$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");
$surname = $_POST['surname'];
$name = $_POST['name'];
$title = $_POST['title'];
$published = $_POST['published'];
$whenread = $_POST['whenread'];
$query = "insert into $table (surname, name,
title, published, whenread) values (\"$surname\",
\"$name\", \"$title\", \"$published\",
\"$whenread\")";
$result = mysql_query($query);
if (!$result) { echo "Insert failed"; }
else
{ echo "ok"; }
mysql_close();
?>
|
‘delete.php’:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<?php
$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");
$id
=
$_POST['id'];
$query = "delete from
$table where id=$id";
$result = mysql_query($query);
if (!$result) { echo "Delete failed: $query"; }
else
{ echo "ok"; }
mysql_close();
?>
|
At this stage you should
have a nice looking, searchable table with in-place editing and being able to
add and delete rows. Also, there are quite a few javascript libraries
referenced in books_manip_js.php and also two CSS files. I hope the comments
within the PHP scripts help to understand what is being done.
0 comments:
Post a Comment