create proper MYSQL query Wordpress

Question

Hi i have a problem to create an sql query request. I have a working query to get published or private posts on category and a custom post-type and a meta_key 'mapcoordinates' in my wp_usermeta table on user_id. What i try to accomplished is extending the query that I also get another meta_value from my wp_usermeta table with meta_key 'some_value'. So I need to search on two meta_keys values (mapcoordinates , somevalue) instead off one (mapcoordinates), see working example Maybe somebody can help me'

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID, wp_usermeta.meta_value AS user_map_coord, wp_usermeta.user_id
        FROM wp_posts  
        INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
        INNER JOIN wp_usermeta  ON ( wp_posts.post_author = wp_usermeta.user_id )
        WHERE 1=1
        AND ( wp_term_relationships.term_taxonomy_id IN (5,6))
        AND  wp_posts.post_type = 'ouder_type'
        AND wp_usermeta.meta_key= 'mapcoordinates'
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
        GROUP BY wp_posts.ID ORDER BY wp_posts.post_date

Above query is working. I like to extend it in something like this.

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID, wp_usermeta.meta_value AS user_map_coord, wp_usermeta.user_id
        FROM wp_posts  
        INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
        INNER JOIN wp_usermeta  ON ( wp_posts.post_author = wp_usermeta.user_id )
        WHERE 1=1
        AND ( wp_term_relationships.term_taxonomy_id IN ( 5,6))
        AND  wp_posts.post_type = 'ouder_type'
        AND wp_usermeta.meta_key= 'mapcoordinates'
        // this line added not working i get only one result , expecting 2 results
        AND wp_usermeta.meta_key= 'somevalue'
        AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
        GROUP BY wp_posts.ID ORDER BY wp_posts.post_date

don't you rather need AND ( ... OR ...) ?

St3an
– St3an

2016-03-21 19:07:32 +00:00
Commented Mar 21, 2016 at 19:07 — St3an
– St3an, Commented Mar 21, 2016 at 19:07

kezsto · Accepted Answer · 2016-03-21 19:34:09Z

0

You have wp_usermeta.meta_key equal to two different things at the same time, so probably shouldn't get any results. I guess what you're after is more along the lines of:

AND wp_usermeta.meta_key IN ('mapcoordinates','somevalue')

answered Mar 21, 2016 at 19:34

kezsto

364 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bram Van Stel Over a year ago

I tried already , AND wp_usermeta.meta_key IN ('mapcoordinates','somevalue'). The problem is that i miss one column in my result. The column names off my result are : ID----meta_value---user_map_coord----post_id ok with aliases i can rename the columns in my result thats not an issue. In my result the values of meta_value and user_map_coord are the same. I understand why because in my select i only ask for 3 fields and i need four .

Collectives™ on Stack Overflow

create proper MYSQL query Wordpress

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related