Context
I just added the FULLTEXT index onto a few columns between two separate tables. The actual fulltext implementation works fine; however, when I use the MATCH() AGAINST() in the WHERE clause, my other specification in the WHERE clause fails to work. My query goes like so:
SELECT
articles.article_id,
articles.title,
articles.article,
articles.idUsers,
articles.published,
users.uidUsers,
MATCH(articles.title, articles.article) AGAINST (? IN BOOLEAN MODE) AS content,
MATCH(users.uidUsers) AGAINST (? IN BOOLEAN MODE) AS user,
articlecoverimages.image,
profileimages.image AS profileImage
FROM
articles
JOIN users
JOIN articlecoverimages
JOIN profileimages
ON users.idUsers = articlecoverimages.idUsers
AND
articles.article_id = articlecoverimages.article_id
AND
profileimages.idUsers = users.idUsers
WHERE //start of the code in question
MATCH(articles.title, articles.article) AGAINST (? IN BOOLEAN MODE)
OR
MATCH(users.uidUsers) AGAINST (? IN BOOLEAN MODE)
AND
articles.published = ? //end of the code in question
ORDER BY
(content + user) DESC
LIMIT 10
This query works fine except for one thing: despite that fact that I specified that the variable used with
...
AND
articles.published = ?
...
is ? = "yes", the query still returns rows where articles.published is equal to "no". Everything else about the query is fine and works well; the articles are ordered by relevance of text and username.
Question
Is there a rule where you can't specify anything in the WHERE clause when using FULLTEXT or something of that sort? If not, is there anything visibly wrong with my code?
What I've Tried
I have already tried changing the order of the articles.published = ? to before and after the
MATCH specifications in the WHERE clause to no avail. Let me know if there's something else I could put on here to make this easier to understand or if you have any questions. Thanks.