Geek Answers Handbook

MySQL search for multiple keywords

Look for posts matching two keywords (both, not just one of them).

I think the reason the next MySQL query returns no matches is because the keywords that match "climate" and the keywords that match "recycle" don't match the rows in the t_keywords table. How do you go on?

SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND t_keywords.keyword = "climate"
AND t_keywords.keyword = "recycling"
GROUP BY t_posts.id
+4
source share
4 answers
SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND t_keywords.keyword IN ('climate', 'recycling')
GROUP BY t_posts.id HAVING count(t_keywords.id) = 2

, , , - . , "2" , .

+2

: http://www.w3schools.com/sql/sql_and_or.asp

- "" - , t_keywords.keyword = "" - , (t_keywords.keyword = "recycling" ) FALSE.
TRUE AND FALSE FALSE - , .

IN, () OR :

WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND (t_keywords.keyword = "climate" OR t_keywords.keyword = "recycling")
GROUP BY t_posts.id
+1

:

SELECT
    t_posts.id, t_posts.title
FROM
    t_posts
    INNER JOIN t_posts_keywords ON t_posts_keywords.id_post = t_posts.id
    INNER JOIN t_keywords ON t_keywords.id = t_posts_keywords.id_keyword
WHERE
    t_keywords.keyword IN ('climate', 'recycling');

E: . This is based on your query to find entries from t_keywords with the key value "climate" or "recycle" that is tied to the t_posts_keywords rotation table for t_posts for output. p>

+1
source

You can use IN. Like this:

SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND t_keywords.keyword IN("climate","recycling")
GROUP BY t_posts.id

You can also use ORbetween. Like this:

SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND 
(
   t_keywords.keyword ="climate" OR
   t_keywords.keyword ="recycling"
)
GROUP BY t_posts.id
0
source

More articles:


All Articles