I'm using the SQL below to pull data from the database, including the 'membership_expiry_date' (aka 'Subs Expiry'). This is in a LONGTEXT format in the DB, but I'm using the SQL to convert it to a DATE, so I can use conditional formatting.
I believe the SQL is correct, but I am not able to get the same conditional formatting as when I pull a DATETIME field from the DB. i.e. the 'less than' and 'greater than' operators are not available for the 'Subs Expiry' column like they are with 'User Registered' column.
Are you able to offer any assistance?
Thanks,
Jim
SELECT u1.`ID` AS `User ID`,
u1.`user_login` AS `Website_ID`,
u1.`user_registered` AS `User_Registered`,
m1.meta_value AS `Last_Name`,
m2.meta_value AS `First_Name`,
STR_TO_DATE(m3.meta_value, '%Y/%m/%d') as `Subs_Expire`,
FROM dbName_users u1 LEFT JOIN
dbName_usermeta m1 ON m1.`user_id` = u1.`ID` AND
m1.`meta_key` = 'last_name' LEFT JOIN
dbName_usermeta m2 ON m2.`user_id` = u1.`ID` AND
m2.`meta_key` = 'first_name' LEFT JOIN
dbName_usermeta m3 ON m3.`user_id` = u1.`ID` AND
m3.`meta_key` = 'membership_expiry_date';
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
Please let me know if creating a VIEW helped with the issue.
Thank you for your assistance. I am not familiar with using views, any chance you can start me off please? What do I need to put in this view? Where do I write it? How do I do that in wpDataTables?
Sorry for the late response. Wednesday 1st and Thursday 2nd of May were non-working days in our country.
It's fairly simple. You need to open the PHPMyAdmin and open the SQL tab. Then create a view like this:
CREATE VIEW `view1` AS SELECT
u1.`ID` AS `User ID`,
u1.`user_login` AS `Website_ID`,
u1.`user_registered` AS `User_Registered`,
m1.meta_value AS `Last_Name`,
m2.meta_value AS `First_Name`,
STR_TO_DATE(m3.meta_value, '%Y/%m/%d') as `Subs_Expire`,
FROM dbName_users u1 LEFT JOIN
dbName_usermeta m1 ON m1.`user_id` = u1.`ID` AND
m1.`meta_key` = 'last_name' LEFT JOIN
dbName_usermeta m2 ON m2.`user_id` = u1.`ID` AND
m2.`meta_key` = 'first_name' LEFT JOIN
dbName_usermeta m3 ON m3.`user_id` = u1.`ID` AND
m3.`meta_key` = 'membership_expiry_date';
Then, you will have the view `view1` created in PHPMyAdmin, and you can create a wpDataTable by querying that view like:
I managed to get the view working. I now only use the code:
SELECT * FROM view1;
in wpDataTables and it does what it's supposed to - except I still can't use the conditional formatting as I would expect to, i.e. the 'less than' and 'greater than' operators are not available for the 'Subs_Expire' column.
Please help me understand what you're trying to do with the "Subs_Expire" column? If it works in PHPMyAdmin, it should work the same in our plugin. That's why I'd like to know what your idea with it is, so I can offer another solution.
Also, please make sure to show me what some entries look like in that column.
I'm trying to get the 'membership_expiry_date' out of the database as the column 'Subs_Expire'.
I then want to be able to use the conditional formatting in wpDataTables to highlight the dates in red that are older than today's date.
I was thinking of using something along the lines of:
IF 'Subs_Expire' < TODAY
HIGHLIGHT RED
However, wpDataTables is not recognising 'Subs_Expire' at a date, or a datetime column, so I can't use the additional, conditional formatting operators that are only available on date / datetime columns.
Perhaps I'm making it more complicated than it needs to be and there's a simpler solution(?) - but that still doesn't explain why wpDataTables doesn't see the 'Subs_Expire' column as a date / datetime column...
Hi,
I'm using the SQL below to pull data from the database, including the 'membership_expiry_date' (aka 'Subs Expiry'). This is in a LONGTEXT format in the DB, but I'm using the SQL to convert it to a DATE, so I can use conditional formatting.
I believe the SQL is correct, but I am not able to get the same conditional formatting as when I pull a DATETIME field from the DB. i.e. the 'less than' and 'greater than' operators are not available for the 'Subs Expiry' column like they are with 'User Registered' column.
Are you able to offer any assistance?
Thanks,
Jim
Hello Jim.
Thank you for your purchase.
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please let me know if creating a VIEW helped with the issue.
Best regards.
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
Hi Aleksandar,
Thank you for your assistance. I am not familiar with using views, any chance you can start me off please? What do I need to put in this view? Where do I write it? How do I do that in wpDataTables?
FYI, the DB I'm using is MariaDB.
Kind regards,
Jim
Hey Jim.
Sorry for the late response. Wednesday 1st and Thursday 2nd of May were non-working days in our country.
It's fairly simple. You need to open the PHPMyAdmin and open the SQL tab. Then create a view like this:
CREATE VIEW `view1` AS SELECT u1.`ID` AS `User ID`, u1.`user_login` AS `Website_ID`, u1.`user_registered` AS `User_Registered`, m1.meta_value AS `Last_Name`, m2.meta_value AS `First_Name`, STR_TO_DATE(m3.meta_value, '%Y/%m/%d') as `Subs_Expire`, FROM dbName_users u1 LEFT JOIN dbName_usermeta m1 ON m1.`user_id` = u1.`ID` AND m1.`meta_key` = 'last_name' LEFT JOIN dbName_usermeta m2 ON m2.`user_id` = u1.`ID` AND m2.`meta_key` = 'first_name' LEFT JOIN dbName_usermeta m3 ON m3.`user_id` = u1.`ID` AND m3.`meta_key` = 'membership_expiry_date';
Then, you will have the view `view1` created in PHPMyAdmin, and you can create a wpDataTable by querying that view like:
Best regards.
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
Hi Aleksandar,
Thank you again for your assistance.
I managed to get the view working. I now only use the code:
in wpDataTables and it does what it's supposed to - except I still can't use the conditional formatting as I would expect to, i.e. the 'less than' and 'greater than' operators are not available for the 'Subs_Expire' column.
Why might this be the case? How can I resolve it?
Kind regards,
Jim
Hi again Jim.
I'm glad you got the view to work!
Please help me understand what you're trying to do with the "Subs_Expire" column? If it works in PHPMyAdmin, it should work the same in our plugin. That's why I'd like to know what your idea with it is, so I can offer another solution.
Also, please make sure to show me what some entries look like in that column.
Best regards.
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
Hi Aleksander,
It's pretty much all in the first ticket.
I'm trying to get the 'membership_expiry_date' out of the database as the column 'Subs_Expire'.
I then want to be able to use the conditional formatting in wpDataTables to highlight the dates in red that are older than today's date.
I was thinking of using something along the lines of:
However, wpDataTables is not recognising 'Subs_Expire' at a date, or a datetime column, so I can't use the additional, conditional formatting operators that are only available on date / datetime columns.
Perhaps I'm making it more complicated than it needs to be and there's a simpler solution(?) - but that still doesn't explain why wpDataTables doesn't see the 'Subs_Expire' column as a date / datetime column...
Kind regards,
Jim