The next SQL query in PHP should display results according to title and tag records. The tags are situated within the table wordpress_terms and also the posts in wordpress_posts. In the centre we've wordpress_term_associations to link everything up.
wp_posts the area we would like is
wp_term_relationships the area
object_id should match the publish id and
term_taxonomy_id should match the
term_id should match the
wp_terms_relationships. Only then do we want the area from
wp_terms known as
name with regards to the search.
Right now this code isn't including terms within the search.
SELECT ID, post_name, post_title, post_date FROM wp_posts JOIN wp_term_relationships ON object_id = wp_posts.id LEFT JOIN wp_terms ON term_id = wp_term_relationships.term_taxonomy_id WHERE wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' AND (wp_posts.post_title LIKE '%$my_text%' OR wp_terms.name LIKE '%$my_text%')"
How can we get that one working.
What should really happen may be the search should really return results according to publish title or publish tag. The issue is publish tags are thought terms and assist in another table that needs another to obtain the corresponding id.
I'll try to map the appropriate but.
TABLE: wp_posts ================================================================== post_id ==== post_title ================================================================== TABLE wp_terms ================================================================== term_id ==== name ================================================================== TABLE wp_term_relationships ================================================================== object_id ==== term_taxonomy_id ==================================================================
Therefore we use wordpress_term_associations to obtain which term id's (
term_taxonomy_id) reflect which posts (
Hope that can help
Table Structure Image
Without seeing your schema the very best I'm able to offers are below.
Using table aliases to simplify:
p = wordpress_posts
r = wordpress_term_associations
t = wordpress_terms
SELECT p.ID, p.post_name, p.post_title, p.post_date, t.name FROM wp_posts p, wp_term_relationships r LEFT JOIN wp_terms t ON t.term_id = r.term_taxonomy_id WHERE r.object_id = p.ID AND p.post_type = 'post' AND p.post_status = 'publish' AND (p.post_title LIKE '%my_text%' OR t.name LIKE '%my_text%')
I am unsure I realize the question properly, but when you love to obtain the title of connected terms then this works:
$query = "SELECT DISTINCT ID, post_name, post_title, post_date FROM wp_posts JOIN wp_term_relationships ON object_id = wp_posts.id LEFT JOIN wp_terms ON term_id = wp_term_relationships.term_taxonomy_id WHERE wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' AND (wp_posts.post_title LIKE '%$my_text%' OR wp_terms.name LIKE '%$my_text%')";