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.

Advertisements

93 thoughts on “Example on how to use jquery.dataTables and jquery.jeditable

    • You can grab the files right from the posting, I did not use any other data/files.

      Did you name your table and are you referencing that id you gave the table in the JavaScript? Other than that I do not have any clue as to what might have gone wrong.

      Did you try to follow the posting step by step?
      If so, where did things go wrong?

        • I was able to reproduce the problem.

          This happens when you have no table headers defined.
          Please add some table headers and see if the problem persists.

  1. Hi ! Thanks for this great example, it’s very useful for me.
    I have a question, when i try your code, i can only do one real edit (real edit : visual modification in the table and saving in the database). The others edits appears only in the html table but not in my database… I may be miss a thing… Can you give me some explications ?
    (the only thing that i modified, i deleted html tag “html, head, title and body” because i can’t keep this in my website, i have just permission to write in a piece of the body part, head is locked because its content is about the graphic template and session managment… Is the problem linked at this ?)

    • I forgot to talk about one test : for the first edit, the var row_id / id in edit.php is the good, but, for all other edit, the id become the value of the first column shown (the id column is “hidden”, i didn’t modify this code, my first visible colum is “surname”)
      So, for a row like “2 – Myo – Azerty – …”, for the first edit, it will me 2 as id, but if i try a new edit, the id will be Myo (so, the update query didn’t work because Myo isn’t an id)
      I say you a big thank now for your future helping ;)

      • Are you able to provide a sample database and your source code so I can try to reproduce the problem?
        My wild guess is that the row_id is not set up properly when creating the table.
        Do you get an “Update failed” error message or does the “UPD: newvalue” show up?

    • I am not sure why only one edit works and all others fail.
      Are all the relevat Javascript libraries included (check the source of the page when it has loaded).

      Omitting the html, title and body HTML tags should not have an impact.
      Also, try to debug the POST request using the browser provided developer tools to see what
      is being sent to the edit.php script (or just make that log what it is getting as data into a text file)

      • I did some new tries. I copied strictly your source code, i just modified database access data with mine and… I have the same problem ! After one edit, the next fail… (even delete because the id isn’t the right). But on my screen, for all edits, there’s “UPD : newvalue”… (for the first or the others).

        For the first edit, a print_r of POST array show :
        Array( [value] => Raspberry [id] => [row_id] => 1 [column] => 0)
        And for the second (or others are same) :
        Array ( [value] => Strawberry [id] => [row_id] => Bamford [column] => 0 )
        In the second edit, row_id take a value of an irreal “row_surname”

        A solution will be forcing the refreshing of the page ? In this case, it always can be the first edit… But i think it isn’t very optimized… (and i don’t know where/how write it)

        Also, if i can help you, my web browser says me there is a JavaScript error, it’s may be linked because it concerns a conversion on a oColumn (and my problem is like it, with the column id whose “disappears” after one edit)
        (Quote : “Uncaught exception: TypeError: Cannot convert ‘oColumn’ to object
        Error thrown at line 6461, column 10 in () in http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.0/jquery.dataTables.js:
        j=0, jLen=oColumn.asSorting.length “)

        Have you any new idea to solve it ?

        • That is quite odd, since if the SQL query will only result in the UPD: “return” string if the SQL query worked. So it should have updated it….

          Concerning the Javascript error, that is most likely the cause of the problem, are you using newer versions of the javascript libraries?

          Also, the sequence in which the Javascript libraries are included matters.

          Another problem could be the version of browser you are using which may or may not “mess” up the Javascript execution…

          • In fact, the query is valid… but it’s affect 0 line in the table

            Example :

            For the first edit :
            UPDATE books SET surname=”Apricot” WHERE id=”1″
            This query is right and affects one line in the table : the line where id=1, it’s ok, the php variable $result is true, we can show “UPD…”

            For the second edit :
            UPDATE books SET surname=”Apricot” WHERE id=”Suarez”
            The query is right but doesn’t affect any line because there isn’t any id equals with Suarez. But the query is true, it’s correctly writed with a right syntax, so $result is also true ! And we can show “UPD” again althought the query doesn’t update anything in this case.

            I tried the script with different browsers : Opera 11.64, Firefox 13 and IE 9, there is the same result…

            Where can I find new version of JS libraries ? I use the same links as you in your books_manip_js.php

            My problem is very strange… Have you successed to reproduce it ? Can you also give me your browser ans it’s version, may be there are only few to work right with this script…

  2. After many tries, i have found a cheat, just add at line 48 of books_manip_js.php this :
    alert(this.parentNode.getAttribute(‘id’));
    With this, edit works correctly !… But add and delete don’t…
    If i try to put the alert not at the 48th but at the begin of the 54th, all edits, even the first, have the surname value as id (so, all fail…)
    May be, the right problem isn’t linked with the edit script but with add and delete script whose change the value of the id… ? I have so few experience with JQuery… =S

    • I success to solve the problem, maybe it isn’t a clean solution but it works good with all functions (edit, add n delete), i replace the content of the callback function (line 36 and 37) by this :
      window.location.reload();
      With this forced refresh, all is right ! =)
      If a day, you found another solution without reload/refresh, tell me it, else, i will keep this solution.
      Thanks for all, i was happy to talk with you, and maybe, see you soon for a next problem xD (i don’t hope because i dislike disturb you ^^)
      Good luck for your next works.

      • I was able to reproduce it. It seems, when I was testing I was manually reloading the table and thus the bug never showed up..

        Maybe the is some programming error in the callback function but I am not good enough at Javascript to fix this. Your workaround works, although you only see the UPD: entry for a split second due to the “screen” reloading. I have added the window.location.reload(); line to the source code.

        Thank you very much for pointing out the error!

  3. sorry my english
    if i use the add botton, with words (portugueses) like (eça), in the table show (eça). With the update no problem, because have the “$value = utf8_decode($value); – line 50). How put this in add.php.
    great job and thank you for share.

    • The simple approach would be to do the following:

      $surname = $_POST[‘surname’];
      $name = $_POST[‘name’];
      $title = $_POST[‘title’];
      $published = $_POST[‘published’];
      $whenread = $_POST[‘whenread’];

      $surname = utf8_decode($surname);
      $name = utf8_decode($name);
      $title = utf8_decode($title);
      $published = utf8_decode($published);
      $whenread = utf8_decode($whenread);

      This should work, please try and test it.

      A more elegant solution would be to just loop through the $_POST Array and simply decode everything it has with utf8_decode();

      That could be done like this:

      function u8dec(&$value, $key)
      {
      $value = utf8_decode($value);
      }

      array_walk($_POST, ‘u8dec’);

      • Hello hexeract

        I try the first solution and works!!!!!!. I spend hours yestarday to fix this(trial and error), but no sucess. And you with a few lines solved. Thank you very much.
        i don´t know if you have the idea of the importance of this script. In the forum of datatables many people search for this solution, i search for this a long time, and one day i have the luck to found your site.
        I found this – http://code.google.com/p/jquery-datatables-editable/, but the problem for many people like me , that don´t now programing(but understand a litle bit, read), is the conection to database to (add, delete, and update), as you can see they don´t teach how do that, therefore yours scripts is amazing.If you one day have the solution for de delete.php, to avoid the refresh, please post here.
        Thank you very much for share.

        And thank you to google tradutor to help me write this words-)))

        • Nice to hear the “workaround” works!

          The main reason for using utf8_decode in edit/add is that the database was not set up with UTF-8 as charset but I simply used the default (latin1)

          You could set put the database (and tables) as utf-8 but you then need to remove the utf8_decode lines and then either modify the queries to return latin1 (for the output) or make the PHP output UTF-8 characters….

          Creating add.php/delete.php did take some time to develop and was more or less a trial and error debugging until I figured out what was provided via $_POST and how to use it.

          • yestarday i do that, set up the database with utf-8 and the tables and save the files add.php,delete.php,books_manip_js.php,books_edit.php with coding utf-8. Then delete line “$value = utf8_decode($value);”. Like this solve the problem in the table, but in the database have the same problem. Your solution solved both side.
            For the native portuguese language work, using “utf8_unicode_ci” in database and tables.
            The files save as coding ansi and put the code above in add.php.
            i use 10 columns.

            thank you

            https://github.com/n1crack/IgnitedDatatables-native-php-version

          • The charset encoding of the files themselves is not the problem, but getting PHP to output strings as UTF-8 so the browser can recognise/interpret the characters on screen correctly is.

            The table probably had the correct UTF-8 strings but you would also modify the MySQL query string so it returns utf-8 strings. Unless PHP does something stupid and “magically” converts strings around (which I do not know if it does).

            Anyways, as long as it now works for you, I am happy :)

  4. Sorry for noob question, but I would like to change the column names to something prettier than the mysql column names in the table, and also choose which columns I want from mysql, not all of them. How do I do that?

    • Untested, but my guess is to try and use a view of a table.
      You can swap out the column names if you build yourself a key/value store which has a “column -> fancy column name” and just do a lookup and use that string instead.

      Does that help you?

  5. If you one day go try put filter at the files php, please send me. And this is the last tables I now but very powerful – http://tablefilter.free.fr/. I found a ftp with more 300 books of programing, All in english, if you give me a e-mail i send to you. It hard to me learn in english but in portuguese there are so few good books avaible.
    Mary Crhistmas

    • Yes, you should be able to follow the steps in the article to get a working demonstration.
      What was your result, what does your (php) code look like and what versions of the Javascript libraries were you using?

      • Thanks for your fast reply.
        I put the this code

        $(document).ready(function() {

        $(\’#example\’).dataTable( {
        “bPaginate”: true,
        “bStateSave”: true,

        } );

        when I add the tabletool code it’s not working. Seems like the problem is tahat the line “sDom”: ‘Tlfrtip’,
        is not correctly interpreted beacause of the ”

        I think that whith this query, i don’t add a id (mysql insert id???) and my table have the first column with id.

        Thank you and sorry for my englisH

    • ok now i can edit ! but i have siome issues when i try to add a new row with the add button.
      i have a javascript alert :DataTables warning (table id = ‘example’): Requested unknown parameter ’11’ from the data source for row 43
      but the row is added and i checked my database the new entry is added too?

      • I never got the add/delete row functionality to work the way I wanted hence I never added that to the original blog posting.
        It was easier for me to simply write a “Add Record” php Script or just bulk add data using the CSV import of MySQL.

    • You need to find out what your update.php Script is getting so I would advise to add some logging to that script.
      Either it is getting nothing in the POST request (bad) or you are getting data you are not expecting and thus updating the database fails…

      Another approach would be to use Fiddler2 if you are using a Windows machine to log all http(s) requests between your browser and the target.

      • OH speak slowly please…lol
        I’m a noob in javascript and php…and english is not my native language !!!
        I didn’t understand what you said lol.
        Just have this alert but the data added in the table and in my Sql table are corrects.
        But when i try to edit the new rows added i can’t…
        Thank you very much
        here is a link if you want :
        http://www.solutionconfort.fr/V2/datatable.php

  6. I have the same probleme with the delete function, it’s working only if i manually refresh the page..

    • To avoid refresh in file book_manip_js after this line – oTable.fnUpdate( sValue, aPos[0], aPos[1] ); write this
      window.location.reload();

      • As said, I did avoid the Add/Delete Row functionality and just created a specific Add Record / Delete Record page. It was easier than trying to fiddle with JavaScript since I am not a JavaScript programmer myself…

        • ok ! window.location.reload() was still in the code, i add ” at rel= 11″ and it’s fine now , no javascript alert.
          But when i tried to edit a line after adding a new row, i have an alert “Cell cannot be updated(Server error)”. Then i click ok and i can edit.
          i’m gonna change the message in this alert by “error : let’s try again” ??? loool
          thaks ronal and hexeract

          • zazze try http://www.jtable.org/, i use this to input data, and the hexerect to edit, because have inline edition. The only (big problem), is the php files don´t have search

    • thank you zazze, i hope you try input search in php files jtable. Still have problem with edit after insert a row. Thats why i use both.

  7. i give up. Everything is fonctionnal, edit/add/delete/search/filter/export, but when i add or delete a row, i can not edit another without refreshing the page before…

    • zazze, i can´t put search by column, can you put here the complete code off file books_manip_js.php

  8. var asInitVals = new Array();

    $(document).ready(function() {
    var oTable = $(\’#example\’).dataTable( {
    “bPaginate”: true,
    “bStateSave”: true,
    “bAutoWidth”: true,
    “sDom”: \’Tlfrtip\’,
    “oTableTools”: {
    “sSwfPath”: “http://solutionconfort.fr/V2/TableTools/swf/copy_csv_xls_pdf.swf”
    },
    “oLanguage”: { “sProcessing”: “Traitement en cours…”,
    “sLengthMenu”: “Afficher _MENU_ elements”,
    “sZeroRecords”: “Aucun element a afficher”,
    “sInfo”: “Affichage de l\’element _START_ a _END_ sur _TOTAL_ elements”,
    “sInfoEmpty”: “Affichage de l`\’element 0 a 0 sur 0 elements”,
    “sInfoFiltered”: “(filtre de _MAX_ elements au total)”,
    “sInfoPostFix”: “”,
    “sSearch”: “Rechercher:”,
    “sUrl”: “”,
    “oPaginate”: {
    “sFirst”: “Premier”,
    “sPrevious”: “Precedent”,
    “sNext”: “Suivant”,
    “sLast”: “Dernier”
    }
    }
    } );
    /* Apply the jEditable handlers to the table */
    $(\’td\’, oTable.fnGetNodes()).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]
    };
    window.location.reload();
    },
    “height”: “14px”
    } );

    $(“tfoot input”).keyup( function () {
    /* Filter on the column (the index) of this element */
    oTable.fnFilter( this.value, $(“tfoot input”).index(this) );
    } );

    /*
    * Support functions to provide a little bit of \’user friendlyness\’ to the textboxes in
    * the footer
    */
    $(“tfoot input”).each( function (i) {
    asInitVals[i] = this.value;
    } );

    $(“tfoot input”).focus( function () {
    if ( this.className == “search_init” )
    {
    this.className = “”;
    this.value = “”;
    }
    } );

    $(“tfoot input”).blur( function (i) {
    if ( this.value == “” )
    {
    this.className = “search_init”;
    this.value = asInitVals[$(“tfoot input”).index(this)];
    }
    } );
    $(\’#example\’).dataTable().makeEditable({
    “sAddURL”: “add.php”,
    “sDeleteURL”: “delete.php”,
    “sAddNewRowFormId”: “addme”
    } );
    } );

  9. And you must add before

    I have 12 columns…you have to adapt this according to the number of column you have

  10. hello Ronald. Good to hear that’s it’s OK for you, sorry for the delay i just seen your comment. I’m trying now to put conditional row (according to the value of the row)

    • zazze, this table still have a problem, if i want work with 1.000.000 rows , they don´t work (crash the browser), i try with http://tablefilter.free.fr/, but 13.000 rows have same problem. Tables is amazing thing for deal with data , but don´t work if i want deal with many

  11. oooh..i think i won’t have so many rows…what about ajax configuration? i think that server side process will always make this kind of problem.

  12. Thanks for a great post. I have a question on use your method to wordpress. In my site I am using a form to submit the data and stored it in DB. But DB structure is little bit different. It uses a “pivot query” show the table. I have four coloums “submit_time”,”form_name”,”field_name” and “field_value”. “submit_time” is actually the row id in DATE_FORMAT. “form_name” is for a particular Form, where “field_name” is the a cell element which serve as a column of that particular form and obviously “field_value” is the value of that field. You may view the table at http://www.twostar.co.in/test-form/‎. My question is how I’ll write the jeditable code for my table and also the update.php and delete.php? Could you please help me to solve it out? Thanks in advance.

    • They key here is that in books_edit_js.php the row_id is used which corresponds to the column ‘id’ where in your case you need to use your submit_time column as a reference and use that to identify the row to be deleted and/or edited. So your update.php and delete.php
      are given the submit_time and thus you can work on the specified row.

      If you can provide me with an archive of the PHP scripts and database dump I will have a look at it.

      • The DB Query is as below:
        SELECT `submit_time` AS ‘Submitted’,
        max(if(`field_name`=’State’, `field_value`, null )) AS ‘State’,
        max(if(`field_name`=’City’, `field_value`, null )) AS ‘City’,
        max(if(`field_name`=’Tehsil’, `field_value`, null )) AS ‘Tehsil’,
        max(if(`field_name`=’Village’, `field_value`, null )) AS ‘Village’,
        max(if(`field_name`=’Name’, `field_value`, null )) AS ‘Name’,
        max(if(`field_name`=’Address’, `field_value`, null )) AS ‘Address’,
        max(if(`field_name`=’Ward’, `field_value`, null )) AS ‘Ward’,
        max(if(`field_name`=’Mobile’, `field_value`, null )) AS ‘Mobile’,
        max(if(`field_name`=’Decrepation’, `field_value`, null )) AS ‘Decrepation’,
        max(if(`field_name`=’Submitted Login’, `field_value`, null )) AS ‘Submitted Login’,
        max(if(`field_name`=’Submitted From’, `field_value`, null )) AS ‘Submitted From’,
        GROUP_CONCAT(if(`file` is null or length(`file`) = 0, null, `field_name`)) AS ‘fields_with_file’
        FROM `star_cf7dbplugin_submits`
        WHERE `form_name` = ‘Final Form’
        GROUP BY `submit_time`
        ORDER BY `submit_time` DESC
        LIMIT 0,100

        The PHP script is not created by me. It is a wordpress plugin. You may download it from the link below http://downloads.wordpress.org/plugin/contact-form-7-to-database-extension.2.5.2.zip

  13. I was not aware that you were talking about a real wordpress plugin which you want to extend/enhance. My best advice would be to contact the author of the plugin if he could extend/add the functionality you want/need.

    If you are the only one editing the data you might want to look at some PHP script that will allow you direct manipluation of the database tabes, for example phpmyadmin (http://www.phpmyadmin.net/home_page/index.php), Adminer (http://www.adminer.org/) or something similar…

  14. Thanks for the reply. Actually I am not want to extend/enhance the plugin. That plugin only store my form data to my database and the author of that plugin help me to setup the query. I am displaying the data by DataTable plugin and try to build up the code with jeditable for front end editing. I do understand the codes but not an expert in programming. Your post will really help me a lot to understand the coding. I’ll try my best to setup the code.

  15. Hello very good script … but I’m having a little problem … could not change the fields of script references the table of the database I tried to adapt … could give me a force … indicating what information I would have to change the script to function properly … I thank you …

  16. yes that’s right … but when I change the information in the various errors … or else could redo more clearly the part of php or comment which can be changed … I thank you … only these two files books_edit_js.php – books_edit.php

    • You can use a lookup table to change the column names to something you want to display on the web page.
      Sadly, WordPress is not really good at displaying source code in the comments, so I had to resort to Pastebin.
      Check the following source code snippet on how you could do that:

      Source Code

      Hope it helps.

      • could have altered the file line books_edit.php -> $query = “update $table set $fieldname = ‘$value’ where idB = ‘$id'”;
        $result = mysql_query($query); and file these lines books_edit_js.php

        while ( $row = mysql_fetch_assoc($result) )
        {
        $fieldname = $row[‘Field’];
        if ( $fieldname == ‘idB’ ) continue;
        echo “$fieldname”;
        }

        and

        while ($row = mysql_fetch_assoc($result))
        {

        $id = $row[‘idB’];
        echo “”;

        unset($row[‘idB’]);

        foreach ($row as $key => $value)
        {
        echo “$value”;
        }
        echo “”;
        }
        and it worked correctly before the camp was id moved to idB and the database

        • If I understand you correctly, you changed the name of the column that contains the unique identification for each row from ‘id’ to ‘idB’`? Your source code looks incomplete..

          I would not change the column name for the id but if you must, you need to check that you do have a (hidden) value in the table, please re-examine the lines 89 to 102 of the ‘books_edit_js.php’ source. You probably also need to alter ‘id’ (line 93) on the left side to idB and also modify the row_id: field at the top of the source code from ‘id’ to ‘idB’ (line 39).

          Does this help you?

  17. you mean creating an id field in the table and leave it hidden and use the script without touching it … until it is a good idea … ‘ll see possibilidde to do this …

    Thank you …

  18. hello anyone know how to not show all fields of the structure and change the name to display correctly …

    thank you…

  19. I did a little different but funcinou is that I’m using postgresql and commands change … the problem now is I’m using the update file and books_edit.php books_edit_js.php …

    but already gave you an idea of how to make …

  20. paylaşımınız için teşekkürler mean thank you danke şukran good job pefect and simple

  21. When I select an entry in the surname column the delete button becomes active but the delete doesn’t work. Even after a manual refresh.

    • Strange. I can only assume you are either not sending the proper row id to the delete.php or delete.php does something wrong all together.
      If you can, add some debug output (to a file) to your delete.php to see of you are getting the data you are expecting.

  22. Same as Doug … the delete code is not working :( I quit with this shit ….. datatables is nearly impossible to code!

    • I agree. It took me hours to get the example working properly and only after I plastered the add.php/delete.php with debug code to see what is actually being sent as data and what the database does with the data I managed to understand what went wrong where and then could fix the code.

      • Hi all !
        I had the same type of problem with the delete (yes, i already posted comments, in June ’12, about this) and I tried to fix it… I remember to have finding a way to avoid problems but i haven’t the source code under hand and my project is too older to that I keep memories about it with precision. If you’re interested, i can search files and provide you in the next days, even if there aren’t perfects, they might be helpful for you ! ;)

  23. @hexeract so how did u manage to solve this? To be honest I’m now trying/checking jtable and the code there is really really easy! Btw I’m really interested in your solution @myozhotis This would be great!!!! :) -> killafrog@gmail.com

    • As I said, it took me quite a lot of debugging code to find out what gets passed to delete.php and then to check if it will execute the proper SQL query. I ran into the problem that I never passed a rowid (which in my case corresponds to a unique ‘id’ column of the database) to delete.php
      and thus the delete.php never executed the correct SQL query.

      • At this point I think I’ll continue my work with jTable. Really thx for your time Hex :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s