Simple MYSQL question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Simple MYSQL question

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Thanks feyd

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply