mySQL: selecting based on several keys in key/value table

1 Views

I’m using WordPress and trying to select values from one of my own tables that correspond to values in the native wp_postmeta table.

My table is called “wellwishertable” and I would like to find all the ID’s (so I can count them) from the wellwisher table where…

Read More

1) The wellwishertable ‘associatedID’ field is in wp_postmeta post_id

2) The wellwishertable ‘associatedAuthorID’ is the same as a $userID variable

3) The wellwishertable ‘permission’ is pending

4) The wp_postmeta meta_key ‘status’ is NOT equal to meta_value ‘open’

5) The wp_postmeta meta_key ‘freeze’ is NOT equal to meta_value ‘frozen’

I’ve got this far… 

    "SELECT DISTINCT wellwisher.ID FROM wellwishertable wellwisher 
    INNER JOIN wp_postmeta ON (wellwisher.associatedID = wp_postmeta.post_id) 
    INNER JOIN wp_postmeta AS mt1 ON (wp_postmeta.post_id = mt1.post_id) 
    INNER JOIN wp_postmeta AS mt2 ON (wp_postmeta.post_id = mt2.post_id) 

    WHERE wellwisher.associatedAuthorID=".$userID." 
    AND wellwisher.permission ='pending' 
    AND ( (mt1.meta_key = 'status' AND mt1.meta_value != 'open') AND 
          (mt2.meta_key = 'freeze' AND mt2.meta_value != 'frozen') );

This seems to be ‘nearly’ working except it doesn’t count records that don’t have a “freeze” meta_key in the wp_postmeta table. I want it to count any record that isn’t “frozen” regardless of whether it exists or not (as long as ‘status’ isn’t ‘open’).

Could someone point me in the right direction?

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. Use LEFT JOIN instead of INNER JOIN on mt2, as INNER JOIN will only return results that have an entry there.

    In order to avoid requiring mt2.meta_key to always have to equal freeze, move that condition up to the ON clause in your join.

    You could also consider using COUNT directly in your query rather than figuring it out later:

    "SELECT COUNT(DISTINCT wellwisher.ID) FROM wellwishertable wellwisher 
INNER JOIN wp_postmeta ON (wellwisher.associatedID = wp_postmeta.post_id) 
INNER JOIN wp_postmeta AS mt1 ON (wp_postmeta.post_id = mt1.post_id) 
LEFT JOIN wp_postmeta AS mt2 ON (wp_postmeta.post_id = mt2.post_id) 
    AND mt2.meta_key = 'freeze'

WHERE wellwisher.associatedAuthorID=".$userID." 
AND wellwisher.permission ='pending' 
AND ( (mt1.meta_key = 'status' AND mt1.meta_value != 'open') AND 
      (mt2.meta_value != 'frozen') );

    IMPORTANT:
    Dumping your $userID directly into your query string could be a big security issue. Please consider using parameterized queries.