Hello. I am using VAR1 in my SQL query to filter as part of an IN clause, example:
SELECT COL1 FROM TABLE1 WHERE COL1 IN ('%VAR1%')
I am setting VAR1 in php and is a comma separated list of single quoted strings, example:
'myval1','myval2','myval3'
I cannot get my SQL to read these values correctly, and I believe it is because of the single quotes wrapping the value. If I use a default value of myval for this variable, without the single quotes, the query executes fine. If I use 'myval' or 'myval1','myval2','myval3', no results are returned.
My question is, how do I need to format this VAR1 value ('myval1','myval2','myval3') for this to work?
Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.
I am not very experienced with that specific operator, but researched about it and I realise the IN operator is defined as :
The IN operator allows us to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
So, I believe the best way to use this with our variable Placeholders is to use multiple individual placeholders, for example like this :
SELECT * FROM TableName
WHERE ColumnName IN ('%VAR1%', '%VAR2%', '%VAR3%');
Then set some default VAR1, VAR2 and VAR3 values on your Back-End table settings in the Placeholder to generate the back-end/default version of the table.
And on front-end, of course you can input different value for each VAR in the Shortcode for different Page needs, like :
I don’t think using multiple variables is the best approach, the number of values in my list will be dynamic (sometimes it will be two, sometimes more). I need to be able to use a single variable, and just get the string formatting of this value correct. Like I mentioned in the ticket, I have the query working correctly when I hard code the value list, but not when I pass it as a variable, so it seems like I just need to know what format this string of comma repeated values needs to be to work when passed as a variable.
Thank you for this clarification, I see what you mean.
We will have to ask our QA Team and the Developers if they can come up with a suitable workaround for your use-case.
Could you please allow us remote access and show us a bit more details of an example, how these values will usually be saved in the cell of your source SQL data, how they will look in our Table when you just call an SQL Query without the filtering first, then you can give us a couple of example cells to focus on, then we will do our best to see how these types of values could be used in that scenario.
For the Access, can you send me the URL of the Page with the Table shortcode;
And for back-end access please send me Administrator User credentials ( or make a new Admin for my email [email protected];
If you can also send us FTP credentials and access to the Database ( URL with credentials for PHPMyAdmin or Hosting Panel access)?
Then we can go in and inspect how you setup everything.
So, if you can add screenshots, or even better, record a Video of your screen to show us all the details, how the source data looks coming from the cells of the SQL Database versus how it looks in our Table;
And also, how are you trying to pass these comma separated values in the VAR1 Placeholder on the Table's back-end?
Did you manage to generate a default version of the SQL Table on the back-end with a simpler placeholder VAR1 value, then the SQL Parser we use struggles to generate it when you pass different comma separated values via the Shortcode, etc?
It is important for us to confirm all these details and access your table and Page with the shortcode remotely, it is the quickest way to directly work on your data set rather than trying to create a random data set which might work - but maybe it does not work only with specific data, and so on...
You also mentioned that you are setting VAR1 in PHP as
a comma separated list of single quoted strings, as example like : 'myval1','myval2','myval3'.
If you can please show us those details as well, how are you setting those up exactly via PHP, and how they should be called via the SQL Query table, etc?
If you send any sensitive data in a Video, screenshots or Admin access, please ensure it is a Private message.
If a Video gets too large, you can upload it to weTransfer and send me a download link.
I have got to say, what you’re asking me to do here is a lot. you can easily test this with any database, using any varchar column type. I’m sure you have a few DBs you use to develop and test against. Just do a select query on a column and include “where col1 in (‘%VAR1%’) and pass a few different values to this variable. That’s all I’m doing.
Here is what works and what does not. When I say works, I mean the SQL query is able to execute and find results. The records I'm selecting has both PHSBand and HuronValley values in the selected column.
At this time, there's no way to pass multiple values in a single placeholder. Currently, the only way around this is to use Milos's advice:
SELECT * FROM TableName
WHERE ColumnName IN ('%VAR1%', '%VAR2%', '%VAR3%');
Our team will consider this for future development. As our developers prioritize features based on both our company plans and customer suggestions, we encourage you to add this as a feature suggestion on this page. The more votes a feature request receives, the more likely it will be developed quickly.
Hello. I am using VAR1 in my SQL query to filter as part of an IN clause, example:
SELECT COL1 FROM TABLE1 WHERE COL1 IN ('%VAR1%')
I am setting VAR1 in php and is a comma separated list of single quoted strings, example:
'myval1','myval2','myval3'
I cannot get my SQL to read these values correctly, and I believe it is because of the single quotes wrapping the value. If I use a default value of myval for this variable, without the single quotes, the query executes fine. If I use 'myval' or 'myval1','myval2','myval3', no results are returned.
My question is, how do I need to format this VAR1 value ('myval1','myval2','myval3') for this to work?
Hi Jereme,
Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.
I am not very experienced with that specific operator, but researched about it and I realise the IN operator is defined as :
The IN operator allows us to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
So, I believe the best way to use this with our variable Placeholders is to use multiple individual placeholders, for example like this :
Then set some default VAR1, VAR2 and VAR3 values on your Back-End table settings in the Placeholder to generate the back-end/default version of the table.
And on front-end, of course you can input different value for each VAR in the Shortcode for different Page needs, like :
[ wpdatatable id=1 var1='Value 1' var2='value 2' var2='value 3']
Let me know if that can be a suitable workaround for your use case.
Thanks.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
I don’t think using multiple variables is the best approach, the number of values in my list will be dynamic (sometimes it will be two, sometimes more). I need to be able to use a single variable, and just get the string formatting of this value correct. Like I mentioned in the ticket, I have the query working correctly when I hard code the value list, but not when I pass it as a variable, so it seems like I just need to know what format this string of comma repeated values needs to be to work when passed as a variable.
Hi Jereme,
Thank you for this clarification, I see what you mean.
We will have to ask our QA Team and the Developers if they can come up with a suitable workaround for your use-case.
Could you please allow us remote access and show us a bit more details of an example, how these values will usually be saved in the cell of your source SQL data, how they will look in our Table when you just call an SQL Query without the filtering first, then you can give us a couple of example cells to focus on, then we will do our best to see how these types of values could be used in that scenario.
For the Access, can you send me the URL of the Page with the Table shortcode;
And for back-end access please send me Administrator User credentials ( or make a new Admin for my email [email protected];
If you can also send us FTP credentials and access to the Database ( URL with credentials for PHPMyAdmin or Hosting Panel access)?
Then we can go in and inspect how you setup everything.
So, if you can add screenshots, or even better, record a Video of your screen to show us all the details, how the source data looks coming from the cells of the SQL Database versus how it looks in our Table;
And also, how are you trying to pass these comma separated values in the VAR1 Placeholder on the Table's back-end?
Did you manage to generate a default version of the SQL Table on the back-end with a simpler placeholder VAR1 value, then the SQL Parser we use struggles to generate it when you pass different comma separated values via the Shortcode, etc?
It is important for us to confirm all these details and access your table and Page with the shortcode remotely, it is the quickest way to directly work on your data set rather than trying to create a random data set which might work - but maybe it does not work only with specific data, and so on...
You also mentioned that you are setting VAR1 in PHP as
a comma separated list of single quoted strings, as example like : 'myval1','myval2','myval3'.
If you can please show us those details as well, how are you setting those up exactly via PHP, and how they should be called via the SQL Query table, etc?
If you send any sensitive data in a Video, screenshots or Admin access, please ensure it is a Private message.
If a Video gets too large, you can upload it to weTransfer and send me a download link.
Thank you.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
I have got to say, what you’re asking me to do here is a lot. you can easily test this with any database, using any varchar column type. I’m sure you have a few DBs you use to develop and test against. Just do a select query on a column and include “where col1 in (‘%VAR1%’) and pass a few different values to this variable. That’s all I’m doing.
Here is what works and what does not. When I say works, I mean the SQL query is able to execute and find results. The records I'm selecting has both PHSBand and HuronValley values in the selected column.
Attached files: notworking2.png
notworking3.png
notworking.png
working.png
Did you have a chance to try this?
Hello Jereme.
Thank you for your patience.
At this time, there's no way to pass multiple values in a single placeholder. Currently, the only way around this is to use Milos's advice:
Our team will consider this for future development. As our developers prioritize features based on both our company plans and customer suggestions, we encourage you to add this as a feature suggestion on this page. The more votes a feature request receives, the more likely it will be developed quickly.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables