Shortcode sql_where and filter for WP Data Access
-
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? I saw that notification some where.Also, this code returns empty list:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value=”Cybernetic-Scientist”]And this returns all rows:
[wpdataaccess pub_id=”1″]I believe I followed the sample correctly and the field_value does exist more than one row.
There’s no upgrade required to the plugin for filter to work, right?Best Regards,
Vasut
-
Also, do you have a tutorial site that shows complete mapping between SQL commands and Filter use-cases in Shortcodes?
The examples you provided on this page is nice, but it would be very helpful if you have complete mapping.
https://wpdataaccess.com/docs/documentation/shortcodes/shortcode-wpdataaccess/Can we do something like this? It returns empty list, but the row does exist.
[wpdadiehard table_name=”USER” filter_field_name=”USER_ROLE,USER_EMAIL” filter_field_value=”Road Warrior,[email protected]”]Thanks.
I backed up the old content to my local drive, and replaced it with the new content.
I deactivated the plugin and re-activated it.
Still not working.
Is there a left-over cache anywhere I need to refresh?Thanks.
Hi Vasut,
Can you use the plugin editor and change line 362 of file wp-data-access\WPDataAccess\Data_Tables\WPDA_Data_Tables.php from:
"{$filter_field_name_array[ $i ]}like %s ", [ $filter_field_value_array[ $i ] ]
to:
" where{$filter_field_name_array[ $i ]}like %s ", [ $filter_field_value_array[ $i ] ]Thanks,
PeterHi Peter,
Now, it works, but for a single filter.
I’m testing a few use cases with multiple filtering and I have questions.
This is my table, named USER:USER_ID USER_ROLE USER_PHONE1 USER_EMAIL Data Soong Cybernetic-Scientist 313.867.5309 [email protected] Noonian Soong Cybernetic Scientist [email protected] Mad Max Road Warrior 123-456-7890 [email protected]Use Case 1:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value=”Cybernetic Scientist”]
Returns: Noonian Soong
This is correct.Use Case 2:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value=”Cybernetic Scientist,Road Warrior”]
Returns: All rows
Maybe my filter params are incorrect?Use Case 3:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE,USER_PHONE1″ filter_field_value=”Cybernetic-Scientist,123-456-7890″]
Returns: No matching records found
This result doesn’t look right. I followed this example:
[wpdataaccess pub_id=”6″ filter_field_name=”job,ename” filter_field_value=”president,ford”]Do you have a tutorial/reference page summarizing the AND, OR, NOT, IN and all other SQL operations and examples how to use them in Filters?
The concept of filter is new to us, and we need extensive comparison/use cases to get us up to speed in transitioning from the SQL Where clause to Filter parameters.Another question:
How are other sites already in production impacted by the transition from sql_where to Filters, requiring a major change in Shortcode? They’d normally have trmendous amount of data and pages. The fact that my POC using sql_where suddenly stopped working suggests that the change is not backward compatible with the existing sql_where in the Shortcode.Thanks,
VasutThank you, Peter. Your dedication to help all of us is already greatly appreciated.
It’s very good to work with a professional like you. Kudos!The explanation you’ve detailed clarifies why the example results in ‘No matching records found’. This is because it’s an AND operation. Sorry, my bad. I assumed ‘,’ was an OR operation, and it didn’t occur to me. ??
So, how do we build the filters for OR operator?
I tried to guess the syntax and found interesting results.[wpdataaccess pub_id="1" filter_field_name="USER_ROLE" filter_field_value="Cybernetic Scientist" filter_field_name="USER_PHONE1" filter_field_value="313.867.5309"]
Result: One rowUSER_ID USER_ROLE USER_PHONE1 USER_EMAIL Data Soong Cybernetic-Scientist 313.867.5309 [email protected]When I re-ordered the pairs and put USER_PHONE1 pair first:
[wpdataaccess pub_id="1" filter_field_name="USER_PHONE1" filter_field_value="313.867.5309" filter_field_name="USER_ROLE"?filter_field_value="Cybernetic Scientist"]
Result: Two rowsUSER_ID USER_ROLE USER_PHONE1 USER_EMAIL Data Soong Cybernetic-Scientist 313.867.5309 [email protected] Noonian Soong Cybernetic Scientist [email protected]Hi Peter,
The project we’re working on is an inverntory system. It’ll be heavy on its ability to produce reports on the inventory of the items in different aspects that the client wants.
So, all the SQL WHERE operators would be in use to leverage the search into the database.This means AND, OR, their combinations, nested clauses, IN, LIKE, all of that would be used.
Currently, you have comma (‘,’) as AND. But, this takes away the character ‘,’ that may appear in the content. For example, in the database, if I capture multiple roles like ‘CEO, CTO’, I may have to wrap it under a single quote to segregate it from the AND operator.
How would we do an OR operator? Instead of ‘,’ what separator can we use for OR?
What about the IN and LIKE operators?Proposal
-
Would it work if something like this is interpretable:
Allowing the ‘OR’ and ‘AND’ operator between any two conditional name-value pairs.
While this resembles WHERE clause, it’ll enhance the flexibility in the clause by segregating the operator from within the name-value parameters themselves.Example:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_PHONE1″ filter_field_value=”313.867.5309″ OR filter_field_name=”USER_ROLE” filter_field_value=”Cybernetic Scientist”]This could be for the IN operator:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value IN “CEO,CTO”]This could be for the LIKE operator:
[wpdataaccess pub_id=”1″ filter_field_name=”USER_ROLE” filter_field_value LIKE “Cybernetic%”]What do you think?
Thanks,
VasutHi Peter,
Time frame is not very tight. Client understands that WordPress and plugins, Shortcode and PHP, these goodies are all new to us.
We’re at the state of working on a demo-able POC for them. While we have time to wiggle, it should be reasonable. We should not take like a year long, just to show them something appealing, or they might go to someone else. ??For POC, we’re planning to allow a user to select a report from a list of static links, e.g.:
Reports
1. Team Members (Name, Phone, Email, Availability, …)
2. Items report (Item Desc, car Make-Model, Qty Ordered, Qty Used, Qty Damaged, Qty In-Stock)
3. …Since Shortcode is embedded in the PHP page, I guess it’s vulnerable to SQL Injection. But, I’m still not clear about that. Filter params would also expose column names as would sql_where clause. My knowledge in that topic is quite limited.
Some examples of usage could be to search for a section of members like these:
1.
[wpdataaccess table=”USER” sql_where=”USER_ROLE IN (‘Cybernetic Scientist’,’CTO’)2.
[wpdataaccess table=”USER” sql_where=”USER_ROLE=’Cybernetic Scientist’ OR USER_ROLE LIKE (‘%CTO%’) OR USER_PHONE1 LIKE ‘%daystrom-institute%'”]How would these two clauses be translated into Filter parameterized Shortcodes? If they can, then we would’ve jumped over a major hurdle in our challenges.
Thanks,
Vasut
- The topic ‘Shortcode sql_where and filter for WP Data Access’ is closed to new replies.
