PSET8 - MASHUP : Can't find FULLTEXT indexes when using WITH QUERY EXPANSION

Question

I'm working on the final pset, more specifically on the search.php file.

I've figured out how to use query() and I'm actually taking advantage of print(json_encode($places, JSON_PRETTY_PRINT)); to check the results of my matching.

Here's below a picture of my places table in mySQL:

So I've set up the Index_type of country_code, postal_code, place_name, admin_name1, admin_code1 to FULLTEXT and gave a Key_name to each of them (in case of they could be eventually necessary).

When I call query() to SELECT all rows from my places table where I MATCH (country_code, postal_code, place_name, admin_name1, admin_code1) AGAINST $_GET["geo"] IN BOOLEAN MODE, everything works fine.

Now if I call the same query() to SELECT all rows from my places table where I MATCH (country_code, postal_code, place_name, admin_name1, admin_code1) AGAINST $_GET["geo"] WITH QUERY EXPANSION, I always get this:

Fatal error: Can't find FULLTEXT index matching the column list in /home/jharvard/vhosts/pset8/includes/functions.php on line 49

I've tried to replace the Column_name in MATCH with the Key_name but it didn't worked too.

I've checked more than once the dev page relative to Full-Text searches WITH QUERY EXPANSION to be sure I've written my code with the right syntax but despite all my efforts, nothing but IN BOOLEAN MODE works.

I've also tried everything recommended on this page and guess what? Nothing resolved my problem! :)

Could somebody explain me why IN BOOLEAN MODE is the only full-text search method to work please?

Answer 1

It is very weird but I advice you to follow these steps:

1. Use ALTER TABLE <table> ENABLE KEYS to make sure that your keys are active.
2. If nothing's still working, use ALTER TABLE <table> DROP INDEX <key_name or if no key_name set, column_name>. This command erase your Index_type and will allow us to reassign the columns with a FULLTEXT Index_type

3. Re-Assign each column's FULLTEXT Index_type with this line: ALTER TABLE <table> ADD FULLTEXT(<column_name>). Type each command on the same SQL query like this:

   ALTER TABLE <table> ADD FULLTEXT(<column_name1>); ALTER TABLE <table> ADD FULLTEXT(<column_name2>); ALTER TABLE <table> ADD FULLTEXT(<column_name3>);

For the last line of your query, you must enter this group command: ALTER TABLE <table> ADD FULLTEXT(<column_name1>, <column_name2>, <column_name3>, ...), so your final query before hitting the go button looks like this:

ALTER TABLE <table> ADD FULLTEXT(<column_name1>);
ALTER TABLE <table> ADD FULLTEXT(<column_name2>);
ALTER TABLE <table> ADD FULLTEXT(<column_name3>);
ALTER TABLE <table> ADD FULLTEXT(<column_name1>, <column_name2>, <column_name3>);

A success message should be prompted on your screen:

ALTER TABLE  `table` ADD FULLTEXT (
`column_name1`
);# 43633 rows affected.
ALTER TABLE  `table` ADD FULLTEXT (
`column_name2`
);# 43633 rows affected.
ALTER TABLE  `table` ADD FULLTEXT (
`column_name3`
);# 43633 rows affected.
ALTER TABLE  `table` ADD FULLTEXT (
`column_name1` ,
`column_name2` ,
`column_name3`
);# 43633 rows affected.

The most important thing to read in the success message is `# XXX rows affected.`` If this detail is prompted, you should be fine.

Now try to test your search.php with WITH QUERY EXPANSION and now everything should work!

EDIT: I've modified the original post to provide one clear and detailed answer to my question.