multiple id's?

[askeli]

Hi all, just a quick question, is it possible to select an id from a field containing more than one id number?

example:
select * from table where id=3

id field contains

1 blue
2,3 green
3 orange
4 yellow

this will select Orange but not Green, is it possible to select green aswell? and id=2 will still select Green?

many thanks

[Sanenet]

If id is a list, then use the IN selector.

SELECT * FROM whatever WHERE id IN (2,3)

[askeli]

Thanks, i tried it without luck

ill try and explain better
i have a table with id field
1
2
3
etc
but i have a few rows that need to have 2 or 3 ids for example
1
2
3,34,65
4
i am selecting with where id=$id and need an id of 3 to select that row as well as an id of 34 etc

i tried IN ($id,$id) but this found nothing, im probably doing it wrong somewhere, still learning thank you for your patience.

[Sanenet]

So... if ID is 3, then it should match rows..

1,3,5
3
3,5,6
1,34,5
30,6,9

Right? So use the wildcard % :
SELECT * FROM bananas WHERE id = '%3%'

% means "match ANYTHING", so it will match 3, 23, 32, 223,232,a3a, etc.

You can also use *, which means "Match ONE OF".

Exact wildcards may change from DB to DB, so if you get a strange error double check to make sure it works on your ODBC.

[askeli]

nearly

1,3,5 yes
3 yes
3,5,6 yes
1,34,5 NO
30,6,9 No

i tried the %xx% but that give the wrong results

id=3 needs to match 3, not with 3 in it.

Thanks for your help,

[askeli]

Got the problem sorted with a bit of trial and error

solution:

WHERE id RLIKE '[[:<:]]".$id."[[:>:]]'

Thanks