5 {{ upvoteCount | shortNum }}
5 {{ upvoteCount | shortNum }}

help in good search query

by lovefax89
4 replies
my query is

SELECT * FROM bollywood_films WHERE film_name LIKE '%love aaj kal%'

it only works when keyword is right..if i write "love ajj kal" then i found zero result.

i also try this

SELECT film_name FROM bollywood_films WHERE MATCH (film_name) AGAINST ('love aaj kal')

this query is not working.. my column type is varchar(255)
this error is shows in phpmyadmin

"#1214 - The used table type doesn't support FULLTEXT indexes"

help me if you know or provide my search query

Thanks
#good #query #search
  • Profile picture of the author baronz
    First, make sure you have a FULLTEXT index
    ALTER TABLE bollywood_films ADD FULLTEXT(film_name);
    then try this:
    "SELECT film_name, MATCH (film_name) AGAINST ('love ajj kal') AS Relevance FROM bollywood_films WHERE MATCH (film_name) AGAINST ('love ajj kal' IN BOOLEAN MODE) HAVING Relevance > 0.2"
    {{ DiscussionBoard.errors[5917400].message }}
    • Profile picture of the author robkelly
      It sounds like you have an index on the table that should not be there, so i would log into phpmyadmin and click stucture and go down to indexes and remove the index for 'film_name'
      {{ DiscussionBoard.errors[5919771].message }}
  • Profile picture of the author lovefax89
    thanks for your help...
    my engine is InnoDB
    when i try to alter my table then this msg shows phpmyadmin

    "#1214 - The used table type doesn't support FULLTEXT indexes"

    is then any way to change my engine.. because when i try this query in " MyISAM" query is working fine
    {{ DiscussionBoard.errors[5920371].message }}
  • Profile picture of the author Earnie Boyd
    You might consider looking at sql - Fulltext Search with InnoDB - Stack Overflow and this article Full-Text Search with InnoDB | Dr Dobb's suggests InnoDB has started supporting the FULLTEXT index.
    Signature
    Earnie Boyd
    {{ DiscussionBoard.errors[5922771].message }}

Trending Topics