WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
Select Page
Popular Search formimportsqlqueryexport

WP Data Access

  • Getting started
  • Installation
  • What’s new?
  • Patches
  • Known limitations
  • FAQ

Data Tables

  • Getting Started
  • Creating and publishing
  • Responsive data tables
  • SEO friendly data tables
  • Adding hyperlinks
  • Extension Manager
  • Style Manager
  • Language settings
Styling
  • Style Manager
  • Premium styling
  • Global styling
  • Code Manager Styling
  • Manual styling
  • Styling issues
Filters
  • Static filters
  • Interactive filters
    • URL parameters
    • Search Panes
    • Search Builder
    • Search form
    • Advanced search
    • Geolocation search
Export & other buttons
  • Export to PDF, CSV, Excel, SQL
  • Print & copy buttons
  • Column selection buttons
  • Custom buttons
Advanced features
  • Advanced settings
  • Large tables & performance
  • Progressively SHOW MORE
  • Custom queries
  • Custom Post Types
  • Row grouping
  • Responsive master-detail
  • Master-detail tables
  • Non admin user access
Demos & tutorials
  • Demos
  • Tutorials

Data Apps

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
Project Templates
  • Project Templates
  • Creating templates
  • Table Settings
  • Relationships
  • List Table
  • Data Entry
  • Reconcile
Data Forms
  • Data Forms
  • Overview
  • Project Demo
  • Page Demo
Advanced features
  • Adding filters
  • Custom validation
  • Add less/more button
  • User roles
  • Managing roles and users
  • Advanced table options
  • Advanced lookups
  • Front-end toolbar
  • Transfer to other WP site
Demos & tutorials
  • Demos
  • Tutorials

Data Explorer

  • Getting started
  • Navigation
  • Explore tables & views
  • Manage tables & views
  • Naming conventions
Manage Actions
  • Actions
Manage Settings
  • Settings
  • Table Settings
  • Column Settings
  • Search Settings
  • Geolocation Settings
  • Dynamic Hyperlinks
  • Dashboard Menus
Data Management
  • Import CSV & SQL files
  • Run SQL script files
  • Data Backup
Problems & solutions
  • InnoDB #rows estimation
  • Import file too large
  • Transfer to other WP DB

Remote Databases

  • MySQL | MariaDB
Other DBMS
  • Heterogeneous Connections
  • Using ODBC | JDBC
  • Connect to SQL Server
Premium Data Services
  • Remote wizard
  • START HERE
  • Errors and messages
  • Limitations
  • Tutorials
  • Remote Databases
    • Remote databases
    • SQL Server
    • PostgreSQL
    • Oracle
    • MariaDB | MySQL
    Remote Data Files
    • Remote data files
    • CSV files
    • JSON files
    • MS Access
    • XML files
    • Public URL
    • Synchronization
    Advanced features
    • Performance
    • Indexes

SQL

  • Getting Started
  • Tutorials
  • Visual Query Builder
  • Writing queries
  • Data exchange
  • Stored procedures
  • Database triggers
  • Scheduled queries

Business Intelligence

  • Getting started
  • Dashboards
  • Sharing dashboards
Dashboards Widgets
  • Dashboard Widgets
  • Publication Widget
  • Project Widget
  • Chart Widget
  • Custom Code Widget
  • Database Widget
  • Sharing Widgets

Table Design

  • Getting started
  • Introduction
  • Basic mode

Plugin Settings

  • Getting started
  • Plugin
  • Back-end
  • Front-end
  • Dashboard
  • Data Forms
  • Data Tables
  • Data Backup
  • Uninstall
  • Manage Repository
  • Manage Roles
  • System Info

Code

Shortcodes
  • Overview
  • wpdataaccess
  • wpdadataproject
  • wpdadataforms
  • wpdadiehard
  • wpdageomap
  • wpdawidget
Hooks & filters
  • Overview
  • Hooks
    • Overview
    • wpda_add_search_actions
    • wpda_add_search_filter
    • wpda_before_list_table
    • wpda_after_list_table
    • wpda_wpdataaccess_prepare
    Filters
    • Overview
    • wpda_column_default
    • wpda_before_simple_form
    • wpda_after_simple_form
    • wpda_construct_where_clause
API
  • WP Data Access API
  • Remote database access
  • CRUD Example
  • Extensions
    • Alternative search algorithm
    • Alternative buttons extension
Code Manager
  • Overview
  • Write code
  • Share(d) code

wpdataaccess

Table of Contents
  • Data Table shortcodes
    • Copy/paste shortcode from a publication
    • Examples
    • Inline demo without styling
  • Filter examples
  • Styling
Use shortcode wpdataaccess to bring database tables and views to your website and WordPress back-end. jQuery DataTables is used to provide dynamic read-only HTML tables supporting pagination, sorting and searching. Choose between flat scrollable and responsive tables.
Shortcode wpdataaccess must be enabled:
Plugin Settings > Plugin > Shortcode [ wpdataaccess ]

Data Table shortcodes #

Use Data Tables to create data tables. Every publication has a unique publication id (pub_id) and a unique publication name (pub_name) which 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 Data Tables to your clipboard and pasted into your page or post:
  • Start Data Tables
  • Click on link Shortcode
  • Copy shortcode to clipboard
  • Edit your page or post
  • Paste shortcode into your page or post

