Page 1 of 1

Simple MYSQL question

Posted: Sat Sep 23, 2006 10:27 am
by lazersam
Hi

I'm sure this is easy to answer...

I have a varchar field in mysql that contains some numbers e.g.: 4, 3, 1, 2 seperated by commas. I want to search the data base and select only the rows that contain one number from that field. For example, select all the rows from table where field contains number 4. Anyone know the syntax for that? How can mysql scan the field and see if number 4 is in it?

Larry.

Posted: Sat Sep 23, 2006 10:38 am
by feyd
There's a function called FIND_IN_SET(), however it may be better to break this field outin some fashion such that it only has to store a single number. Not only is this generally considered better design, but also allows for more storage capacity.

Thanks feyd

Posted: Sat Sep 23, 2006 11:27 am
by lazersam
Thanks feyd,

I think you are right. I temporally solved the problem using LIKE and placing commas in the field. But on reflection, what's another field? Cheers.

Larry.

Posted: Sat Sep 23, 2006 11:34 am
by feyd
Normally, this sort of storage is broken into at least two tables. Table A contains fields which are common to multiple records from table B. B will then reference a record in A. This is often referred to as a one-to-many relationship between A and B, respectively.