Simple MySQL SELECT Query, Help Needed.

TitanKing

Thank you for taking a look at my thread...

I need to do the following...

Say I have a table like this, I would like to get the field_id where the number 22 exists in the number column...

->
+------------+------------------+
|  field_id   |  numbers        |
+------------+------------------+
| 1              |  12, 19, 22     |
| 2              |   9, 22, 14      | 
| 3              |  11,6,88         |
| 4              |  1, 16, 22       |
+------------+------------------+
<-

Any help would be much appreciated. I thank you in advance !

DoctorWho

select * from yourTable where number LIKE '%22%'

TitanKing

Thank you very very much ! I appreciate it a whole lot ! Your help is very much appreciated !

That sound just like the function I needed ! :p

Lars_Berg

That's not a good solution. The query will return records with the number 225 for instance. In those cases where it will work it will be very slow as no indexes may be used.

The real problem is your database design. You should never store multiple values in a column. Store those values as separate records in a related table. Search for aritcles on normalization and relational database design.

Roger_Ramjet

Bear in mind that it is also going to find numbers 122, 226, 1221 etc.

My guess is that you either need to control the data entry VERY strictly to enable an exclusive search for LIKE '22,%' OR LIKE '%, 22,%' to only get 22 (even if it is the first entry).

Also guessing: this column related records in this table to those in another table in a Many to Many relationship: a common newbie solution. What you should have is a seperate table that relates the 2 tables.

field_id, number
1 , 22
1 , 12
2 , 9
3 , 11
2 , 22
2 , 14
1 , 19
3 , 6
3 , 88

TitanKing

Thank you very much, I was very much interested in the LIKE function but I understand what you guys are saying ! I am surely going to use your methods ! Thanks guys you all have been a great help !