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.

In wp_posts the area we would like is post_id In wp_term_relationships the area object_id should match the publish 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. 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.

UPDATE

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 (object_id)

Hope that can help

Table Structure Image

enter image description here

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