There are two ways to add a static filter to a data table:
- Default where clause
- Shortcode parameters
1. Default where clause #
The default where clause supports conditions, subqueries and other more complex filters as long as it contains valid SQL.
Using plugin variables in your default where clause #
The WordPress user ID and some pre-defined plugin environment variables are available in the default where clause. The following example demonstrates how the WordPress user ID can be used to limit query output. Read more…
Using URL parameters in your default where clause #
Premium users can also use URL parameters in their default where clauses. The following notations allow user to access these values:
httpGet[‘param_name’] | Return the value of parameter param_name for an HTTP GET request |
httpPost[‘param_name’] | Return the value of parameter param_name for an HTTP POST request |
httpRequest[‘param_name’] | Return the value of parameter param_name for an HTTP GET and POST requests |
Notes #
- Null is returned when no argument with the given name is found
- Values are sanitized and prepared automatically
Examples #
Show only rows for a specific student
student_id = httpPost['student_id'] and httpPost['student_id'] is not null
Add a like condition using if
first_name like if( httpGet['student_firstname'] is null, first_name, concat( '%', httpGet['student_firstname'], '%' ) )
Show only rows for a specific student when parameter student_id is present or all rows if parameter student_id is not present
student_id = ifnull( httpPost['student_id'], student_id )
2. Shortcode parameters #
- filter_field_name (field name(s) to be filtered)
- filter_field_value (field value(s) to filter, performs a LIKE and therefor allows wildcards)
Example of a filter for one specific column
[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.
Example of a multiple column filter
[wpdataaccess pub_id="6" filter_field_name="job,ename" filter_field_value="president,ford"]
The filter allows to use % as a wildcard as shown in the single column filter example. These filter parameters do not support operators like IN, OR, NOT, >, < and so on. Please use the default where clause to use these operators and more complex filters.
Hi, is it possible to set the order by attribute in the admin page with a column that is not selected in column names? For example sort by date_time column but I don’t choose this column in column names because I don’t want to how this column in the result.
Yes Michal, you can do that, although it might be a bit confusing fo a user to get a sorted list without seeing the sorting column. Please keep in mind that a user can change the order by clicking on the header of another column.
Hi, I am still evaluating your plugin and I might already be pushing it to the limit. But is there a way to filter the content with the help of user meta? I tried WHERE Clause: FIELD_NAME = ‘get_the_author_meta( ‘FIELD_NAME’, $user->ID )’ with zero result.
Hi Clas, You can use FIELD_NAME=$$USERID$$ to compare with the user id or FIELD_NAME=$$USER$$ to compare with the user name. You cannot use PHP functions in a where. Hope this helps, Peter
I tested this
https://ottawa.ogs.on.ca/researching/indexes/library-catalog/?wpda_search_column_Title=Wesleyan
and get nothing found but if I go to
https://ottawa.ogs.on.ca/researching/indexes/library-catalog
and put Wesleyan in the column filter I get expected results.
what am I doing wrong?
Hi Charles,
Please change: ?wpda_search_column_Title to ?wpda_search_column_title. It works than.
Good luck,
Peter
Hi Peter,
I probably once again need your help and expertise to figure this one out, I have option to search for the production table based on Cargo Control Number (unique field) or by PO (reference number) which may vary, the whole PO number might have additional characters to the left or right of it, but we need to see all occurrences that contain the searchable PO term.
We use the Gravity Form on https://www.rutherfordglobal.com/pars-search/ where the user has to choose the search by, then Gravity form notification is set to wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4} where CCN:2 is the second field ID of the term and PO:4 is the form field ID 4 and the final results are displayed on https://www.rutherfordglobal.com/pars-search-results/
The search results page has the shortcode [wpdataaccess pub_id="2"]
All works well as long as the user enters a CCN number or a full exact PO number, but as soon as the PO number is any shorter form it will not find the record(s)
As I understand I need a WHERE clause similar to what I have on the old website where I use php diect select statement such as:
==============================================================
// Set SQL string based on selected search option
if ($search == “CCN”)
$sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where ccn = ‘”.$parsNo.”‘”;
else if ($search == “REF”)
$sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where po like ‘%”.$parsNo.”%'”;
===============================================================
I tried to put some filter in the Where clause under Data Tables using po like ‘%wpda_search_column_po%’, tried many variations of this, even tried to put in the actual PO number I’m searching po like ‘%2588594%’ and never found, but there are many records in the PO table having in them this number
Sorry for the long description, I just wanted to explain in detail what I’m looking to achieve:
1). Allow the search for PO only to be any part of the actual PO number stored in the database and display all results
2). If that is possible how do I make sure that it will not affect the search for CCN number which is always exact
Hope you can help on this as you did many times in the past.
Hi Joseph,
The plugin allows you to use wildcard with URL arguments. You just need to add them yourself. This example:%
wpda_search_column_po=%
adds a wildcard in front and after your search value.
Not sure if this works with Gravity Forms, but it could look like this:
wpda_search_column_po=%{PO:4}%
Can your please test if this works?
Thanks,
Peter
Hi Peter,
Excellent idea and thank you very much for your reply.
I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:
https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af
Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:
https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d
But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#
Thanks,
Joseph
Hi Peter,
Excellent idea and thank you very much for your reply.
I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:
https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af
Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:
https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d
But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#
Thanks,
Joseph
Hi Joseph,
Great to read it works for CCN #. What happens if you remove the wildcards for PO #?
Thanks,
Peter
Hi Peter,
If I remove the wildcards from PO it continues to work for CCN, but PO needs to be exact match to show, what I need is a way to allow still for CCN search and in the same time make the PO search show all available results that contain the PO entered
Hope that’s possible, if not I thought to create 2 separate searches, one for the exact CCN wpda_search_column_ccn={CCN:2} and a second one for wildcard PO wpda_search_column_po=%{PO:4}% each displaying its own results, but I don’t know if I can create 2 separate searches in Data Tables
Thanks,
Joseph
Hi Joseph,
I see there already is some javascript in your form submit. Can you change it, so that:
– Only the CCN # is send WITH wildcards when CCN # is entered
– Only the exact PO # is send when PO # is entered
Would this solve the problem?
Thanks,
Peter
Hi Peter,
How would I define a WHERE clause when I have 2 options for searching: option by CCN# (which is always the same length) and option by REF# where I need to display all records that have the REF# entered as part of the record?
I tried adding in the WHERE clause: po like ‘%wpda_search_column_po%’ which did not work and which also prevented getting the results for any search by ccn number which are working without needed a where clause
Thanks,
Joseph
Hi Peter,
Any chance you can help on the where clause please
Thanks,
Joseph
Hi Peter,
sorry was away for awhile, my search results page uses:
wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4}
If I use the %{CCN:2}% and no % on the PO I get no results for any
If I use {CCN:2} and %{PO:4}% i get CCN result and all the database for PO results
If I use {CCN:2} and %{PO:4} I get CCN result and all the database as result for PO
Same would happen if I use {PO:4}% I get CCN result and no result for PO
I’m not using any JS in the form, it’s a Gravity Form that allows only entering the search term for either CCN or PO, then it takes the entered term and adds it to the wpda shortcode
For testing, if I use 3959752984492 for CCN# I get correct results, if I use 858859 for REF# (PO) I get no results, but there are 12 records that have 858859 in their full PO number
Thanks,
Joseph
Hi Joseph,
Are you sure about your numbers? If I search for PO=106306 it works!
I noticed that a search on column PO is much slower than a search on column CCN. Is it possible to create an idex on column PO?
Thanks,
Peter
Hi Peter,
Tried the PO number 106306 it does display 2 records, but looking in the actual database I can see 61 records that have the number 106306 included, it’s true that there are these 2 records that have identical PO, but all the other ones have the same number in their PO column, like SEGU9106306 or GM605321937010630666 and they want to see all the occurrences of the PO column that has the number entered anywhere in the record (noticed that the entered number is never at the front of the string)
I noticed too that the search for PO is much slower than for CCN, how would I go and create an index to speed up the search for PO?
Thanks,
Joseph
Hi Joseph,
So you want to do a wildcard search? In that case you need to add % characters to both sides of your argument like: PO=%106306%
But that might return too many rows. Can you check that? It also makes no sense to add an index on that column if you use a wildcard. Is there maybe a way to narrow a search on PO?
Thanks,
Peter
Hi Peter,
Here is my search:
wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}%
doing a live search at https://www.rutherfordglobal.com/pars-search/ for CCN 17K8PARS016310 display the correct amount of records from the database, but if I do a PO search for 858859 it gives me the whole database while there are only 12 records%858859% with this pattern
Using the same search with %PO% for PO # 106306 doesn’t show any records while in the database there 78 records with this pattern
I cleared the cache and used 2 separate browsers, same result
Thanks,
Joseph
Hi Joseph,
Can you send me your table structure so I can have a closer look at this issue? Please follow these steps:
> Data Explorer > Select table > Manage > SQL > Copy to clipboard
You can use the contactform to send it in private.
Thanks,
Peter
Hi Peter,
Sent Explorer/Table/SQL clipboard data through the contact form @ https://wpdataaccess.com/contact/
Thanks,
Joseph
Is it possible, as part of a publication, to create a filter for users to apply – in particular for a date range?
For example in my weather records table, a user might want to see the data from 1 January-31 January 2005.
And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?
Thanks as always.
Hi Mike,
A date range filter is not available at this time. Sorry!
I did a little research. I’ll add a custom search form to the next release, which allows you to make data range selections. But it only works client side! WP Data Access works server side by default. With server side processing the plugin loads the data shown on the actual page only, which usually makes a publication faster. Especially if you have a lot of data. Client side processing can have advantages to of course. If users do a lot of searching, ordering, etc on your publication data, loading all the data on page load might speed up local (client side) actions.
You can change your publication to work client side, forcing the plugin to load all the data at once and see if that works for you. Although you have over 4400 rows in your publication, the row content is not taking up a lot of space. Might be worth to give it a try. To force WP Data Access to use client side processing, just add this to column table options advanced of your publication:
{ "serverSide": true }
>>> And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?
I will add support for row grouping which allows you to do that to the next release. That was on my to do list anyway… 🙂
Best regards,
Peter
How do i create a query in WP Data access for only top 3
Hi Danie,
You can use the pageLength option in table options advanced. An example can be found here:
https://wpdataaccess.com/docs/data-tables-advanced-features/advanced-settings/
Hope this helps,
Peter
Hi Peter:
I’m trying to create an url filter to get some kind of “master-detail” report. I think I have achieved how to do the hyperlink but the “child” table seems to don’t care about arguments.
This is the example: https://cnchiboi.com/vd-contacto/
What am I doing wrong?
Thank you.
Your master detail report looks great! 🙂 You just need to change your URL argument
from: ?idPeticion=4
to: ?wpda_search_column_idPeticion=4
The plugin needs the wpda_search_column_ prefix to start searching.
Good work,
Peter
Hi Peter:
Yes! It works even hiding id colum to final user. That’s exactly the idea: The user doesn’t need to know how it works.
Thank you for the final help!!
Regads
I’ve just given five stars to your amazing plugin. The most powerfull I have reached for WordPress all over the world.
Regards from Spain
Hi, is it possible to only show data in the table for those rows that match an input parameter defined in an advanced custom field for the specific post?
i.e.:
– i have post with the advanced custom field “symbol” which holds the ticker symbol “AAPL” for a specific post.
– i have my database table with the latest share prices for all symbols in the database, also “AAPL”
– i then want to publish that database table in my post and use the advanced custom field as the input parameter, so that it only shows the share price for “AAPL”.
This would allow me to create a template custom post type with the table embedded automatically. Then, depending on the symbol defined per each post, the table would return the right row with the share price.
Hi Tim,
If you can access the value of your custom field via GET or POST, you can try the URL parameters in your Default WHERE clause. Kindly refer to the example notations above or here.