In usermeta
table WordPress stores many serialized data. So direct meta_value
would not work I think. So try the below query-
SELECT a.id,
a.user_email,
a.user_registered,
a.user_login,
b1.meta_value AS first_name,
b2.meta_value AS last_name,
b3.meta_value AS qualified,
b4.meta_value AS referrer,
b5.meta_value AS view_type,
b6.meta_value AS ref_by,
b7.meta_value AS wp_optimizemember_custom_fields
FROM wp_users AS a
INNER JOIN wp_usermeta AS b1
ON b1.user_id = a.id
AND b1.meta_key = 'first_name'
INNER JOIN wp_usermeta AS b2
ON b2.user_id = a.id
AND b2.meta_key = 'last_name'
INNER JOIN wp_usermeta AS b3
ON b3.user_id = a.id
AND b3.meta_key = 'qualified'
INNER JOIN wp_usermeta AS b4
ON b4.user_id = a.id
AND b4.meta_key = 'referrer'
INNER JOIN wp_usermeta AS b5
ON b5.user_id = a.id
AND b5.meta_key = 'view_type'
INNER JOIN wp_usermeta AS b6
ON b6.user_id = a.id
AND b6.meta_key = 'ref_by'
INNER JOIN wp_usermeta AS b7
ON b7.user_id = a.id
AND b7.meta_key = 'wp_optimizemember_custom_fields'
WHERE ( b4.meta_value LIKE '%{$user_ID}%'
AND b5.meta_value LIKE '%lead%' )
OR ( b6.meta_value LIKE '%{$user_ID}%'
AND b5.meta_value LIKE '%lead%' )
Look here I put all the meta_value
‘s value between two %
. That means it will take the serialized data and will search it as string.
Hope that helps.