mySQL搜索引擎仍然无法获得适合多个表的关系

The following SQL query in PHP should display results based on title and tag entries. The tags are located in the table wp_terms and the posts in wp_posts. In the middle we have wp_term_relationships to link everything up.

In wp_posts the field we want is post_id In wp_term_relationships the field object_id should match the post id and term_taxonomy_id should match the term_id. In wp_terms the term_id should match the term_taxonomy_id in wp_terms_relationships. Then we want the field from wp_terms called name for the purpose of the search.

At the moment this code is not including terms in 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 do we get this one working.

UPDATE

What is supposed to happen is the search is supposed to return results based on post title or post tag. The problem is post tags are considered terms and help in another table that requires a third to find the corresponding id.

I will try and map the relevant but.

TABLE:  wp_posts

==================================================================
    post_id       ====     post_title
==================================================================

TABLE wp_terms

==================================================================
        term_id       ====     name
==================================================================

TABLE wp_term_relationships

==================================================================
        object_id       ====     term_taxonomy_id
==================================================================

So we use wp_term_relationships to get which term id's (term_taxonomy_id) reflect which posts (object_id);

Hope that helps

Table Structure Image

I'm not sure I understand the question correctly, but if you like to get the name of associated terms then this will work:

$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%')";