vasut
Forum Replies Created
-
Sounds good, Peter.
And that’s a good idea on using publications to hide all the parameters.I was also thinking, maybe, if Views type of table can be dynamically created in the background and referred to as a prepared report when user selects a report.
But, then the pub_id is static. Would that work?I’m still not clear how operators like OR, IN, and LIKE would be used in Filter params?
I’ll send you the message with private email through the plugin contactForm.
Thank you!
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,
VasutHi 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,
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]- This reply was modified 4 years, 8 months ago by vasut.
Hi 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,
VasutFurther more, I tested by removing part of the code (the dependencies – use …) and refreshed the page. Sure enough, the page shows error of missing components. So, I put it back and the error was gone.
This means the effect from edit to page is immediate.The fact that it’s still not working, could it mean that some dependency components also need to be updated with their new versions?
Thanks,
VasutI 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 Peter,
Thank you for the quick clarification.
I’m using WP Data Access inside HostGator, which is only accessible through the GUI provided.
I went to Edit Plugin and selected WP Data Access, and went into the directory structure
wp-data-access\WPDataAccess\Data_Tables
I don’t see any button to upload the new file over the old file.
Should I do Select-All on the new file content and replace the old content with the new content and save the file back, just like that?– 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.
Anther superp video tutorial!
Yep, I got the images to display on the page in the rows now.
Thank you, Peter.Thanks to your guidance and video, I can publish data from a table on the page now.
If you don’t mind I’d like to ask one more question here:
How do you enter an image to the table’s blob/binary field and show it as image on the page?
I saw your example showing nice motorcycle images.
I have a User table that I’d like to show a thumbnail image of the users.Thanks,
VasutHi Peter,
Problem is solved. You are Correct!
I did the Table Design, but Not the Table Create.
Also, I went along with the (default) ‘Advanced Design Mode’, Not the ‘Basic Design Mode’.
After correcting the mistake, I can now see my table ‘USER’ in the list. ??
I’m going to flag this topic as Resolved.
I’m pretty sure I’ll run into more challenges, in which case I’ll search through existing topics and if I can’t find similar issue I’ll open a new topic.Thank you, Peter.
Next time, if any beginner comes to you with this problem, you can go straight to your most recent question: ‘Did you Create the table?’ Haha.
By the way, I really like your training videos. Good pace. Good explanations, easy to understand and follow, and you have an outstandingly nice voice and accent.Best Regards,
VasutHi Peter,
Your question: Which option is enabled?
> I tried all three options, but none showed any of my tables in the list.I’ve captured screenshots, but don’t see a way to attach a file here or on the Contactform page at the plugin site. Both seem to accept only text message.
Here, this message field seems to support HTML. But that probably does not work with local file referencing.But I’m still confused. If tables are stored in WP schema, shouldn’t WP recognize them and list them in the ‘Table Name’ drop-down?
Do you have a link to video clip showing the steps when you created the DB and tables from the start? I’d like to check if I did anything wrong, or any misstep.
Thank you,
VasutPeter,
In addition to my previous two reponses, and I’m not sure if this is relevant, but I have both WP Data Access and Participants Database plugins installed, to try both out.
Not being able to create table with Participants Database, I decided to focus on WP Data Access, and was thinking about uninstalling Participants Database plugin. I’m not sure if having both plugins would confuse WP. It’s unlikely, as all my tables, so far, have been created witin WP Data Access. But, I don’t want to rule anything out of the suspect list.Thanks for staying with me and helping me on this. I really appreciate it.
Vasut
When I designed New Table, I used the defaults – Engine: MyISAM, Collation: utf8_unicode_ci. Are these correct options?