Dynamic Query

[qball]

I have a form that collects several values into an array[]. How do I create a dynamic query based on the values of the array[]? For example, say the values are company ids and match the company_id in table companies. I loop through the array[] as follows:

while($x = each($assigned_to)) 
        { 
        print "$x[0] => $x[1]<BR>"; 
        }

Let's say the basic query is as follows:

$sql = <<END_SQL;

select 
   comp_name
from
   companies
where 
   company_id='10'

END_SQL

My array[], or as indicated in the while loop $assigned_to values are '10','15','20'. I now want the query to look something like the following:

$sql = <<END_SQL;

select 
   comp_name
from
   companies
where 
   company_id='10'
and
   company_id='15'
and
   company_id='20'

END_SQL

Any thoughts on a way to approach this?

qball~"I have node idea?!"

[chromatic]

Hmm, there was a similar question in generating various sql arguments dynamically, and on crack recently. Would either of those solutions work?

[blakem]

Are you asking about the SQL or the perl? I think the SQL you are looking for would use 'OR' instead of 'AND':

$sql = <<END_SQL;

select 
   comp_name
from
   companies
where 
   company_id='10'
or
   company_id='15'
or
   company_id='20'

END_SQL

is that what you are looking for???

-Blake

[tachyon]

Hi I don't understand your loop syntax but assuming you have your list in @array as shown this works fine:

my @array = (10,15,20);
my $where_list = '';

foreach my $index (0..$#array) {
    $where_list .= ($index) ? "and\n" : "where\n";
    $where_list .= "   company_id='$array[$index]'\n";
}

$sql = <<END_SQL;

select 
   comp_name
from
   companies
$where_list

END_SQL

print $sql;

We just loop over the array assigning the index to $index. We build up out $where_list. When $index == 0 we add 'where' otherwise we add 'and'. We then add the company_id bit. Finally we just interpolate the $where_list into your $sql herepage

Hope this helps.

BTW Do you really mean 'and' or should it be 'or'?

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[VSarkiss]

tachyon has developed an excellent sample, and chromatic has pointed you in the direction of improving it.

Combining the two gives both simple and effective code. What you're looking for is an in clause in your SQL. It meshes very nicely with join in Perl:

my @company_ids = qw(10 15 20);    # for example
my $where = 'where company_id in (' . join(',', @company_ids) . ')';

# insert the where clause at the bottom of the sql
$sql = END_SQL;
select comp_name
from   companies
$where
END_SQL

I think it's a little clearer when it's all combined, but that's more a matter of taste:

$sql = <<END_OF_SQL;
    select comp_name
    from   companies
    where  company_id in ( @{[join(',', @company_ids)]} )
END_OF_SQL

HTH