Monthly Archives: March 2012

Standard

Posted by

Posted on

2012/03/16

Posted under

howto, Javascript, mysql, php, Uncategorized

Comments

95 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’:

<?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).

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’:

<?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>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" 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:

<?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’:

<?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>Books</title>
<style type="text/css" title="currentStyle">
    @import "http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.0/css/jquery.dataTables.css";
    @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’:

<?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");


file_put_contents('test.txt', file_get_contents('php://input'));

$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’:

<?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.