In the wpDataTables plugin setting, I have define a datasource, the sql is like this:
Select * FROM PTable where loginid = '%VAR1%'
In the placeholder, I have hardcode the %VAR% to a '[email protected]' so when I put the shortcode to HTML content, it will by default showing all records if loginid = '[email protected]'. However, now, my page have a javascript such that I want to show records filtering by different email login.
In javascript:
firebase.auth().onAuthStateChanged((user) => { if (user) {// already signed in
var emailStr = user.email; //whenever the page load, I need to pass the value of emailStr to VAR1 , then showing the correct data. } }
If I understand correctly you would like to filter the table based on the currently logged in user?
You can add the placeholder %CURRENT_USER_ID% as the predefined filtering value in column settings of "User ID" column, and the table will automatically be filtered per the ID of the currently logged in user.
For SQL tables you can use the placeholder in the query.
Placeholders are executed only in the plugin or as part of hooks in the wordpress so you will not be able to use them in creating views for example. But you can create a column in the view that will contain a column that has values from the logged-in user and then create a table like this
SELECT * FROM view_name WHERE user_login = %CURRENT_USER_LOGIN%
I hope this helps, do let us know if you need any further assistance.
I did try but got error when generating the data source.
Question 1.
In the wpdatatables, -> data source , i put sql like :
Select * from PTable where user_login = %CURRENT_USER_LOGIN%. The field "user_login" is called "sopId" in the sql database, as I changed the field header in the wpdatatables Table Preview.
In the Placeholders, I put an email in the %CURRENT_USER_LOGIN% field.
I may got mis-understanding the purpose of %CURRENT_USER_LOGIN%. I thought %CURRENT_USER_LOGIN% is referred to the current logged in user who created this data source.
According to your previous reply, I think I'm wrong. The %CURRENT_USER_LOGIN% is referring to the user who have logged-in in my website currently via my Google firebase login tool (JavaScript). Is it what the %CURRENT_USER_LOGIN% mean?
Let me share with you a usecase for this placeholder that might explain things better.
you can insert placeholders in the query as if they are some specific values. For example, you can provide the query like this:
SELECT * FROM my_table WHERE my_field > %VAR1% AND my_field < %VAR2% AND user_id = %CURRENT_USER_ID%
In this example the query means: “show me everything from my_table, where my_field is greater than variable 1, but less then variable 2, and user_id is equal to the currently logged in users’s ID”.
Before you try to save the table, you first need to see if this query returns anything. By default, %VAR1%, %VAR2% and %VAR3% are equal to empty strings, and %CURRENT_USER_ID% is equal to the user that creates the table – typically the Admin user, most probably with ID=1. If you do not redefine the defaults, this query will be parsed like this on MySQL side:
SELECT * FROM my_table WHERE my_field > ” AND my_field < ” AND user_id = 1
Most likely, such a query would not return anything, and wpDataTables would say there’s no data to build the table upon. To avoid this, you will need to expand the placeholder configuration block, and define the default values for the variables: e.g., set %VAR1% equal to 0, and %VAR2% equal to 100. Also, you can redefine the value for %CURRENT_USER_ID%, e.g. if the table does not contain any values related to the admin’s ID, set it to “15“.
The variable values are then saved in the table settings, and they will be always used as default if not overridden by shortcode parameters. Only the %CURRENT_USER_ID% is not saved, and the provided value is used only at the instant of the table generation, after which the actual user ID is always fetched.
The query would then be parsed like this on MySQL side:
SELECT * FROM my_table WHERE my_field > 0 AND my_field < 100 AND user_id = 15
If this query returns any data, it will successfully create a wpDataTable.
Later, you can insert this wpDataTable’s shortcode in your posts or pages, and it will fetch the data for the current user. But you can also override the variable settings. Simply provide the variable values in the shortcode like this:
[ wpdatatable id=12 var1=150 var2=350]
and the values from shortcode attributes would be applied to the variable and the query would be treated like:
SELECT * FROM my_table WHERE my_field > 150 AND my_field < 350 AND user_id = (current_user_id)
Using placeholders like this, you can use a single wpDataTable to produce many different output tables on different pages.
Thx for your detailed reply. Actually, I want a single wpDataTable to produce different output from a single page.
I have a table column of user id which stored email address. Is it possible to override the variable by different email address when a single page is loaded. The email address is getting from javascript.
in this case you can use %CURRENT_USER_EMAIL% instead of ID or user login then you would be able to display different data that will be passed from the query based on the email address of the currently logged in used. I hope I understood better this time and that this will help you out.
Do let me know if you need any further assistance.
In the wpDataTables plugin setting, I have define a datasource, the sql is like this:
Select * FROM PTable where loginid = '%VAR1%'
In the placeholder, I have hardcode the %VAR% to a '[email protected]' so when I put the shortcode to HTML content, it will by default showing all records if loginid = '[email protected]'. However, now, my page have a javascript such that I want to show records filtering by different email login.
In javascript:
firebase.auth().onAuthStateChanged((user) => {
if (user) {// already signed in
var emailStr = user.email;
//whenever the page load, I need to pass the value of emailStr to VAR1 , then showing the correct data.
}
}
In wordpress page:
[wpdatatable id=8 VAR1='[email protected]']
Please let me know any way to do that?
I have searched from web and I expected to call php by ajax as shortcode is server-end program, please correct me if I'm wrong.
If there is any reference for this, please let me know.
Hi Grace
Thank you for reaching out to us.
If I understand correctly you would like to filter the table based on the currently logged in user?
You can add the placeholder %CURRENT_USER_ID% as the predefined filtering value in column settings of "User ID" column, and the table will automatically be filtered per the ID of the currently logged in user.
For SQL tables you can use the placeholder in the query.
Placeholders are executed only in the plugin or as part of hooks in the wordpress so you will not be able to use them in creating views for example. But you can create a column in the view that will contain a column that has values from the logged-in user and then create a table like this
I hope this helps, do let us know if you need any further assistance.
I did try but got error when generating the data source.
Question 1.
In the wpdatatables, -> data source , i put sql like :
Select * from PTable where user_login = %CURRENT_USER_LOGIN%. The field "user_login" is called "sopId" in the sql database, as I changed the field header in the wpdatatables Table Preview.
In the Placeholders, I put an email in the %CURRENT_USER_LOGIN% field.
e.g "[email protected]" that i'm sure there is a testing record.
I got error when click save change:
-- cannot calculate position of aaa000 within [email protected]
Question 2.
I may got mis-understanding the purpose of %CURRENT_USER_LOGIN%. I thought %CURRENT_USER_LOGIN% is referred to the current logged in user who created this data source.
According to your previous reply, I think I'm wrong. The %CURRENT_USER_LOGIN% is referring to the user who have logged-in in my website currently via my Google firebase login tool (JavaScript). Is it what the %CURRENT_USER_LOGIN% mean?
Hi Grace
Let me share with you a usecase for this placeholder that might explain things better.
you can insert placeholders in the query as if they are some specific values. For example, you can provide the query like this:
SELECT * FROM my_table
WHERE my_field > %VAR1%
AND my_field < %VAR2%
AND user_id = %CURRENT_USER_ID%
In this example the query means: “show me everything from my_table, where my_field is greater than variable 1, but less then variable 2, and user_id is equal to the currently logged in users’s ID”.
Before you try to save the table, you first need to see if this query returns anything. By default, %VAR1%, %VAR2% and %VAR3% are equal to empty strings, and %CURRENT_USER_ID% is equal to the user that creates the table – typically the Admin user, most probably with ID=1. If you do not redefine the defaults, this query will be parsed like this on MySQL side:
SELECT * FROM my_table
WHERE my_field > ”
AND my_field < ”
AND user_id = 1
Most likely, such a query would not return anything, and wpDataTables would say there’s no data to build the table upon. To avoid this, you will need to expand the placeholder configuration block, and define the default values for the variables: e.g., set %VAR1% equal to 0, and %VAR2% equal to 100. Also, you can redefine the value for %CURRENT_USER_ID%, e.g. if the table does not contain any values related to the admin’s ID, set it to “15“.
The variable values are then saved in the table settings, and they will be always used as default if not overridden by shortcode parameters. Only the %CURRENT_USER_ID% is not saved, and the provided value is used only at the instant of the table generation, after which the actual user ID is always fetched.
The query would then be parsed like this on MySQL side:
SELECT * FROM my_table
WHERE my_field > 0
AND my_field < 100
AND user_id = 15
If this query returns any data, it will successfully create a wpDataTable.
Later, you can insert this wpDataTable’s shortcode in your posts or pages, and it will fetch the data for the current user. But you can also override the variable settings. Simply provide the variable values in the shortcode like this:
[ wpdatatable id=12 var1=150 var2=350]
and the values from shortcode attributes would be applied to the variable and the query would be treated like:
SELECT * FROM my_table
WHERE my_field > 150
AND my_field < 350
AND user_id = (current_user_id)
Using placeholders like this, you can use a single wpDataTable to produce many different output tables on different pages.
Hi Blazenka,
Thx for your detailed reply. Actually, I want a single wpDataTable to produce different output from a single page.
I have a table column of user id which stored email address. Is it possible to override the variable by different email address when a single page is loaded. The email address is getting from javascript.
Hi Grace
We have number of placeholders you can use in table generation depending on what information you want to display.
https://wpdatatables.com/documentation/table-features/using-placeholders/
in this case you can use %CURRENT_USER_EMAIL% instead of ID or user login then you would be able to display different data that will be passed from the query based on the email address of the currently logged in used. I hope I understood better this time and that this will help you out.
Do let me know if you need any further assistance.