My site is like imdb.com - it stores movie details and actor's names. In my actor field, I'm storing actors name like "name1,name2,name3,name4".
My site has the option to search movies by actor name. My code for this is below:
SELECT * FROM imdb WHERE actors LIKE '%%actorname%%'
The problem is that if a visitor searches for 'Lal', then movies with actor's named 'Mohanlal' and 'Lal' will both appear. I only want to list the 'Lal' movies. How can I write the query to do that?
Try the following query:
SELECT * FROM `imdb` WHERE `actors` RLIKE '[[:<:]]Lal[[:>:]]'
Even better, and easier:
SELECT * FROM `imdb` WHERE find_in_set('Lal',actors)
Make the query like this:
SELECT * FROM imdb WHERE actor LIKE 'actorname'
and put a text next to your inputfield to let them use wildcards for search. Then, if they want to search for 'Lal', the will only find 'Lal', if they want to search for everything that ends on 'lal' (%lal), they will find Mohanlal and Lal.
You should have created separated table for actors, anyway we will try to find solution of what we are left to.
SELECT * FROM imdb WHERE actors LIKE 'Lal,%' OR actors LIKE '%,Lal%' OR actors = 'Lal'
SELECT * FROM imdb WHERE actor LIKE '%,lal' or actor LIKE 'lal'
I think this is what you need:
SELECT whatever FROM whereever AS t WHERE t.actors = SOUNDEX('test')