[Resolved] Fulltext search - nonvalid MySQL result problem

[bunner_bob]

My database has information in two different languages. I've set up two different six-field FULLTEXT indexes, which cover the following fields

"contentindex" contains
title
blurb
maintext
first
last
otherinst

"contentindexsp" contains
titlesp
blurbsp
maintextsp
first
last
otherinstsp

When I run this search it works fine:

SELECT id,title AS title,itemtype,MATCH (title,blurb,maintext,first,last,otherinst) 
AGAINST ('Spanish') AS score FROM cpcontent WHERE MATCH (title,blurb,maintext,first,last,otherinst) 
AGAINST ('Spanish')

But this search gives me "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource":

SELECT id,titlesp AS title,itemtype,MATCH (titlesp,blurbsp,maintextsp,first,last,otherinstsp) 
AGAINST ('Spanish') AS score FROM cpcontent WHERE MATCH (titlesp,blurbsp,maintextsp,first,last,otherinstsp) 
AGAINST ('Spanish')

I'm sure it's something simple, but I don't know what it is - help anyone?

• Bob

[tsinka]

Hi,

which error do you get if you change the mysql_query line in your code to e.g.

$res = mysql_query($sql) or die("SQL error: ".mysql_error());

The invalid result resource seems to be caused by an error in ther query.

Thomas

[bunner_bob]

Oh it's definitely an error in the query - I just can't figure out what it could be. I'm thinking I'm doing something illegal with regards to MATCH.

Either that or it's because I have two FULLTEXT indexes in one table - could that confuse things? Maybe PHP is using the wrong index, which doesn't index a number of the fields.

Suppose I should start dissecting - just thought I'd see if it raised any obvious issues.

• Bob

[tsinka]

Hi,

please post the error message.

Thanks,
Thomas

[bunner_bob]

Boy do I feel stupid. And not so stupid.

The not so stupid part is, I didn't realize you could display "mysql_error()" and actually learn something about what went wrong. I've just been troubleshooting in the dark until now. This should prove useful in the future.

The stupid part is, while I created the second 6-column index on my LOCAL version of the database, I didn't do so on the LIVE version. All my problems were of course on the live version.

The mysql_error() message told me it couldn't find a matching FULLTEXT index on the columns I was searching on, which led me to the problem.

Thanks, for both solving my problem and teaching me a new tool.

• Bob