help in good search query

by 4 replies
5
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
#programming #good #query #search
  • 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"
    • [1] reply
    • 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'
  • 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
  • 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.
Join the discussion

Next Topics on Trending Feed