Finding value in a comma-separated text field in MySQL?

I've got a database of games with a genre field that has unique ids in it that are separated by commas. It's a text field. For example: HALO 2 - cat_genre => '1开发者_JAVA百科,2' (Action,Sci-Fi)

I'm trying to make a function that calculates the total number of games in that genre. So it's matching 1 value to multiple values separated by commas in the db.

I was using SELECT * FROM gh_game WHERE cat_genre IN (1) which would find Action Games.

I'm trying to work this out, I thought I had nailed it once before but I just can't figure it out.

You need to create a many to many relation. like so

CREATE TABLE gameGenreTable ( id int NOT NULL PRIMARY KEY, genreID, gameID)

EDIT: if you're using InnoDB you can also create foreign keys on genreID and gameID.. I would add a UNIQUE key on genreID, gameID

then you can do a query like this

SELECT genreID,count(genreID) as count from gameGenreTable GROUP BY genreID;

-- and join in your other table to get the genre name (or just use the ID).