Help with trick MySQL select problem

I have a database table, which has field that could contain a single integer
or a list of comma separated integers.

Is it possible to match each row of that field against an array of integers
and return those rows where any of the integers in that field are in my
array?

For example suppose I did the following:

arInts = array(2,4,6,8,10);

What I want to do is return all rows from my table where my field contains a
single integer and it is in my array, or where my field contains a list of
integers and any of those integers is in my array?

Is this possible and if so how do I do it?

Thanks

Hamilton

re: Help with trick MySQL select problem

Funnyweb (r_rachnid@eggstra.co.au) decided we needed to hear...[color=blue]
> I have a database table, which has field that could contain a single integer
> or a list of comma separated integers.
>
> Is it possible to match each row of that field against an array of integers
> and return those rows where any of the integers in that field are in my
> array?
>
> For example suppose I did the following:
>
> arInts = array(2,4,6,8,10);
>
> What I want to do is return all rows from my table where my field contains a
> single integer and it is in my array, or where my field contains a list of
> integers and any of those integers is in my array?
>
> Is this possible and if so how do I do it?
>
> Thanks
>
> Hamilton[/color]

Closest you could get in pure SQL would probably be...

select * from table where
field_in_set('2', arraycol) > 0 or
field_in_set('4', arraycol) > 0 or
field_in_set('6', arraycol) > 0 or
field_in_set('8', arraycol) > 0 or
field_in_set('10', arraycol) > 0

arraycol is your column with the comma separated ints, and '2',
'4' etc are the values from the array. You would have to generate
the query via a loop over your array, but of course if the array
is large, then the query will also be large!

How big is the table? If it is small, how likely is it to grow?
I ask because you might be better off selecting all rows, then
processing the result set in a loop, discarding rows not matching
your array - use a combination of explode and array_intersect.

--
Dave <dave@REMOVEbundook.com>
(Remove REMOVE for email address)

re: Help with trick MySQL select problem

Funnyweb wrote:[color=blue]
> I have a database table, which has field that could contain a single integer
> or a list of comma separated integers.
>
> Is it possible to match each row of that field against an array of integers
> and return those rows where any of the integers in that field are in my
> array?
>
> For example suppose I did the following:
>
> arInts = array(2,4,6,8,10);
>
> What I want to do is return all rows from my table where my field contains a
> single integer and it is in my array, or where my field contains a list of
> integers and any of those integers is in my array?
>
> Is this possible and if so how do I do it?
>
> Thanks
>
> Hamilton
>
>
>[/color]


Hamilton,

Try reading up on database normalization. Then create a link table with
two columns. First is the primary key of the original table, second is
a *single* integer. Multiple rows can have the same first column but
the second column would chance.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

re: Help with trick MySQL select problem

Hamilton is right.

If you have tables like this:

users
int userid
varchar name
bit active

books
int bookid
varchar name

Instead of having another field in users to hold all bookids that they
have read, do this:

read_book
int userid
int bookid

With 1 row for each book each user read. Then you can select all rows
from read_book where userid=x and get the book info for each row.