searching content of mysql fields

Question

I have a table setup the following way

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventid` int(11) NOT NULL,
  `invites` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)

In that table i have rows which look like this

1, 127, '32215, 55648, 89887, 55555, 66666, 33333'
2, 168, '44444, 33333, 121212, 0540541'

How would i search the contnts of the invites field for a match for a particular number such as if i wanted to search for 33333, it would return both rows, but if i searched for 44444 it would return only the second?

Answer 1

I believe you can use find_in_set(), so something like:

select * from tablename where find_in_set('44444', invites);

This isn't very good database design though.

Answer 2

As a side note, allow me to note that your database schema - storing a set of values in a plain-text field - is improper. If you want to query on a one-to-many relationship like this, you ought to have another table that defines the relationship between a single event and a single invitation, like this:

eventid invitationid
1 15
1 16
1 17
2 18
3 19
3 20

Then you'll be able to do standard SQL queries that will actually be performant and will make sense.