Using Boolean Fulltext Searching with Multiple Keywords

[ximenao]

Hello,

I am making an online exam with textboxes. If the user enters an answer with the correct keyword or keywords,

then he will be told "You are correct!" and of course "You are wrong" if the opposite is true...

To do this I am using a fulltext search with a mysql database table with three fields (q1, q2, q3) with the text format

and one id field as the primary key as tinyint set to auto increment. I used the following code:

 

ALTER TABLE formone_ex1_secthreesr ADD FULLTEXT(q1,q2,q3,q4,q5);

 

to create a fulltext index. I use phpmy admin which tells me that a fulltext index exists.

It was recommended to me to use the following code:

 

$query="SELECT 
MATCH (q1) AGAINST ('$uno' IN BOOLEAN MODE) as ans1,
MATCH (q2) AGAINST ('$dos' IN BOOLEAN MODE) as ans2,
MATCH (q3) AGAINST ('$tres' IN BOOLEAN MODE) as ans3
FROM formone_ex1_secthreesr";


$data=mysql_query($query) or die(mysql_error()); 



mysql_close();



if($data["ans1"]!='0') {
        echo "<p><font color=\"#7E4B01\" size=\"+1\"><b> $uno</b></font> is correct!</p>";
} else {
        echo "<p>Sorry,<font color=\"#FF0000\" size=\"+1\"><b> $uno</b></font> is wrong! The correct answer is <font color=\"#7E4B01\" size=\"+1\">\"<b>Yo me llamo John Smith</b>.\"</font></p>";
}

if($data["ans2"]!='0') {
        echo "<p><font color=\"#7E4B01\" size=\"+1\"><b> $dos</b></font> is correct!</p>";
} else {
        echo "<p>Sorry,<font color=\"#FF0000\" size=\"+1\"><b> $dos</b></font> is wrong! The correct answer is <font color=\"#7E4B01\" size=\"+1\">\"<b>¿Qué hora es?</b>\"</font></p>";
}

if($data["ans3"]!='0') {
        echo "<p><font color=\"#7E4B01\" size=\"+1\"><b> $tres</b></font> is correct!</p>";
} else {
        echo "<p>Sorry,<font color=\"#FF0000\" size=\"+1\"><b> $tres</b></font> is wrong! The correct answer is <font color=\"#7E4B01\" size=\"+1\">\"<b>¿Qué fecha es?</b>\"</font></p>";
}

 

 

However, even if I enter an incorrect keyword, not contained within the database, it is returned as correct.

Interestingly I tried removing "IN BOOLEAN MODE" and I get a error saying

"Can't find FULLTEXT index matching the column list query:"

 

 

Can anyone help me fix this please?

[ximenao]

As an additional note, let me add that the MySQL version is 5.0

[fenway]

Show the CREATE TABLE output.

[ximenao]

Thanks for responding Fenway.

Below is the MySQL code for the table

 

CREATE TABLE formone_ex1_secthreesr (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`q1` TEXT( 75 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`q2` TEXT( 75 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`q3` TEXT( 75 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`q4` TEXT( 75 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`q5` TEXT( 75 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL 
)

 

And here is the code I used to modify each field to a fulltext index:

 

ALTER TABLE formone_ex1_secthreesr ADD FULLTEXT(q1)

 

Before I used this code

 

ALTER TABLE formone_ex1_secthreesr ADD FULLTEXT q1 (

`q1` ,

`q2` ,

`q3` ,

`q4` ,

`q5`

)

 

But that didn't work either.

 

[fenway]

Well, since you're named the index the same, you need to drop the other one first -- or give it a different name.

[ximenao]

Once again thanks for responding fenway...

Yeh I dropped the initial index, the one with the multiple fields.

Right now every field is indexed individually and it still doesn't work...

 

Time is running out, I'm thinking of approaching it from a completely different angle

but how I don't know as yet.

[fenway]

MySQL won't combine individual column indexes for FULLTEXT -- use the combined one.