1. Home
  2. Docs
  3. Index
  4. Data Publisher
  5. Advanced settings

Advanced settings

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 the library to change the look and feel of a DataTable.

The plugin uses a set of default options. To allow plugin users to overwrite these default options or add their own, the Data Publisher 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

Your advanced settings MUST BE valid JSON! If you enter invalid JSON, changes are your publication will not show up at all. The plugin supports JSON validation for this column. This feature can be disabled (how to disable to JSON validator…). Be aware, even if you disable the JSON validator your advanced settings still must be valid JSON (use an online validator instead).

Β» What is JSON?

Some examples

Define in which order areas are shown:

{
  "dom":"iptlfr"
}

Load all data into the browser on startup:

{
  "serverSide":false
}

Remember table state:

{
  "stateSave":true
}

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
}

Add javascript function which sets color depending on content (more complex):

{
  "paging":false, 
  "serverSide":false, 
  "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'); } }" 
}

In the last example, I’m using a javascript function. Please notice, that the function is written between double quotes. This is to maintain valid JSON. The value of property fnRowCallback is a string, which contains the function code.

Function fnRowCallback checks the value of column Brand Type. If the type is Z 800 E ABS, the text color is set to black. Otherwise it is set to green. You can see this code in action in real time below.

BrandBrand TypeColorPhotoAttachmentsPriceLicence PlateCategoryFuel TypeMileageEngine CapacityNo Cylinders
BrandBrand TypeColorPhotoAttachmentsPriceLicence PlateCategoryFuel TypeMileageEngine CapacityNo Cylinders

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:
https://datatables.net/reference/option/

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. After that you should be able to use extension options in the same way. Just add them to column Table options (advanced).

Addtional extensions can be found here:
https://datatables.net/extensions/index

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.

Was this article helpful to you? Yes 1 No

25 Replies to “Advanced settings”

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

  2. 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/2020/03/25/filter-wpda-wpdataaccess-prepare-to-add-custom-options-to-a-publication/

    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

    1. 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? πŸ˜€

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

    1. 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. :/

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

        1. 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. ^^”

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

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

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

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

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

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

  8. 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
    }

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

  9. 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;

      1. 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… πŸ™‚

  10. Hi Peter, thanks for this great plugin.

    Could I insert an independent search field for each column?

    Thanks for help!

    1. 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/2020/03/25/filter-wpda-wpdataaccess-prepare-to-add-custom-options-to-a-publication/

      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

  11. 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%

    1. Hi Salah,

      There are two ways to add a filter to a publication:

      (1) Use the Data Publisher to add a default where clause

      The Data Publisher 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 the Data Publisher 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 Publisher and table based publications. Here is the documentation:
      https://wpdataaccess.com/docs/documentation/shortcodes/shortcode-wpdataaccess/
      Please scroll down to the filter examples.

      Best regards,
      Peter

      1. Hello,
        thank you very much,

        another question please :
        how can i add Export buttons to the Data Publisher Data Table,
        i did use the Datatable option : “buttons” like this :
        “buttons” : “[‘copy’,’csv’,’pdf’]”
        but nothing happend

Leave a Reply

Your email address will not be published. Required fields are marked *