SEO

Hi!

What I would like to do is this:
SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY

and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
"ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
ColumnName='BAR' OR ..." because I don't know those strings and how many
of them are in the set before.

--
Dan

On Fri, 18 Jan 2008 18:52:10 +0100, yataaa <gates531@gmail.com> wrote:
> What I would like to do is this:
> SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY

Depends on the database used. In MySQL, "WHERE fieldname IN
('foo','bar','foz')" is perfectly possible.

> and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
> "ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
> ColumnName='BAR' OR ..." because I don't know those strings and how many
> of them are in the set before.

? That's no reason:

$array = array('foo','bar','baz');
$query = 'SELECT fieldname FROM tablename';
if(!empty($array)) $query .= "WHERE fieldname = '".implode("' OR fieldname
= '",$array)."'";
--
Rik Wasmus

On Fri, 18 Jan 2008 19:08:42 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:

> On Fri, 18 Jan 2008 18:52:10 +0100, yataaa <gates531@gmail.com> wrote:
>> What I would like to do is this:
>> SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY
>
> Depends on the database used. In MySQL, "WHERE fieldname IN
> ('foo','bar','foz')" is perfectly possible.
>
>> and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
>> "ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
>> ColumnName='BAR' OR ..." because I don't know those strings and howmany
>> of them are in the set before.
>
> ? That's no reason:
>
> $array = array('foo','bar','baz');
> $query = 'SELECT fieldname FROM tablename';
> if(!empty($array)) $query .= "WHERE fieldname = '".implode("' OR
> fieldname = '",$array)."'";

Baaah, late, tired, need the weekend, thought I was reading this in
comp.lang.php, je m'excuse.
--
Rik Wasmus

On Fri, 18 Jan 2008 18:52:10 +0100, yataaa wrote:
> Hi!
>
> What I would like to do is this:
> SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY
>
> and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
> "ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
> ColumnName='BAR' OR ..." because I don't know those strings and how many
> of them are in the set before.

.... WHERE ColumnName IN ('val1', 'val2', 'val3' ... );

is the right way to construct that. There is a way to do it without
knowing the match values beforehand, but Paul yells when people talk
about it. You might be able to put your match terms into a temporary
table and join against it.

--
For their next act, they'll no doubt be buying a firewall running under NT,
which makes about as much sense as building a prison out of meringue.
-- Tanuki

Peter H. Coffin schrieb:

> ... WHERE ColumnName IN ('val1', 'val2', 'val3' ... );

Oh great, thats exactly what I wanted.

> is the right way to construct that. There is a way to do it without
> knowing the match values beforehand, but Paul yells when people talk
> about it. You might be able to put your match terms into a temporary
> table and join against it.

Yes, I thought about that, too. But I couldn't figure out the way to do
that.. Is that kind of "unclean" mySQL, or why does he yell?

--
Dan

yataaa wrote:
> Hi!
>
> What I would like to do is this:
> SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY
>
> and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
> "ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
> ColumnName='BAR' OR ..." because I don't know those strings and how
> many of them are in the set before.

Where will your set come from in the first place? How will you construct
your array of values? If you can construct them in this sort of array/set
format, why could you not construct them in the ColumnName='foo' OR
ColumnName='BAR' OR ..., just as easily?

Peter H. Coffin wrote:
> On Fri, 18 Jan 2008 18:52:10 +0100, yataaa wrote:
>> Hi!
>>
>> What I would like to do is this:
>> SELECT * FROM 'tableName' WHERE ColumnName is in ARRAY
>>
>> and ARRAY has the form of a set or array, like {"foo", "bar", "raz",
>> "ma", "taz"}. I can't do it like "...WHERE ColumnName='foo' OR
>> ColumnName='BAR' OR ..." because I don't know those strings and how
>> many of them are in the set before.
>
> ... WHERE ColumnName IN ('val1', 'val2', 'val3' ... );
>
> is the right way to construct that. There is a way to do it without
> knowing the match values beforehand, but Paul yells when people talk
> about it. You might be able to put your match terms into a temporary
> table and join against it.

I'm intrigued, how can one produce such a query without knowing what you
want to match against?

yataaa wrote:
> Peter H. Coffin schrieb:
>
>> ... WHERE ColumnName IN ('val1', 'val2', 'val3' ... );
>
> Oh great, thats exactly what I wanted.
>
>> is the right way to construct that. There is a way to do it without
>> knowing the match values beforehand, but Paul yells when people talk
>> about it. You might be able to put your match terms into a temporary
>> table and join against it.
>
> Yes, I thought about that, too. But I couldn't figure out the way to
> do that.. Is that kind of "unclean" mySQL, or why does he yell?

I don't know either.

See my other post in this tread, asking about where your values are coming
from and how you intend to construct your array.

On Sat, 19 Jan 2008 10:04:14 +0100, yataaa wrote:
> Peter H. Coffin schrieb:
>
>> ... WHERE ColumnName IN ('val1', 'val2', 'val3' ... );
>
> Oh great, thats exactly what I wanted.
>
>> is the right way to construct that. There is a way to do it without
>> knowing the match values beforehand, but Paul yells when people talk
>> about it. You might be able to put your match terms into a temporary
>> table and join against it.
>
> Yes, I thought about that, too. But I couldn't figure out the way to do
> that.. Is that kind of "unclean" mySQL, or why does he yell?

There's a couple of reasons to avoid it: it's not standard SQL so it
won't port to any other DBMS. It's overloading a specific task to do
something else, which can make the application fragile. But probably the
more important bit is that it encourages bad design by enabling multiple
values inside a given column-row to be usable. The bit above requires
you to know, at the time that you're writing the query, what values
you're searching for, and even if you're writing the query at runtime in
an application language (like PHP or C), you can at least capture the
query as run and SEE all the conditions that are being used to select
your data set.

--
94. When arresting prisoners, my guards will not allow them to stop and grab a
useless trinket of purely sentimental value.
--Peter Anspach's list of things to do as an Evil Overlord

Paul Lautman wrote:

> Where will your set come from in the first place? How will you construct
> your array of values? If you can construct them in this sort of array/set
> format, why could you not construct them in the ColumnName='foo' OR
> ColumnName='BAR' OR ..., just as easily?

You're right, I could do it that way, I just wanted to know if there is
an easy and quick way to use the data of an array of strings that comes
from java. But the fact that "IN ('foo', 'bar', ...)" works is enough
and really helps me, thanks to you all! =)


--
Dan