Mysql select with find_in_set with two stringlist

Question

I got in my table this 2 rows:

1, 'Halo: Reach', 2010, ''fps','sci-fi'', '"The best game of the year".', 'Microsoft', 'Bungie', 'XBOX 360', 9.5, 'http://wuwb.com/wp/wp-content/uploads/2009/06/halo_reach.thumbnail.jpg', '', ''

2, 'FIFA 11', 2010, 'sport,soccer', '"The best soccer game ever"', 'EA', 'EA', 'PC, XBOX 360, PS3', 10, 'http://wuwb.com/wp/wp-content/uploads/2009/06/halo_reach.thumbnail.jpg', '', ''

And in my php script I got:

$Genres = "sci-fi,sport,soccer"; // After using the sumbit button

I need to make a select query that order by genres, so FIFA 11 would be first and halo:reach second. So I tried to use find_in_set but it not working with two string list.

The only option I think about was to do for each genre IN clause and order by some fields and making limit, but I think its really bad way to do this.

THX, BBLN.

Answer 1

IN clause is the way to go. Just prepare the genres array by quoting/escaping it in PHP and then implode it with comma delimiter.

SELECT * FROM tbl_games WHERE genre IN ('sci-fi', 'sport'...) ORDER BY genre

Answer 2

I found better way :D

I created a new table (gamesgenres), so for every game I got some rows there.

SELECT count(games.id) as relevance, GROUP_CONCAT(gamesgenres.genre) as genres, games.* FROM games, gamesgenres WHERE ('sci-fi' IN (gamesgenres.genre) OR 'soccer' IN (gamesgenres.genre) OR 'fps' IN (gamesgenres.genre)) AND gamesgenres.game = games.genres group by games.id ORDER BY relevance DESC;