I have a discussions table and a discussion_posts table. It's pretty much like this:
discussions
-----------
id | title
1 | upload test
2 | random
3 | another
discussion_posts
----------------
id | disc_id | post
1 | 1 | my post
2 | 2 | test
3 | 2 | upload
4 | 3 | test
I am trying to get a search so that if I look for "upload test" it will match the first two discussions. This is because "Upload Test" is the obvious title of discussion 1 while "test" and "upload" are both words in two separate posts within "Random". The third should not show up because it only has one of the words.
I have one query for discussions already squared away and I was trying to do posts on its own like this:
SELECT DISTINCT p.disc_id FROM discussion_posts p
INNER JOIN discussions disc ON disc.id=p.disc_id
WHERE (post LIKE "%upload%") AND (post LIKE "%test%")
Using AND will exclude "Random" since the two words are in two posts, while using OR will accidentally include "Another" since it has test in one post.
Any ideas?
