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 read-only HTML tables supporting pagination, sorting and searching. Choose between flat and responsive tables.

Shortcode wpdataaccess must be enabled:
Settings > WP Data Access > Plugin > Shortcode [ wpdataaccess ]

Data Publisher shortcodes

You can use the Data Publisher to create publications. Every publication has a unique publication id (pub_id) and a unique publication name (pub_name) and can be used in shortcode wpdataaccess. Supported shortcode parameters:

  • pub_id (publication id)
  • pub_name (publication name)
  • filter_field_name (field name(s) to be filtered)
  • filter_field_value (field value(s) to filter, performs a LIKE and therefor allows wildcards)

Copy/paste shortcode from a publication

There’s no need to type in the shortcode. The shortcode can be copied from the Data Publisher to your clipboard and pasted into your page or post:

  • Start the Data Publisher
  • Click on link Shortcode
  • Copy shortcode to clipboard
  • Edit your page or post
  • Paste shortcode into your page or post

Example

[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"]

Live example without styling

FirstnameLastnameEmailPhone NoAddressZipcodeCityCountyCountry
FirstnameLastnameEmailPhone NoAddressZipcodeCityCountyCountry

User defined shortcodes

You can also add user defined shortcodes. Parameter table is mandatory for user defined shortcodes. All other parameters are optional. Tables are shown in flat format by default. Supported shortcode parameters:

  • 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)

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 Settings > WP Data Access > tab Front-End Settings
  • Click on the table or view your want to grant access to (multiple selections possible)
  • Save your front-end settings

Table access is automatically granted to publications created with the Data Publisher!

Example

[wpdataaccess table="wp_customers"]

Filter examples

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.

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

Styling

> Styling shortcode wpdataaccess

Was this article helpful to you? Yes No 1

