Filtered - Scrollable, Seachable, Sortable table of entries via direct SQL query

 

Goal: Create a * filtered * scrollable, searchable and sortable view of FP entries via a direct query to the FP tables.

 

The filter is via an FP form with which the user selects the variables that the entries list will be filtered on.

The variables are passed to the query via the HTTP page address string.

 

The FP form being used as the filter is attached:  fp-filter-for-datatables.jpg

 

The web page address parameters used to pass the filter selections to the query are attached: fp-filter-for-datatables-address-parameters.JPG

 

Tip: when you create the filter form, under the "Settings" tab, for "Action After Form Submission" select "Redirect to URL".

In the URL address, include the address of the page showing the filtered results and include the filter keys as the parameters.

Sample:

mysite.com/path-showing-filtered-results/?country=[227 show=id]&state=[228 show=id]

 

A sample of what the goal looks like is attached: datatables-filtered-fl-stores.jpg

In that example, there is a highlighted row under the cursor, the search box is live, meaning the results are filtered in real time as you type into the search box and every column is sortable by clicking on the column name.

 

This solution leverages other FP User Tips & Tricks:

* How to create Scrollable, Seachable, Sortable table of entries via direct SQL query

Scrollable, Seachable, Sortable table of entries via direct SQL query

 

* How to use DataTables to display FP entries

Example: DataTables - display a table of a form's entries

 

* How to create a denormalized view of FP entries via direct SQL query of FP tables

How to create a denormalized view of form entries

 

Disclaimer:

I am, most emphatically, *not* a developer, so this code is probably not efficient, well formed, properly structured or any of the other things that someone who actually knew what they were doing would do. I can barely spell PHP, much less create something with it.

People who actually know how to develop with PHP will find lots of opportunities for improvement here.

For instance, I could not get the passing of the query from the shortcode to work due to using "" in the query string, so I hard-coded the query into the PHP file.

This solution would probably be better implemented as a plugin rather than a shortcode, but, again, that is well beyond my abilities.

 

Credits:

This solution is based heavily on:

http://digwp.com/2010/01/custom-query-shortcode/

as well as innumerable searches and lots of reading of the PHP documentation and Stack Overflow entries.

 

Overview:

This solution uses a shortcode to format a DataTables display of FP entries.

The shortcode PHP code lives in the theme's functions.php file.

 

Code is attached: FP-Filtered-DataTables-direct-SQL-query-shortcode-function.php

 

To use:

1. Add the code to your WP theme's functions.php file.

2. Add this shortcode to a page: [localstores the_query="you must hard code the query into the functions php file"]

 

Note that you will probably want to change the name of your shortocode to something other than "localstores."

To do so, you need to edit the following lines of code:

11: function localstores_query_shortcode($atts)

639: add_shortcode("localstores", "localstores_query_shortcode");

 

 

Bonus:

You can combine the DataTable with a map to display both the scrollable, searchable, sortable table and a corresponding map of the locations of those * filtered * entries - all on the same page.

See attached for a sample: datatables-filtered-fl-stores-with-map.jpg

The map uses this User Tip and Trick: https://formidableforms.com/help-desk/example-comprehensive-google-map-plugin-map-showing-location-of-all-form-entries/