The plugin uses jQuery DataTables to publish a table on a web page. The jQuery DataTables library has a lot of configurable options, which allow users of this library to change the look and feel.
The plugin uses a set of default options. To allow plugin users to overwrite these default options or add their own, Data Tables contains a Table options (advanced) column. Options entered in this column:
- will be added to the default plugin options, or
- overwrite the default plugin options.
Valid JSON #
Some examples #
Define in which order data table elements are shown #
{ "dom": "iptlfr" }Force ENTER key #
{ "wpda_search_force_enter": false }Load all data into the browser on page load #
{ "serverSide": false }
CAREFULL – This increases your page load time. DO NOT use this with large tables!
Remember table state #
{ "stateSave": true }Disable token check #
{ "killToken": true }The killToken option removes the token check which is added to each request by default to prevent unauthorized usage of data sources.
Add a placeholder to the search box #
{ "language": { "search": "_INPUT_", "searchPlaceholder": "Search..." } }Add an icon to the search box #
{ "language": { "search": "_INPUT_", "searchPlaceholder": "🔍" } }Change the list of shown entries (lengthMenu = items shown in list, pageLength = default) #
{
"lengthMenu": [ 10, 20, 30, 50, 100 ],
"pageLength": 30
}Turn off paging, load all data at one, set scroll bars #
{
"paging": false,
"serverSide":false,
"info":false,
"retrieve":true,
"scrollY": "150px",
"scrollCollapse": true,
"scrollX":"0"
}Overwrite some plugin defaults #
{
"pageLength":5,
"stateSave":false,
"bAutoWidth":true,
"fixedColumns": true,
"fixedHeader": true
}Disable Row Selection #
{
"select":false
}Using JavaScript functions #
The following code auto updates a table each 10 seconds
{ "initComplete": "function(settings, json) { setInterval(function() { jQuery('#'+settings.sTableId).DataTable().ajax.reload(null,false) }, 10000) }" }Add javascript function which sets color depending on content
{ "fnRowCallback": "function(row, data, index) { if (data[1]=='Z 800 E ABS') { jQuery(row).find('td:eq(1)').css('color','black'); } else { jQuery(row).find('td:eq(1)').css('color','green'); } }" }Revised JavaScript example #
Charles Godwin advised to revise the example above to make it easier to understand, less error prone, and debuggable in the browser developer. In his revision the javascript code is added to an external function. The Code Manager is used to add the external javascript function to the web page. The revised advanced table options looks like this:
Calling javascript function instead of writing inline javascript code
{ "fnRowCallback": "function(row, data, index) { setColor(row, data, index); }" }Use the Code Manager to add javascript function setColor to your web page
function setColor(row, data, index) {
if (data[1] == 'Z 800 E ABS') {
jQuery(row).find('td:eq(1)').css('color', 'black');
} else {
jQuery(row).find('td:eq(1)').css('color', 'green');
}
}Inline demo of the example above #
| Brand | Brand Type | Color | Photo | Attachments | Price | Licence Plate | Category | Fuel Type | Mileage | Engine Capacity | No Cylinders |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Brand | Brand Type | Color | Photo | Attachments | Price | Licence Plate | Category | Fuel Type | Mileage | Engine Capacity | No Cylinders |
Adding custom renderers with JavaScript #
WP Data Access allows plugin users to write their own responsive renderers.
A simple renderer
{
"responsive": {
"details": {
"renderer": "function(api, rowIdx, columns) { let render_method = jQuery.fn.dataTable.Responsive.renderer.tableAll(); return render_method(api, rowIdx, columns.filter(column => column.hidden && column.data)); }"
}
}
}This example is a one liner that hides all empty responsive columns. I strongly advise to write a separate javascript renderer function using the Code Manager or another tool which allows you to add custom javascript functions to your web pages. The rewritten function above could look like this:
Rewritten using the Code Manager
function my_renderer(api, rowIdx, columns) {
let render_method = jQuery.fn.dataTable.Responsive.renderer.tableAll();
return render_method(api, rowIdx, columns.filter(column => column.hidden && column.data));
}The advanced option can then be rewritten like this
{
"responsive": {
"renderer": "function(api, rowIdx, columns) { my_renderer(api, rowIdx, columns); }"
}
}Thanks again to my old friend Charles! :-)
Available options #
jQuery DataTable options are very powerful. I haven’t tested them all. There are just too many combinations.
Here is a link to the jQuery DataTables options page:
The plugin installs extension Responsive by default. To use this extension just select Responsive from the dropdown list for column Output. If you want to use other extensions, you need to add them manually or get the premium version. After that you should be able to use extension options in the same way.
Addtional extensions can be found here:
Contribution #
Have you written some interesting JSON to handle specific or complex tasks? Please share them here! Add a message below. Other plugin users might benefit from your experience.
Hi, thanks for the great plugin, first of all. 🙂
I am trying to use the DataTables Buttons extension, but can’t get it to work. I went here to get the CSS and JS: https://datatables.net/download/release and tried to add them via header tags or via wp_enqueue. Neither way has worked so far and I’m seeing errors like “ReferenceError: jQuery is not defined” in the Browser console. So I guess I’m doing it wrong. :/
I would appreciate any help or suggestions.
Hi Michael,
This looks like a loading sequence issue. The DataTables files are registered via the admin_enqueue_scripts hook , but they are enqueued only when they are need and just before a table is added to a page. If you add the button files via the admin_enqueue_scripts hook, they are enqueued before the DataTables and responsive files which results in an error.
To fix this issue, you can use filter wpda_wpdataaccess_prepare. The solutions could look something like this:
public function add_datatable_buttons( $html, $schema_name, $table_name, $pub_id, $columns, $table_settings ) {
wp_enqueue_script( ‘jquery_datatables_buttons’ );
wp_enqueue_style( ‘jquery_datatables_buttons’ );
}
add_filter( ‘wpda_wpdataaccess_prepare’, ‘add_datatable_buttons’, 10, 6 );
Presuming you registered the style and script via the admin_enqueue_scripts hook. More information about filter wpda_wpdataaccess_prepare can be found here:
https://wpdataaccess.com/docs/hooks/wpda_wpdataaccess_prepare/
Hope this helps. If you succeed and your table is on a public page, I would appreciate if you could add a link. 🙂 This is certainly an interesting topic for other plugin users as well.
Best regards,
Peter
Hi Peter, thanks for the help! Using the filter, I finally got the button to show. So… progress. 😀
However, when I actually click it, I get “TypeError: e.action.call is not a function”. This is what I put in the Table Options: {“text”:”My Button”, “action”:”function(){alert(‘Click!’);}” } If I remove the “action” attribute or give it an empty string (“”) as value, the error disappears… but no function gets called either, of course. ^^
This is the example I used as a template: https://datatables.net/extensions/buttons/examples/initialisation/custom
Could it be that the String value for “action” is not correctly interpreted as a function by Data Tables? But I have to put a String here or else WPDA complains. What to do? 😀
Hi Michael,
Sorry, I’m getting the same results! This seems to be scope issue, but I can’t find a solution. There are some refrences to working examples, but they are no longer available. Maybe you can drop your question on the jQuery DataTables forum? If you find a solution, I would be greatful if you can share it.
Best regards,
Peter
No luck so far and I haven’t yet found anything helpful in the DT forum either. But I found some other things that do not work and are maybe related:
1) Setting other callbacks actually gives similar errors. E.g, “rowCallback” or “drawCallback” will give me “TypeError: b.fn.apply is not a function jquery.dataTables.min.js:89:373”. This seems like the same issue, but unrelated to the buttons extension. Is the above example for “fnRowCallback” still working for you, Peter?
2) I am unable to manipulate the Data Table object using the API. I added some custom JS to tun on document.ready:
var table = null;
if ( $.fn.DataTable.isDataTable( ‘#tableID’ ) ) {
table = $(‘#tableID’).DataTable();
}
if(table !== null){
console.log(‘Doing API calls’);
table.button(0).text(‘foo’);
table.button().add(1, {text:’bar’});
table.buttons().action( function(){console.log(‘Click’);} );
table.clear();
}
None of the API calls seem to have any effect at all, however. I’ll keep searching a little, but I feel like I am at a dead end. :/
Hi Michael,
1. The fnRowCallback callback is still working. If you scroll up a little, you can see the bike demo. If scroll up a little more, you can also see the code.
2. I don’t think these settings have any effect after initialization. You need to add these options on initialization.
Can you send me the code? Maybe I can help!? If you prefer to send it in private you can use the contactform below.
Best regards,
Peter
Ok yeah, stupid question. Obviously they are still working. m( And they are for me now, too. Don’t know why they didn’t before…
So I found a workaround. This is what I put in my Table Options:
{
“dom”:”Bfrtip”,
“buttons”:[{“text”:”foo”}],
“initComplete”: “function() { addBtnAction(); }”
}
addBtnAction then is defiend in some custom JS file and essentially calls: jQuery(“#tableID”).DataTable().button(0).action( function(){alert(‘bar’);} );
If I put the “function() {…}” in double quotes here, I get the “not a function” error again. This feels pretty hacky, but at this point, I think, I’ll just roll with it. ^^”
Great you fixed it! And thank you for sharing your solution! 🙂
I wasn’t aware of the initComplete function, but I image this will be of interest for other plugin users as well.
Thanks,
Peter
hi Peter,
i have some question
1. how to ordering data? i try Default order/by not working.
2. how to limit view. i use “pageLength”:5, but not working.
3. how to hide/remove tfoot on table
4. how to show only one last record.
thank you
hi Peter,
i answear my own question.. lol
1. you must set “Allow ordering?” to Yes and than add : 1,desc
2. you must ser “Allow paging?” to Yes and than add “pageLength”:5, to Table Option
3. I use CSS to remove tfoot
4. I dont know how… lol
Thank you
Hi buzzing,
Great styling! 🙂
And how good you are answering your own questions! 😉 Regarding question 3, if you want to hide specific areas or want to change the sequence in which areas are shown, please check out option “dom”:
https://datatables.net/reference/option/dom
Best regards,
Peter
Hi Peter,
Thank you for the most amazing plug-in, videos and documentation.
I lack the in-depth knowledge. In your example above. How would you go about formating the price of the bikes to currency? You refer to installing extensions from datatables.net and there is a currency conversion plug-in. I can not work the javascript/json portion in Table options (advanced) to do it. Any help will be much appreciated.
Hi Peter, thanks for the great plugin, first of all.
There is anyway to make published table refresh without any user action (if there was an new row)?
Thankyou
Hi Pedro,
If you want the page to be refresh only when table data was added or changed, you’ll need to add server and client site logic. You might want to have a look at websockets if you want to do something like this.
Alternatively you might do a periodic page update. Here is an example of a refresh every 30 second. Much easier! 🙂
https://datatables.net/reference/api/ajax.reload()
Good luck!
Peter
Amazing plugin… thanks for investing your time in this Peter. Bravo!
I’m trying to remove the tfoot, and following the directions does not do it for me.
The following ‘Table Options’ do not work:
{
“fixedHeader”: [{
“header”: true,
“footer”: false
}]
}
{
“fixedHeader”: {
“header”: true,
“footer”: false
}
}
{
“fixedHeader.footer”: false
}
{
“fixedFooter”: false
}
{
“header”: true,
“footer”: false
}
Welcome Daz! 🙂
There is no parameter to remove the tfoot element from the table. This is part of the plugin, not jQuery DataTables. So, you cannot remove it from the table options column. There are two ways to remove it though:
(1) Change the plugin code
(2) Using javascript
If you want to change the plugin code:
Open file WPDataAccess\DataTables\WPDA_Data_Tables and remove line 216 (presuming you are on version 3.1.1). Disadvantage is you have to edit the code after every update.
Using javascript, add this javascript code to your webpage:
jQuery(document).ready(function() { jQuery(‘#your_table_id tfoot’).remove(); });
Hope this helps,
Peter
How do I limit my table to only display the top 10 rows after sorting by a column? I don’t want the end-user to see multiple pages or be able to view the rest of the data.
I have tried a few WHERE clauses and advanced options but no luck.
Table name: ‘rankme’
Column I wanted sorted DESC: ‘Score’ [column 1 in my setup]
SELECT TOP 10 * FROM rankme;
LIMIT 10;
LIMIT 10 FROM rankme;
I got this working if I put it in the WHERE clause to display only the top Score, but I need the top 10 values…
Score = (SELECT max(Score) FROM rankme)
Great you solved it Sean! Another way to limit your output is to add these options to your advanced settings:
{“pageLength”: 10,”dom”: “t”}
With pageLength set to 10 only 10 rows are shown. Setting dom to t removes the navigation elements and will unable the possibility to navigate to the next page. Might be helpful with your next publication… 🙂
Hi Peter, thanks for this great plugin.
Could I insert an independent search field for each column?
Thanks for help!
Hi Javier,
You have two option to add independent search fields:
(1) Install the full text search add-on which allows you to add search fields with a few mouse clicks
(2) Add the necessary logic yourself
Option (1) will be available soon. I planned to release the add-on in June, but that’s going to be July. For option (2) you need to have PHP and JS skills. Filter wpda_wpdataaccess_prepare allows you to add your own jQuery DataTable options to your publication. You can find the documentation here:
https://wpdataaccess.com/docs/hooks/wpda_wpdataaccess_prepare/
You will also need the documentation on this page:
https://datatables.net/reference/option/
Please start here to get an idea of the search logic:
https://datatables.net/reference/option/columns.searchable
After that you can combine your filter and search logic and add your own search fields. This can be a challenge! 🙂
Best regards,
Peter
Hello sir,
i made a table that displays the result of a MYSQL View (or table), but i need to add some filter using javascript variables or php variables,
i tried the “fnRowCallback” option, using this logic:
if (ligne_value==myvalue) => show
else => hide
but this affects only the frontend, and makes the paging a little confusing ( page1 => 5 lines ; page 2 => 10 lines….) because it’s filtered after displaying, not before.
is there any solution to filter the MYSQL request using Javascript/PHP variables instead of %USERID% or %USER%
Hi Salah,
There are two ways to add a filter to a publication:
(1) Use Data Tables to add a default where clause
Data Tables contains an advanced section which allows plugin users to add a default where clause. The where clause is added to the publication at run time. You must use Data Tables to add a where clause. For security reasons this is not possible for table based publications.
(2) Add parameters filter_field_name and filter_field_value to your shortcode
This works for both, Data Tables and table based publications. Here is the documentation:
https://wpdataaccess.com/docs/shortcodes/shortcode-wpdataaccess/
Please scroll down to the filter examples.
Best regards,
Peter
Hello,
thank you very much,
another question please :
how can i add Export buttons to Data Tables Data Table,
i did use the Datatable option : “buttons” like this :
“buttons” : “[‘copy’,’csv’,’pdf’]”
but nothing happend
Hi Salah,
It is not possible to add a button with advanced options. You need to install an additional jQuery DataTables plugin. Mitchell Judson wrote a plugin that helps you to do exactly that Your lucky day! 🙂 You can download Mitchels plugin from here:
https://github.com/judsonmitchell/wp-data-access-buttons
Hope this help,
Peter
Hello Peter,
Using this function I am able to color the full row using 1 argument, but how do i use 2 arguments / 2* “else”? (I am not a common Java script user).
“fnRowCallback”: “function(row, data, index) { if (data[2]==’Orange’) { jQuery(row).find(‘td’).css(‘backgroundColor’,’orange’); } else { if (data[2]==’Red’) { jQuery(row).find(‘td’).css(‘backgroundColor’,’red’); } else { jQuery(row).find(‘td’).css(‘backgroundColor’,’#d3d3d3′); } }”
kind regards, Erwin
Hi Erwin,
Looks like you are just missing a } to close your function. Can you add the URL of your publication if adding a } to the end of your function does not help? You can use the contactform if your prefer to send it in private.
Best regards,
Peter
thnx, this works fine now. I will use contactform next time.
Loving the plugin btw.
Regards Erwin
hello sir
how to add right align in table data (advanced settings) for numeric data
Hi Ali,
You can use CSS to right align a column. Every td element in Data Tables has a CSS class which contains the column name. Suppose your column name is employee_salary, you can right align that column by adding this CSS to your web page:
td.employee_salary { text-align: right; }
Hope this helps,
Peter
Nice plugin !
But I need advice – I have a table with two columns – “href” and “title”. I need to overwrite the output of the first column and hide the second. It should be like this using the table options:
{
“columnDefs” : [
{ “visible”: false, “targets”: [ 1 ] },
{ “render”: “function( data, type, row ) { return ‘‘ + row[1] + ‘‘; }”,
“targets”: [ 1 ]
}
]
}
But how do I insert double quotes inside a function that is specified as a double-quoted string?
‘‘ does not work …
Hi petr,
Interesting question… 🙂 Looks lik you are right! Double quotes do not work. I was able to reproduce and solve this error. Unfortunately I cannot release an update immediately as there are a number of outstanding changes. So it will be available with the next version. Hope you don’t mind. If you need a quick update for development I can upload an update, but I cannot garuantee it is bug free. Thank you for reporting!!
Good to read you like the plugin 🙂
Peter
Thank you for your quick reply, I will wait for the next version 🙂
Hi Peter,
I’m using fnRowCallback function to correctly filter data in the row, however I need to filter the data in the “child” class that’s below the row. It appears the code example only allows changes if the data is in the row and not in the expanded section below the row (if you click on the expand or compress toggle button). Is there functionality that allows changes to that child data?
Thank you!
Hi Cory,
Interesting question! The child data is added to a child table. You could use jQuery to access the child table (class=wpda-child-table). You’ll need to write more javascript of course, so you might consider to put your javascript code into a separate function. You can use the Code Manager (https://wordpress.org/plugins/code-manager/) to add a javascript function to your page. Does that help?
Sorry for letting you wait so long. Due to personal cirsumstances I was not able to answer your question earlier.
Best regards,
Peter
Thank you very much Peter, this does help! And thank you for the plugin recommendation, I was just thinking if there was a plugin that existed like Code Manager!