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

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';

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

Hi Aleksandar,

Thank you again for your assistance.

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.

Why might this be the case? How can I resolve it?

Kind regards,

Jim

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:

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...

Kind regards,

Jim