qball has asked for the wisdom of the Perl Monks concerning the following question:

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?!"

Replies are listed 'Best First'.
Re: Dynamic Query
by chromatic (Archbishop) on Jul 10, 2001 at 22:29 UTC
Re: Dynamic Query
by blakem (Monsignor) on Jul 10, 2001 at 22:32 UTC
    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

Re: Dynamic Query
by tachyon (Chancellor) on Jul 10, 2001 at 22:36 UTC

    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

Re: Dynamic Query
by VSarkiss (Monsignor) on Jul 10, 2001 at 23:45 UTC
    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