WordPress.org

1. fatwombat
   Member
   Posted 9 months ago #

   Could anyone let me know why this would not be working? I get confused between OR and AND ;)

   I need to get anything that is Blue eyes or Blonde hair. At the moment this gets nothing.

   SELECT wp_posts.ID FROM wp_posts
   INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
   WHERE
   	((wp_postmeta.meta_key = 'eyes' AND wp_postmeta.meta_value = 'Blue'
   	OR wp_postmeta.meta_key = 'hair' AND wp_postmeta.meta_value = 'Blonde'))
   	AND ((wp_postmeta.meta_key = 'availability' AND wp_postmeta.meta_value >= '1'
   	AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish'))

   Thanks in advance!

2. popper
   Member
   Posted 9 months ago #

   The problem is each of those values you are checking for is a different entry on the wp_postmeta table, so you'll never find a row with both 'eyes' and 'availability' set at the same time. Something like this might work:

   SELECT wp_posts.ID
   FROM   wp_posts
          INNER JOIN wp_postmeta
            ON wp_posts.ID = wp_postmeta.post_id
   WHERE  ( ( wp_postmeta.meta_key = 'eyes'
              AND wp_postmeta.meta_value = 'Blue' )
             OR EXISTS (SELECT *
                        FROM   wp_postmeta
                        WHERE  wp_postmeta.meta_key = 'hair'
                               AND wp_postmeta.meta_value = 'Blonde'
                               AND wp_postmeta.post_id = wp_posts.ID) )
          AND ( EXISTS(SELECT *
                       FROM   wp_postmeta
                       WHERE  wp_postmeta.meta_key = 'availability'
                              AND wp_postmeta.meta_value >= '1'
                              AND wp_postmeta.post_id = wp_posts.ID) )
          AND wp_posts.post_type = 'post'
          AND wp_posts.post_status = 'publish'

3. fatwombat
   Member
   Posted 9 months ago #

   WOW! That's a bit crazy.

   Reading through it, I understand it though, I can read it fine but if I had to do that myself, no hope! Haha

   Thanks heaps for that Julia! Worked a treat :)

Reply

You must log in to post.