Stuck in wordpress meta key and values SQL Query

Databases
1

hi guys,

I am stuck in finding the way of how to make such query that will find the data from meta_key and meta_values form wordpress post meta table related with posts.

Let me show u what I am trying to achieve:
go to this page
http://jollyhits.com/classiads/test-list/

one field is category field and other fields are meta_key and values.
So, the SQL query that I am using is

$querystr="SELECT distinct wposts.* 
FROM $wpdb->posts wposts
	LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
	LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE ((wpostmeta.meta_key = 'Regione' AND wpostmeta.meta_value ='".$_REQUEST['cmb_search_region']."')
	OR (wpostmeta.meta_key = 'Provincia' AND wpostmeta.meta_value ='".$_REQUEST['cmb_search_province']."'))
	  AND (wposts.post_title like '%".$_REQUEST['srch_text']."%' OR wposts.post_content like '%".$_REQUEST['srch_text']."%')
    AND wposts.post_type = 'post'
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->term_taxonomy.term_id IN (".$_REQUEST['cmb_search_categories'].")
ORDER BY wpostmeta.meta_value ASC
LIMIT 4
";

but it is not showing any results

2

if you think this is a problem with your query, please echo the actual sql string, and tell us something about each table and how they are related

if you think this is a problem with your php, please ask to have this thread moved to the php forum

3

hi , so nice of ur your reply , Hope you will solve my problem !

In wordpress, there are 2 tables , post and postmeta.
I want to search values from more than one meta_keys

4

if you think this is a problem with your query, please echo the actual sql string, and tell us something about each table and how they are related

5

There are 2 tables, Posts and Postsmeta

Postsmeta table

meta_id post_id meta_key meta_value
1 3 Regione 17
2 3 Provincia 3
3 6 Regione 17

Posts table

ID post_title post_content
3 final text here …
6 blog php text here …

post_id is the foreign key in postsmeta table.

Now I want to write such query which shows those posts who’s meta_key is Regione and meta_vaue is 17 and meta_key is Provincia and meta_value is 3.

6

okay, thanks, now i understand :slight_smile:

SELECT posts.ID 
     , posts.post_title 
     , posts.post_content
  FROM ( SELECT post_id
           FROM postsmeta
          WHERE meta_key = 'Regione' AND meta_value = 17  
             OR meta_key = 'Provincia' AND meta_value = 3
         GROUP
             BY post_id
         HAVING COUNT(*) = 2 ) AS m
INNER
  JOIN posts
    ON posts.ID = m.post_id
7

I dont need the OR between meta_keys … I want to fetch those posts who have must Regione and Provincia and their respective values

8

yes you do :slight_smile:

please look at the HAVING clause and then please try the query