PHP标签系统和搜索系统

I'm working on a simple tag product system and search system.. I tried a few methods with explode and arrays but i couldnt make it work.

I have 3 tables

  1. tags id title
  2. posts_tags postid tagid
  3. posts

Is there any solution so I can do a multiple-tags search with sql statements, or just for a one single tag? I just want to make a search system so users can search posts by one or more tags.

//You can join tables
SELECT tags.*, posts.* FROM posts LEFT JOIN posts_tags ON posts.id = posts_tags.postid LEFT JOIN tags ON tags.id = posts_tags.tagid WHERE posts_tags.postid  = '$post_id'

Where $post_id is the id of the post you want search its tags

Hope it helps

To use SQL for this sort of application, you need to search for matching tags, and then count them.

For example, if you want the id values of posts tagged both July and Beach, you might do this.

       SELECT pt.post_id
         FROM posts_tags pt
         JOIN tags t ON t.id = pt.tag_id
        WHERE t.title IN ('July','Beach')
        GROUP BY pt.post_id
       HAVING COUNT(*) = 2

The HAVING clause requires both tags, and the IN () clause mentions the tag titles.

If you want to search for post_id values and show the ones with the most matching tags first, you could try this.

       SELECT pt.post_id, COUNT(*) score
         FROM posts_tags pt
         JOIN tags t ON t.id = pt.tag_id
        WHERE t.title IN ('June', 'July', 'August', 'Beach')
        GROUP BY pt.post_id
        ORDER BY COUNT(*) DESC

Then to get the actual posts, you join all that to your posts table, and your result looks like this.

SELECT match.score, posts.column, posts.column, posts.colun
  FROM posts
  JOIN (
       SELECT pt.post_id, COUNT(*) score
         FROM posts_tags pt
         JOIN tags t ON t.id = pt.tag_id
        WHERE t.title IN ('June', 'July', 'August', 'Beach')
        GROUP BY pt.post_id
       ) match ON posts.id = match.post_id
 ORDER BY match.score DESC

See the trick to it? You need to count the number of matches to tags when you're trying to match multiple ones.