where field in() problem

[redstarfcs]

I need to select some information that are in a database..I have category field that contain the numbers of some categories, this field is a text field

I need to select category 5

My database structure is:

id---name-------category
1 Test 5
2 Good 12 15 7 5
3 Test 2 123 15 10
4 Test 3 33 424
5 Test New
6 Better 12


I use the following query:

select id from db_table where category in('','5')

MySQL engine select only id=1, why it doesn't select also ids: 2 and 5

[dr.p]

select id from db_table where category in('','5')

This code will match against the entire`category` field, not just part of it. I assume from that example data that your `category` fields contains one or more category numbers separated by spaces.

I would use something like the following:


Select id From db_table Where category REGEXP "(^| )(5)( |$)"

If you want to select categories 5, 2 and 3, you can then do:


Select id From db_table Where category REGEXP "(^| )(5|2|3)( |$)"

I suggest you read carefully the SELECT function details in the MySQL manual.