Examples #

[wpdataaccess pub_id="17"]
[wpdataaccess pub_name="My Publication"]

Inline demo without styling #

FirstnameLastnameEmailPhone NoAddressZipcodeCityCountyCountry
FirstnameLastnameEmailPhone NoAddressZipcodeCityCountyCountry

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 Data Tables data entry page.

[wpdataaccess pub_id="6" filter_field_name="job,ename" filter_field_value="president,ford"]
[wpdataaccess pub_id="18" filter_field_name="lastname" filter_field_value="jose%"]
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 #

  • Premium styling
  • Global styling
  • Code Manager styling
  • Manual styling
Share This Article :
  • Facebook
  • Twitter
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on 2022-12-19
Overviewwpdadataproject

29 Comments

  1. bob Hall
    bob Hall on 2019-12-27 at 9:57 pm

    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']

    Reply
  2. Peter Schulz
    Peter Schulz on 2019-12-30 at 10:02 am

    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

    Reply
  3. Nic
    Nic on 2020-01-05 at 10:48 pm

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

    sql_where=”State=’WA'”

    Reply
  4. Nic Cross
    Nic Cross on 2020-01-06 at 1:41 am

    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.

    Reply
  5. Peter Schulz
    Peter Schulz on 2020-01-06 at 2:00 pm

    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 Data Tables, which gives much more control over all the different options. Not all options supported by Data Tables are available from the shortcode.

    Best regards,
    Peter

    Reply
  6. Peter Schulz
    Peter Schulz on 2020-01-06 at 2:06 pm

    Hi Nic,

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

    You can find the documentation here: https://wpdataaccess.com/docs/data-tables/creating-data-tables/

    Best regards,
    Peter

    Reply
  7. Sam
    Sam on 2020-03-02 at 8:51 pm

    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!

    Reply
  8. Peter Schulz
    Peter Schulz on 2020-03-02 at 9:52 pm

    Hi Sam,

    You can use Data Tables 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

    Reply
  9. Chiara
    Chiara on 2020-04-28 at 1:53 pm

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

    Reply
    • Peter Schulz
      Peter Schulz on 2020-04-28 at 2:24 pm

      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 page for more information about dynamic hyperlinks:
      https://wpdataaccess.com/docs/data-explorer-settings/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

      Reply
  10. Vasut
    Vasut on 2020-07-11 at 11:29 pm

    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

    Reply
    • Peter Schulz
      Peter Schulz on 2020-07-12 at 10:27 am

      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

      Reply
  11. Evan Tilley
    Evan Tilley on 2020-11-30 at 7:09 am

    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!

    Reply
    • Peter Schulz
      Peter Schulz on 2020-11-30 at 11:22 am

      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

      Reply
  12. Alex Schallhammer
    Alex Schallhammer on 2020-12-04 at 4:32 pm

    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

    Reply
    • Peter Schulz
      Peter Schulz on 2020-12-05 at 9:26 am

      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

      Reply
  13. Joseph
    Joseph on 2020-12-07 at 6:02 pm

    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

    Reply
    • Joseph
      Joseph on 2020-12-07 at 7:21 pm

      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

      Reply
      • Peter Schulz
        Peter Schulz on 2020-12-07 at 7:45 pm

        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

        Reply
        • Joseph
          Joseph on 2020-12-07 at 8:30 pm

          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 Data Tables 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

          Reply
  14. Peter Schulz
    Peter Schulz on 2020-12-07 at 8:43 pm

    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/data-apps-advanced-features/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

    Reply
    • Joseph
      Joseph on 2020-12-07 at 8:57 pm

      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 Data Tables table I created.
      I’ll follow the instructions from the link you sent and see if I can achieve this task successfully.
      Thanks,
      Joseph

      Reply
      • Peter Schulz
        Peter Schulz on 2020-12-07 at 9:08 pm

        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

        Reply
        • Joseph
          Joseph on 2020-12-08 at 1:40 pm

          Hi Peter,
          Yes you’re correct, we allowing search by CCN or PO, I tested Data Tables 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 Data Tables 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 Tables if the variable is ccn or po.
          Appreciate if you can help on this
          Thanks,
          Joseph

          Reply
          • Peter Schulz
            Peter Schulz on 2020-12-08 at 2:13 pm

            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

            Reply
  15. Joseph
    Joseph on 2020-12-08 at 2:56 pm

    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

    Reply
    • Joseph
      Joseph on 2020-12-08 at 3:02 pm

      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

      Reply
  16. Jan
    Jan on 2021-05-03 at 8:54 pm

    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

    Reply
    • Peter Schulz
      Peter Schulz on 2021-05-04 at 7:38 am

      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

      Reply

Submit a Comment Cancel reply

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

Table of Contents
  • Data Table shortcodes
    • Copy/paste shortcode from a publication
    • Examples
    • Inline demo without styling
  • Filter examples
  • Styling

Links

Blogs
Tutorials
Demos

Socials

LinkedIn
Twitter

Get in touch

Premium support
Free support forum
Contact us

Resources

WordPress plugin directory
YouTube tutorials

Passionate Programmers B.V. © 2017 - 2023 All rights reserved