volleyballmaniac(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.
