SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stuck in wordpress meta key and values SQL Query

    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
    Code MySQL:
    $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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, thanks, now i understand
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, thanks, now i understand
    Code:
    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
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hiddenpearls View Post
    I dont need the OR between meta_keys
    yes you do

    please look at the HAVING clause and then please try the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •