Hi,

I would like to fetch users of specific role in wpdatatables. I have several custom fields that has to be fetched as well. I tried to fetch from mysql database but it doesn't help in fetching custom fields. So, I chose to fetch it from PHP serialize method, but for some reasons it doesn't happen. It would be great if you can help me in doing it. 

Thanks in advance.

Arun Yogesh.

Hi,

I have managed to get it done by MySQL. Below is the code:

SELECT 
        wpu6_users.display_name AS n_user_display_name,
        t1.meta_value AS n_user_first_name,
        t2.meta_value AS n_user_phone, 
        wpu6_users.user_email AS n_user_user_email,
        t3.meta_value AS n_user_address_1,
        t4.meta_value AS n_user_address_2,
        t5.meta_value AS n_user_area,
        t6.meta_value AS n_user_city,
        t7.meta_value AS n_user_state,
        t8.meta_value AS n_user_country,
        t9.meta_value AS n_user_postcode
        FROM wpu6_users
        LEFT JOIN wpu6_usermeta AS t1 ON t1.user_id = wpu6_users.ID 
        LEFT JOIN wpu6_usermeta AS t2 ON t1.user_id = t2.user_id 
        LEFT JOIN wpu6_usermeta AS t3 ON t2.user_id = t3.user_id  
        LEFT JOIN wpu6_usermeta AS t4 ON t3.user_id = t4.user_id
        LEFT JOIN wpu6_usermeta AS t5 ON t4.user_id = t5.user_id 
        LEFT JOIN wpu6_usermeta AS t6 ON t5.user_id = t6.user_id  
        LEFT JOIN wpu6_usermeta AS t7 ON t6.user_id = t7.user_id
        LEFT JOIN wpu6_usermeta AS t8 ON t7.user_id = t8.user_id
        LEFT JOIN wpu6_usermeta AS t9 ON t8.user_id = t9.user_id
        WHERE t1.meta_key = 'billing_first_name' 
        AND t2.meta_key = 'billing_phone' 
        AND t3.meta_key = 'billing_address_1' 
        AND t4.meta_key = 'billing_address_2'
        AND t5.meta_key = 'mv_area' 
        AND t6.meta_key = 'billing_city' 
        AND t7.meta_key = 'billing_state'
        AND t8.meta_key = 'billing_country' 
        AND t9.meta_key = 'billing_postcode' 

Now I am trying to display users based on role. Can anyone suggest the best way to do it in MySQL?

Any help is greatly appreciated. 

Thanks in advance.

Hi,

Additionally, help me in retrieving users based on role using below MySQL query.

SELECT 
        wpu6_users.display_name AS n_user_display_name,
        t1.meta_value AS n_user_first_name,
        t2.meta_value AS n_user_phone, 
        wpu6_users.user_email AS n_user_user_email,
        t3.meta_value AS n_user_address_1,
        t4.meta_value AS n_user_address_2,
        t5.meta_value AS n_user_area,
        t6.meta_value AS n_user_city,
        t7.meta_value AS n_user_state,
        t8.meta_value AS n_user_country,
        t9.meta_value AS n_user_postcode
        FROM wpu6_users
        LEFT JOIN wpu6_usermeta AS t1 ON t1.user_id = wpu6_users.ID 
        LEFT JOIN wpu6_usermeta AS t2 ON t1.user_id = t2.user_id 
        LEFT JOIN wpu6_usermeta AS t3 ON t2.user_id = t3.user_id  
        LEFT JOIN wpu6_usermeta AS t4 ON t3.user_id = t4.user_id
        LEFT JOIN wpu6_usermeta AS t5 ON t4.user_id = t5.user_id 
        LEFT JOIN wpu6_usermeta AS t6 ON t5.user_id = t6.user_id  
        LEFT JOIN wpu6_usermeta AS t7 ON t6.user_id = t7.user_id
        LEFT JOIN wpu6_usermeta AS t8 ON t7.user_id = t8.user_id
        LEFT JOIN wpu6_usermeta AS t9 ON t8.user_id = t9.user_id
        WHERE t1.meta_key = 'billing_first_name' 
        AND t2.meta_key = 'billing_phone' 
        AND t3.meta_key = 'billing_address_1' 
        AND t4.meta_key = 'billing_address_2'
        AND t5.meta_key = 'mv_area' 
        AND t6.meta_key = 'billing_city' 
        AND t7.meta_key = 'billing_state'
        AND t8.meta_key = 'billing_country' 
        AND t9.meta_key = 'billing_postcode'