29 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

  11. Hi!

    I am wondering if it is possible to directly perform write CRUD operations on the front-end using shortcode?
    So, for instance, a user fills out a form with an entry, presses submit, and their entry gets updated into the SQL database?

    Thank you!

    1. Hi Evan,

      You can use shortcode wpdadiehard, but you need to test if shortcode wpdadiehard works with your theme. Some themes are using the same parameter names and will not allow you to save data.

      Best regards,
      Peter

  12. Hi Peter!
    This Plugin is great and I’m just beginning to get into it…

    There is a problem I couldn’t solve so far:
    [wpdataaccess pub_id="1"] or [wpdataaccess pub_name="ausgabe"] is NOT working. It’s rendered as text in the font-end.

    But: [wpdataaccess table="sb_test_personen" columns="FirstName, LastName"] is working.
    Also: [wpdadiehard project_id="1" page_id="1"] is working.

    I did set the permission for the table in the settings for the front-end.

    Do you have a hint how to solve this?

    Thanks, Alex

    1. Hi Alex,

      >>> It’s rendered as text in the font-end.
      Do you have a URL I can visit to see the output of your publication?

      >>> Also: [wpdadiehard project_id="1" page_id="1"] is working.
      What is (not) displayed? Which parts are (not) working?

      Thanks,
      Peter

  13. Hi Peter,
    Great plugin, I’m having a problem displaying shortcode on web page or post, it doesn’t show the table, it works in test backend, access right correct and shortcode enabled, is there anything else i need to check to allow visitors see the table?
    Thanks,
    Joseph

    1. Hi Peter,
      Update to my earlier question: found the problem, the plugin generated shortcode is [wpdadataacess pub_id=”2″], I changed it to [wpdataacess pub_id=”2″] and it works (there is an extra ‘da’ in the automatically generated shortcode)
      Is there a way to pass a variable to the table view page, I have a Gravity Form that asks the visitor to input Container # and redirects the user to the page where my table is displayed, but I only want to display the particular Container # row with it’s defined fields in my table, if not found then present the user with a message?
      I did something like: [wpdataacess pub=”2″ filter_field_name=”FileNo,rdate,rtime,ccn,sub,po,status” filter_value=”search%”,”search1%”] where search and search1 are passed by Gravity Form, but I’m getting all the rows in the table instead of only the one in the search.
      Thanks,
      Joseph

      1. Hi Joseph,

        Are you on version 4.0.0? That version had a bug! It copied the wrong shortcode to your clipboard. This is fixed in version 4.0.1. Can you eplease update?

        Thanks,
        Peter

        1. Hi Peter,
          Updated, you were correct, I had version 4, after update the shortcode generated is correct.
          Appreciate your input, would you be able to let me know if I can allow visitor to search the database table I created and show him only the result if exists, I recreating this page:
          http://www.rutherfordglobal.com/pars.html when user enters CCN# 3959752984492 the results page will show him the corresponding record on the results page http://www.rutherfordglobal.com/results.php
          I’m building the same site in WordPress on our second virtual server that will become live once the site is completed and DNS is changed, with the help of your plugin I was able to connect to the database and did use the Data Publisher to create the table and display it on the page, but I need to be able to allow the user to search for particular container, don’t want to show him all the records (on the back end I can add the WHERE Clause and testing works just fine, but don’t know how to send over the ‘search’ criteria on the front-end (using Gravity Forms that can pass field data via query string)
          Is this possible, any help greatly appreciated
          Thanks,
          Joseph

  14. Hi Joseph,

    >>> Appreciate your input, would you be able to let me know if I can allow visitor to search the database table I created and show him only the result if exists…

    Sorry, looks like I missed that question…

    Yes, you can! :- ) Please have a look at this page:
    https://wpdataaccess.com/docs/documentation/data-publisher/adding-filters/

    Scroll down to url parameters. There is an example which shows how you can use an url argument.

    Example:
    If your CCN# is stored in column cnn_nr and you want to search for value 3959752984492, you need to add:
    ?wpda_search_column_ccnr_nr=3959752984492
    to your url.

    Hope this helps,
    Peter

    1. Hi Peter,
      Great news, thanks very much for your help, greatly appreciated
      We have a CCN column and a PO column, I created a Gravity form with a radio button choice of searching either CCN or PO that would allow the user to enter the number to search, that number can be passed to another page which contains the Data Publisher table I created.
      I’ll follow the instructions from the link you sent and see if I can achieve this task successfully.
      Thanks,
      Joseph

      1. Hi Joseph,

        I saw your column selection. If you want to allow the user to search two different columns, you might be better with two separate search fields. Or use javascript on submit? Let me know if you need help.

        Best regards,
        Peter

        1. Hi Peter,
          Yes you’re correct, we allowing search by CCN or PO, I tested Data Publisher with the information from the link you provided and the code sample, it works perfectly for CCN search, but it’s not working for PO search.
          I tried adding a hidden field in my search form that gets the search input so no matter what the person is searching (CCN or PO) I can pass on 1 variable, but because the URL parameter is ?wpda_search_column_ccnr_nr=myvariable the Data Publisher will serve all table fields. Can I change the URL parameter to include 2 search options, the user can only submit 1 search, either CCN or PO, I’m presenting him the input for the search based on his radio button choice so he only passes 1 variable, but I need to be able to tell Data Publisher if the variable is ccn or po.
          Appreciate if you can help on this
          Thanks,
          Joseph

          1. Hi Joseph,

            If you add two fields to your submit form (wpda_search_column_ccn and wpda_search_column_po) and make sure the user is allowed to enter only one of them, both field values (of which one is empty) are submitted. The plugin will use the available value and skip the other (empty value). This is a plain html solution and easier than changing your arguments.

            Let me know,
            Peter

  15. Hi Peter,
    Makes sense, thank you, I use:
    ?wpda_search_column_cnn={CNN:2}&wpda_search_column_po={PO:4}
    It continues to work correctly for any CCN number I enter, but it returns “No matching records found” when I enter a valid PO number
    Is my URL argument correct?
    Thanks,
    Joseph

    1. Hi Peter,
      Sorry update, I think it does, probably I had a wrong po # as I didn’t refresh the database view before entering the number, tried again and it does show PO correctly
      Thank you very much
      Joseph

  16. Hi Peter,
    Beautiful plugin, but I have one issue: I made a simple member database for which I use shortcodes with a variable to search on names. For most of the search patterns it works flawless, but when I use double characters, I get strange results. With this shortcode:
    [wpdataaccess pub_id="1" filter_field_name="Lastname" filter_field_value="%aab%"]
    ..I expect to get results with the pattern “aab” in the name, but it gives me every last name starting with “b”. Same result if the filter_field_value = “%ccb%” or “%ddb%”. How can this be explained?
    Best regards,
    Jan

    1. Hi Jan,

      That’s a bug! Too much sanitization! 🙂 I fixed it. It is available with the next update.

      Thank you for your contribution! 🙂
      Peter

Leave a Reply

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

seven + nineteen =