MATCH AGAINST with 2 WHERE clauses

[madmatter23]

Hello, I'm trying to do something fairly simple, I just need to perform a MATCH/AGAINST query but also add a second WHERE clause. I'm trying this:


SELECT * FROM tblShow, tblGuest WHERE MATCH (tblGuest.firstname, tblGuest.lastname, tblShow.topic) AGAINST ('author') AND WHERE (tblShow.fk_tblGuest = tblGuest.pk_tblGuest) ORDER BY showdate DESC,starttime


But the AND WHERE throws it off. I've also tried doing something along the lines of WHERE (MATCH (fields) AGAINST (keyword)) AND (field=field)) ORDER BY etc. But that doesn't seem to work either. Does anyone know how to make a query with both of these WHERE clauses?

Thanks a lot.

[Andrew Johnson]

Only need one "WHERE"

Try:

SELECT * FROM tblShow, tblGuest WHERE MATCH (tblGuest.firstname, tblGuest.lastname, tblShow.topic) AGAINST ('author') AND (tblShow.fk_tblGuest = tblGuest.pk_tblGuest) ORDER BY showdate DESC,starttime

[madmatter23]

Hm,

When I remove the second WHERE, I receive this error:

#1210 - Incorrect arguments to MATCH

[madmatter23]

This problem seems simple, but I still cannot find a working query. Does anyone know how to fix this?

SELECT * FROM tblShow, tblGuest WHERE MATCH (tblGuest.firstname, tblGuest.lastname, tblShow.topic) AGAINST ('keyword') AND (tblShow.fk_tblGuest = tblGuest.pk_tblGuest) ORDER BY showdate DESC,starttime

[madmatter23]

Still no luck, although I'm now trying to using a JOIN with a WHERE:


SELECT * FROM tblShow AS a INNER JOIN tblGuest AS b ON a.fk_tblGuest=b.pk_tblGuest
WHERE MATCH (b.description, b.firstname, b.lastname, a.topic) AGAINST ('$keywords')
ORDER BY a.showdate DESC,a.starttime DESC

[StupidRalph]

What happens?

[madmatter23]

I still receive the same error "Incorrect arguments to MATCH," error 1210.

[Fumigator]

Do all 4 of these columns have fulltext indices defined for each of them?

b.description, b.firstname, b.lastname, a.topic

[madmatter23]

Yes, they all have fulltext indices.

But your question gave me an idea, which provided a clue.

If I run the query and only match 1 field against the keywords, then it provides results.

So for instance, matching against 1 field from table a will work:

SELECT * FROM tblShow AS a INNER JOIN tblGuest AS b ON a.fk_tblGuest=b.pk_tblGuest
WHERE MATCH (a.topic) AGAINST ('$keywords')
ORDER BY a.showdate DESC,a.starttime DESC


Also, matching against 1 field from table b will work:

SELECT * FROM tblShow AS a INNER JOIN tblGuest AS b ON a.fk_tblGuest=b.pk_tblGuest
WHERE MATCH (b.firstname) AGAINST ('$keywords')
ORDER BY a.showdate DESC,a.starttime DESC


But if I include fields from both tables in the where match clause, it gives me the "Incorrect arguments to MATCH," error 1210.

Strangely, if I include multiple fields from the same table, that also provides an error. So this:

SELECT * FROM tblShow AS a INNER JOIN tblGuest AS b ON a.fk_tblGuest=b.pk_tblGuest
WHERE MATCH (b.firstname, b.lastname) AGAINST ('$keywords')
ORDER BY a.showdate DESC,a.starttime DESC


Will generate this error: "Can't find FULLTEXT index matching the column list." Weird, because if I match against b.firstname and b.lastname separately, it has no problem finding their indexs.

I have no idea how to work around this bug.

[angst]

I tested this on my server and it worked corrected as soon as I enabled FullText.
are you maybe using an old version of MySQL?

[madmatter23]

I'm using MySQL client version: 4.1.22, is that too old?

[angst]

yup, i think thats it.
looking at this post: http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html

"I just updated the mysql version from 4.0.15 to 4.1.8 and it works perfectly..."

[madmatter23]

I've asked my hosting company to upgrade my server's version of MySQL

phpinfo() now lists the MySQL Client API version as 5.0.45.

But it still doesn't work! Ugh. I have no idea what to do. Is there a work around for this? Maybe breaking it into multiple queries?

[madmatter23]

OK! I've got a working query. It's not terribly efficient, but it works.


SELECT * FROM tblShow AS a INNER JOIN tblGuest AS b ON a.fk_tblGuest=b.pk_tblGuest
WHERE (MATCH (b.firstname) AGAINST ('$keywords')) OR (MATCH (b.lastname) AGAINST ('$keywords')) OR (MATCH (a.topic) AGAINST ('$keywords'))
ORDER BY a.showdate DESC,a.starttime DESC


For some reason, it has an error only when I list all of the fields together (separated by commas) in one MATCH AGAINST clause. If separate the fields into separate MATCH AGAINST statements, with ORs in between, it runs the query.

Does anyone know why that would be?