Support » Plugins » Problem with SQL for $WPDB Search query

  • NOTE: this is a repost, because something was wrong with my original topic posting (can’t leave a comment, or tag, or add to favorites….problem with BBpress I think.)

    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 (if wp_taxonomy.parent = ‘906’)


    SIDENOTE: 906 is a ‘ratings’ category, and has the following subcategories assigned to it”

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

    “Business 1 – 4-stars”
    “Business 2 – 3-stars”


    I modified the search box (top-right of page) here:

    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 (star-rating) to be returned.

    I need it to return the terms.names belonging to the parent ‘906’, BUT instead it is returning terms.names for posts associated in the LEFT JOIN (code below).

    So my question is: What changes to my SQL need to be made to get the to be associated with ‘906’ (ratings parent category).

    In order for the search to work, I need the columns returned in one table in the array, so that “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
    , wp_posts.post_title
    , wp_posts.guid
    , wp_posts.post_modified
    , wp_posts.post_type
    FROM wp_posts
    JOIN wp_term_relationships
    ON wp_posts.ID = wp_term_relationships.object_id
    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')
    JOIN wp_terms
    ON wp_term_taxonomy.term_id = wp_terms.term_id
    WHERE wp_posts.post_title LIKE '%$findthis%'
    OR 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.

  • The topic ‘Problem with SQL for $WPDB Search query’ is closed to new replies.