Index

  1. Home
  2. Docs
  3. Index
  4. Shortcodes
  5. Shortcode ‘wpdataaccess’

Shortcode ‘wpdataaccess’

Use shortcode wpdataaccess to show database tables and views on your website. jQuery DataTables is used to provide dynamic HTML tables supporting pagination, sorting and searching. Choose between flat and responsive tables.

IMPORTANT

You need to explicitly grant access to tables shown on your website! Your table and view data will not be shown on your website without this step! Follow these steps to grant access to a table or view you allow users to add it’s content to your website:

  • From the menu click “WP Data Access” > “Manage Plugin”
  • Click on tab “Front-End Settings”
  • Click on the table or view your want to grant access to (multiple selections possible)
  • Save your front-end settings

Shortcode Parameters

The following parameters are supported:

  • pub_id (publication id)
  • pub_name (publication name)
  • database (schema name)
  • table (table or view name)
  • columns (comma seperated list of columns, default: * = all columns)
  • sql_orderby (ordering, for example: 4,desc)
  • responsive (yes or no, default: no)
  • responsive_cols (number of columns shown on the table, default = 1)
  • responsive_type (modal, collapsed (default) or expanded)
  • responsive_icon (yes or no, default: yes)
  • filter_field_name (field name(s) to be filtered)
  • filter_field_value (field value(s) to filter, performs a LIKE and therefor allows wildcards)

Data Publisher

You can use the Data Publisher to create a publication. Every publication created with the Data Publisher has a unique publication id (pub_id) and a unique publication name (pub_name). If you add parameter pub_id or pub_name to your shortcode, all other parameters are taken from your publication. Only parameters filter_field_name and filter_field_value can be used to add a runtime filter.

The shortcode for a specific publication can be copied to your clipboard with the Data Publication tool and looks like:

[wpdataaccess pub_id="17"]

When you transfer a publication to another server, the publication id might change. For this reason parameter pub_name was introduced, which you can use to test and publish a publication on different hosts. Example:

[wpdataaccess pub_name="My Publication"]

User defined shortcodes

You can also add a shortcode manually. For manually added shortcodes parameter table is mandatory. All other parameters are optional. Tables are shown in flat format by default. In it’s simplest form usage looks like:

[wpdataaccess table="wp_customers"]

Note

For security reasons shortcode parameter sql_where is no longer available. Please use parameters filter_field_name and filter_field_value to add a safe filter . The plugin still allows to add a safe where clause on the Data Publisher data entry page.

Filter examples

[wpdataaccess pub_id="18" filter_field_name="lastname" filter_field_value="jose%"]

[wpdataaccess pub_id="6" filter_field_name="job,ename" filter_field_value="president,ford"]

For filters containing multiple column names and values, use a comma seperated value and make sure the array size of filter_field_name and filter_field_value are the same. The filter allows to use % as a wildcard.

Was this article helpful to you? Yes No 1

12 Replies to “Shortcode ‘wpdataaccess’”

  1. This is a great addition. I tried using a view I created table=”qEvents” and it worked. However when I tried to add more parameters it ignored them. For instance, this made no difference from the basic table view. Is the syntax correct?
    [wpdataaccess table="qEvents" responsive_type="Collapsed" sql_where="State='WA']

  2. Hi Bob,

    I’m on holiday until january 2nd. I’ll answer your question after my holiday. Hope you don’t mind!

    Best regards,
    Peter

  3. Aren’t you missing a quote at the end?
    [wpdataaccess table="qEvents" responsive_type="Collapsed" sql_where="State='WA']

    sql_where=”State=’WA'”

  4. Is there a way to disable things like search and/or pagination when using a User Defined Shortcode? I can disable the search through the CSS by hiding it, but hiding the pagination would block the user from getting to the next page.

  5. Hi Bob,

    Sorry for letting you wait for so long! I was on holiday.

    Did you solve the shortcode problem? Have you tried Nic’s suggestion? Thank you Nic!

    It might be a good idea to use the Data Publisher, which gives much more control over all the different options. Not all options supported by the Data Publisher are available from the shortcode.

    Best regards,
    Peter

  6. Hi Nic,

    You cannot disable options with shortcode arguments, but you can use the Data Publisher. From the Data Publisher you can basically use all options available for jQuery DataTables. Just create a new publication from the Data Publisher and copy the publication shortcode into your webpage. That’s all!

    You can find the documentation here: https://wpdataaccess.com/docs/documentation/data-publisher/how-to-setup-and-use-the-data-publisher/

    Don’t forget to authorize the table(s) you want to use on your we pages (menu: WP Data Access > Manage Plugin > Front-end Settings).

    Best regards,
    Peter

  7. Hi, When I try to use the wpdataaccess shortcode twice in the same page, I get the Cannot Reinitialize error. How can I disconnect the database after using the shortcode? Thank you!

  8. Hi Sam,

    You can use the Data Publisher to use multiple instances of shortcode wpdataaccess on one page. The plugin uses the publication id to create unique id’s for the DataTables. Hope this helps!

    Best regards,
    Peter

  9. Hi Peter,

    it’s me again 😀
    I want to creat an output page for a single row in a table. For example I want to create a custom overview for a product, where I can display a product picture somewhere and design the whole page by myself with the different information from the selected student profil. (guess that’s not possible with css, because the information is seen as a table?)
    Currently, I only see the possibility to show a whole table with all products. Then there is the possibility to click onto “view” and see more details from the selected row. Can I somehow design the page with the row information by myself?

    I also thought about the possibility to get the row information somehow and add this to the shortcode, so that i have a shortcode for a page and can go from a wp data publisher page to the filtered on with the details on the selected row through a button. But I have no idea how to do that.

    So all in all I need something to design the detail page of one single row.

    I hope you understand what I mean. Thank you so much for all your help!

    Best regards,
    Chiara

    1. Hi Chiara,

      Yes, you can create your own detail page! 🙂

      That can be any page, even a page on another domain. Please have a look at the following pages for more information about dynamic hyperlinks:
      https://wpdataaccess.com/docs/documentation/data-explorer/table-settings/#dynamic-hyperlinks
      https://wpdataaccess.com/2020/03/22/dynamic-hyperlinks/

      Dynamic hyperlinks is what you need to add your own custom links or icons to a table row. You can use column value substitution to make your links unique. Ones you have added your own links, you can disable the default plugin action.

      I hope the documentation is helpful enough. If not, just ask! 😉

      Good luck,
      Peter

  10. Hi Peter,
    Looks like sql_where which worked a few days ago no longer works now.
    No matter what clause condition it always returns all rows.
    Was it taken out for a security reason, as you’ve mentioned?

    I have five rows in table named “USER” published as pub_id=”1″.
    Column “USER_ROLE” has value of ”Cybernetic-Scientist” in more than one row.
    While this code [wpdataaccess pub_id=”1″] returns all rows, this code returns empty list.
    [wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value=”Cybernetic-Scientist”]

    Do you see anything wrong?
    There’s no upgrade required to the plugin for filter_field parameters to work, right?

    Best Regards,
    Vasut

    1. Hi Vasut,

      You’re using the shortcode correctly. There is an issue with the current version (3.1.5) on SVN. I added a link to your forum topic. Can you please try to update? Thanks!

      Best regards,
      Peter

Leave a Reply

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