WordPress.org

Ready to get started?Download WordPress

Forums

Problem with SQL for $WPDB Search query (2 posts)

  1. volleyballmaniac
    Member
    Posted 4 years ago #

    (MY CODE IS BELOW..........)

    I'm trying to do a custom search query that utilizes $wpdb & SQL.

    The customized search should return the following:

    1. retrieves wp_posts.guid and wp_posts.post_title
    2. retrieves related wp_terms.name (if wp_term_taxonomy.parent = '906')

    --------------------------------------------------------------------

    SIDENOTE: 906 is a 'ratings' category, and has the following subcategories assigned to it"
    5-star
    4-star
    3-star
    2-star
    1-star

    I would like the search results be returned in the following format:

    "Business 1 - 4-stars"
    "Business 2 - 3-stars"
    "Business 3 - 5-stars"
    etc....

    --------------------------------------------------------------------

    So far, I've been able to get the search to do Step 1 above, however I've had issue with Step 2, getting the proper wp_terms.name (star-rating) to be returned.

    I need it to return the terms.names belonging ONLY to the term_taxonomy.parent '906',

    Instead it is returning terms.names for posts associated in the LEFT JOIN for the wp_terms table (code below).

    So my question is: What changes to my SQL need to be made to get the terms.name associated with '906' as their category->parent returned in the query?

    In order for the search to work, I need the columns returned in one table in the array, so that the "4-star" (or whatever) can be directly associated with "Business 1" in a row.

    Here's the SQL i've been working on:
    --------------------------------------------------------------------

    $array = $wpdb->get_results
          ("
          SELECT DISTINCT
          wp_posts.ID
          , wp_posts.post_title
          , wp_posts.guid
          , wp_posts.post_modified
          , wp_posts.post_type
          , wp_terms.name
    
          FROM wp_posts
    
          LEFT
          JOIN wp_term_relationships
          ON wp_posts.ID = wp_term_relationships.object_id
    
          LEFT
          JOIN wp_term_taxonomy
          ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
          AND (wp_term_taxonomy.taxonomy = 'post_tag' OR wp_term_taxonomy.taxonomy = 'category')
    
          LEFT
          JOIN wp_terms
          ON wp_term_taxonomy.term_id = wp_terms.term_id
    
          WHERE wp_posts.post_title LIKE '%$findthis%'
          OR wp_terms.name LIKE '%$findthis%'
    
          AND wp_posts.post_password = ''
          AND wp_posts.post_status = 'publish'
          OR wp_posts.post_status = 'static'
    
          ORDER BY wp_posts.post_modified DESC
          ", ARRAY_A);

    --------------------------------------------------------------------

    Any help would be greatly appreciated, as I've been working on this for over 40 hours.

  2. volleyballmaniac
    Member
    Posted 4 years ago #

    For some reason, you can't put links in topics anymore, so I'm putting it here.

    here's the website: http://hotguam.com

    The search bar is at the top-right. Try searching "burger", to see the search page open up.

Topic Closed

This topic has been closed to new replies.

About this Topic