php - MySQL like on special characters which are not present -

this strange question here goes have tv listing page displays list of shows genre. genre column value each show looks (text string):

adventure, action & adventure, sci-fi & fantasy 

now if search adventure correct results, if have special characters in between no results see below:

$query .= " select t1.*,  t2.content genres tvshows t1 left join tvshows_content t2 on t2.tvshows_id = t1.id , t2.type = 'genres' (t2.content "%,sci fi fantasy,%" or t2.content "sci fi fantasy,%" or t2.content "%,sci fi fantasy" or t2.content = "sci fi fantasy" or t2.content "%sci fi fantasy%" or t2.content "sci-fi-fantasy" ) "; 

my question remove special characters page slugs, want check if maybe present? possible?

if can't find results searching 'action & adventure' not problem, using special character, more likely, using different character encodings in field , query. try binary operator this, try different optimization first.

have @ sql query using:

select t1.*, t2.content genres tvshows t1 left join tvshows_content t2 on t2.tvshows_id = t1.id , t2.type = 'genres' (   t2.content "%,sci-fi-fantasy,%" or    t2.content "sci-fi-fantasy,%" or    t2.content "%,sci-fi-fantasy" or    t2.content = "sci-fi-fantasy" or    t2.content "%sci-fi-fantasy%" or    t2.content "sci-fi-fantasy"  ) 

you doing lot there make sure, genre searching might in beginning or end of list. there 2 different approaches this. 1 nice 1 find_in_set made searching comma separated strings:

select t1.*, t2.content genres tvshows t1 left join tvshows_content t2 on t2.tvshows_id = t1.id , t2.type = 'genres' find_in_set('sci-fi-fantasy',t2.content) > 0 

but here have make sure queries character encoding matches fields character encoding if want search i.e. umlaut. give try , if not work, or can't match encodings, can use way - bit less elegant - concating commas genre field beforehand, way have search 1 variant:

select t1.*, t2.content genres tvshows t1 left join tvshows_content t2 on t2.tvshows_id = t1.id , t2.type = 'genres' concat(',',t2.content,',') "%,sci-fi-fantasy,%" 

but here can use binary operator if need umlaut, special chars, etc. , can't match encodings:

select t1.*, t2.content genres tvshows t1 left join tvshows_content t2 on t2.tvshows_id = t1.id , t2.type = 'genres' binary concat(',',t2.content,',') "%,sci-fi-fantasy,%" 

hope helps!