CODEWITHSUNDEEP
phpmysqlsearchselect
Matt Reid
Matt Reid

Reputation: 235

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,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.

Upvotes: 1

Views: 660

Answers (1)

bren
bren

Reputation: 106

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).

Upvotes: 1

Related Questions