4

hope someone can help me. I am trying to use the full search text MATCH and AGAINST.

I am getting this error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''mytable' WHERE MATCH(user) AGAINST('alex') LIMIT 0, 25' at line 1

my example query is:

SELECT id, user FROM 'mytable' WHERE MATCH(user) AGAINST('alex') LIMIT 0, 25
Narendrasingh Sisodia
  • 21,247
  • 6
  • 47
  • 54
alex
  • 55
  • 4
  • my database is now MyISAM type but error still persisting – alex Feb 16 '16 at 11:13
  • 1
    You don't need quotes around your `'mytable'` tablename instead you can use `backticks` `\`mytable\`` or simply without any `backticks` like as`mytable` if table name is not a special keyword – Narendrasingh Sisodia Feb 16 '16 at 11:14
  • what is this error means? #1191 - Can't find FULLTEXT index matching the column list – alex Feb 16 '16 at 11:25

2 Answers2

3

You can only use backticks around table or column name, you can not use anything except backticks.

SELECT id, user FROM `mytable` WHERE MATCH(user) AGAINST('alex') LIMIT 0, 25

Where backticks required?

If your table or column name contains any MYSQL reserve word than you must need to use backtick around the name.

MYSQL Reserve Keywords and Words

Community
  • 1
  • 1
devpro
  • 16,184
  • 3
  • 27
  • 38
  • what is this error means? #1191 - Can't find FULLTEXT index matching the column list – alex Feb 16 '16 at 11:25
  • @alex: r u not using full text index??? u need to add index for full text search. ALTER TABLE table ADD FULLTEXT index_name(column1); – devpro Feb 16 '16 at 11:27
  • @alex: or you can also follow this question: http://stackoverflow.com/questions/21693475/1191-cant-find-fulltext-index-matching-the-column-list – devpro Feb 16 '16 at 11:28
2

You don't need to quote mytable. DOCS

SELECT id, user FROM mytable WHERE MATCH(user) AGAINST('alex') LIMIT 0, 25

For the error mentioned. 

ALTER TABLE mytable ADD FULLTEXT index_name(user);

The fulltext index should contain exactly the same number of columns, in same order as mentioned in MATCH clause.

Here for more.

Community
  • 1
  • 1
Nabin Kunwar
  • 1,965
  • 14
  • 29