Notice: PHP's XML extension is not available. Please contact your hosting provider to enable PHP's XML extension. in /var/www/html/wp-content/plugins/jetpack/modules/shortcodes/class.filter-embedded-html-objects.php on line 246

PHP and DataTables

We have been porting all our tables in a PHP/JS business app to use DataTables – in order to provide a more consistent interface (we have a mixture at the moment where different developers have worked on things over the years). At the same time as this – we have been writing a nice PHP class for actually building the tables, since many of our queries to build the data for these tables are quite complex – and using javascript to load the data is not always possible due to legacy code.

The PHP class is used to build the actual table structure – and we simply pass it an array of data, as well as a JSON file which holds the config for the table – containing classes, columns (including data type, width, styles etc).

We wanted to be able to call this one PHP function to create the table – AND create the Javascript to apply the data tables code AND have all ordering working. Many of the modules in our application build a PHP page and then load it in to a div via AJAX. This method works really well as it is so fast.

Well… We have managed to get all of this working and I thought I would share our experiences and code here so that others can use it or help us improve it.

We store the JSON file in a folder structure that allows us to file things sensibly. eg. the file below is stored as:

/lead/search_lead.json

Here is an example of the JSON file that we use to store information about how the table will be built:

{
    "name": "search_lead",
    "author": "sam edney <sam@cetasoft.net>",
    "created": "2014-09-01",
    "modified": "2014-09-05",
    "description": "displays results from the search leads page",
    "type": "table",
    "rownumbers" : false,
    "classes": ["display"],
    "styles": [],
    "columns": [
        {
            "id": "leadid",
            "fieldname": "leadID",
            "caption": "",
            "width": "30",
            "datatype": "lead_link"
        },
        {
            "id": "projectnumber",
            "fieldname": "projectID",
            "caption": "!!captionFieldProjectNumber",
            "width": "100",
            "datatype": "project_id_link"
        },
        {
            "id": "client",
            "fieldname": "companyID",
            "caption": "!!captionFieldCompanyName",
            "width": "100",
            "datatype": "company_link"
        },
        {
            "id": "contact",
            "fieldname": "contactID",
            "caption": "!!captionFieldContactName",
            "width": "100",
            "datatype": "contact_link"
        },
        {
            "id": "title",
            "fieldname": "title",
            "caption": "!!captionFieldTitle",
            "width": "100",
            "datatype": "text"
        },
        {
            "id": "budget",
            "fieldname": "budget",
            "caption": "!!captionFieldBudget",
            "width": "100",
            "datatype": "currency",
            "displaytotal": [
                {
                    "type": "columnsum"
                }
            ]
        },
        {
            "id": "last_contact_date",
            "fieldname": "lastCommentDate",
            "caption": "Last Comment",
            "width": "100",
            "datatype": "datetime_utc_to_local"
        }   
    ]
}

We also plan on adding a few more properties to this – such as “tooltip”.

Anyway – we build the table with a simple call:

//build the array of defaults for this table
$table_array        = array();
$table_array['id']  = "search_lead_results";

//get all the active leads (from the database)
$data = get_leads("active");

//print the table to html
echo print_table($data, "lead/search_lead", $table_array);

And this “print_table” function does a few things. the code for it is in this paste-bin. http://pastebin.com/t3LzdA1N

  1. Creates a table with the ID, classes and styles from the $table_array parameter and JSON file.
  2. Creates a table header.
  3. Adds column headings for each of the columns in the JSON file.
  4. Creates a table body.
  5. Adds rows for all the records in the $data array – but while doing so checks for the “datatype” property. Using this property we are able to leave some of the business logic to the table itself. Uses of this so far have been converting dates to local time from UTC, or building a link to more detail.
  6. Also – if the column definition has a “displaytotal” property, we do whatever is required to calculate a total of that column.
  7. Adds a footer to the table
  8. Adds totals to any columns that required one.
  9. Adds a <script> tag.
  10. Adds jQuery code to apply the DataTables code to the new table in the .ready() event.

The “datatype” property is the most important in our JSON file since it is used to do many things. It controls the formatting, the cell alignment, it can convert date/times to local or back to UTC, and it can even add an  <a> tag or javascript to perform an action.

In the example above, we are only including the company ID in the dataset. However, using the datatype “company_link” replaces that ID with the company name and a link to open up the full details for that company.

We are also passing the “lastCommentDate” to the function which we store in UTC. Our table will automatically replace that date with the date / time in the users time zone – thanks to us adding the data type of “datetime_utc_to_local”.

All of this works really well. The first problem we ran in to is that we could not get date ordering to work well – and I think this little hack we came up with is a nice solution.

As we build the table, we insert the SQL formatted date (since in that format dates are alpha-sortable) in to the date column before the nicely formatted date. We do this for all our “date”-type datatypes.  Of course we wrap it in a DIV and make sure it is hidden. Then when you order by that column, it thankfully takes in to account the hidden text value and ordering works well!

We also wrap the content in our own text tags (!!#) so we can later easily find the text later.

//we have these set up to apply prefix and suffix to hidden tags
$hide_open      = "<div class='remove-before-export'>#!!";
$hide_close     = "!!#</div>";

//replace the value we will add to the cell with a modified version
$object->set_value($hide_open . $object->get_value() . $hide_close . print_nice_date($object->get_value()));

The next problem was that when you export – you would get the hidden text in your file (CSV etc). This was fixed by editing the code on the export buttons as follows.

var current = $('#' + table_id).position();

var table = $('#' + table_id).dataTable({
 "scrollY": get_window_height() - current.top - 5,
 "paging": false
});

var export_defaults = [
{
 "sExtends":"copy",
 "fnClick":function(nButton, oConfig, flash) {

 var table_data = this.fnGetTableData(oConfig);
 var new_table_data = table_data.replace(/#!!.+?!!#/g, "");

 this.fnSetText(flash, new_table_data);

 }

},
{
 "sExtends":"xls",
 "fnClick":function(nButton, oConfig, flash) {

 var table_data = this.fnGetTableData(oConfig);
 var new_table_data = table_data.replace(/#!!.+?!!#/g, "");

 this.fnSetText(flash, new_table_data);

 }

}

];

// we only want the copy and export xls button
$.fn.dataTable.TableTools.defaults.sSwfPath = "/swf/copy_csv_xls_pdf.swf";
$.fn.dataTable.TableTools.defaults.aButtons = export_defaults;
var tt = new $.fn.dataTable.TableTools( table );
$( tt.fnContainer() ).insertAfter('div.dataTables_filter');

The important part here is below, as this removes the SQL format date before it is saved to disk or clipboard.

var new_table_data  = table_data.replace(/#!!.+?!!#/g, "");

I’ll try to separate out this class from all our other code that it depends on, so that we can open-source it – as it makes building complicated data tables really easy – especially if you have legacy code that doesn’t really comply to modern JS table-building requirements.

Let me know what you think.