0

I have a query that works when I have a field view_type = sale but when the view_type = lead it doesn't return any records even when I know there is one. Here is the 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 a
INNER JOIN wp_usermeta b1 ON b1.user_id = a.ID AND b1.meta_key = 'first_name' 
INNER JOIN wp_usermeta b2 ON b2.user_id = a.ID AND b2.meta_key = 'last_name'
INNER JOIN wp_usermeta b3 ON b3.user_id = a.ID AND b3.meta_key = 'qualified'
INNER JOIN wp_usermeta b4 ON b4.user_id = a.ID AND b4.meta_key = 'referrer'
INNER JOIN wp_usermeta b5 ON b5.user_id = a.ID AND b5.meta_key = 'view_type'
INNER JOIN wp_usermeta b6 ON b6.user_id = a.ID AND b6.meta_key = 'ref_by'
INNER JOIN wp_usermeta 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') 

What wrong with this query?

0

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.

  • Thanks for that, i'm inserting the data in to the meta values and its just a single string not serialised. So its not working still if you read when I said above, it works when the string is 'sale' and not when its a 'lead'. Pulling my hair out I really need this code fixed. – Nelson101 Dec 22 '16 at 7:58
  • Can you give us both the table with data ? Just go to you database administrator and download those tables as SQL file and attach here. – CodeMascot Dec 22 '16 at 8:08
  • The files are big and they have real data so it cant be displayed here. – Nelson101 Dec 22 '16 at 8:15
  • On which meta_key 'lead' does not work and 'sale' works ? – CodeMascot Dec 22 '16 at 8:16
  • Bottom line is this there is a record with a 'view_type' value 'lead' with the right user ID the query is just not returning anything zero. – Nelson101 Dec 22 '16 at 8:17
0

Ok so the problem was that 1 record didn't have the 'ref_by' meta_key set and with INNER JOINS all must succeed, thats why I wasn't getting a result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.