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 ]
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 #
| Firstname | Lastname | Phone No | Address | Zipcode | City | County | Country | |
|---|---|---|---|---|---|---|---|---|
| Firstname | Lastname | Phone No | Address | Zipcode | City | County | Country |
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.
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']
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
Aren’t you missing a quote at the end?
[wpdataaccess table="qEvents" responsive_type="Collapsed" sql_where="State='WA']
sql_where=”State=’WA'”
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.
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
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
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!
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
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
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
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
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
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!
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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