Help in good search query

Databases
1

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

2

if you want to use fulltext searching (which seems overkill for a varchar(255) column), then you have to create a fulltext index on that column

alternatively, use your application language to split apart the user’s search phrase into words, and generate the query so that it looks like this –

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

you could also try using ANDs instead of ORs but i expect you might not find any results

3

thanks
i try to alert table
ALTER TABLE bollywood_films ADD FULLTEXT(film_name);

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

4

ALTER TABLE bollywood_films ENGINE=myisam