Dynamic Where Clause using DBI

[ssmith001]

I've got an interesting problem that I'm trying to resolve in Perl and I need some advice. Perhaps someone has run into this before. I'll do my best to keep this simple. I have a form my user fills out. This form is basically nothing more than "where clause" criteria from which I need to build a SQL statement. The problem I have is that there are 6 option buttons that can check. I want to avoid having a SQL statement for every and all possible combinations of each of these 6 options. Do you see where I'm going with this? That would be UGLY!

Is there a way to dynamically build the where clause based on what option buttons they have chosen? I have a bunch of variables that I set based on what option buttons that have selected.

Here's an example. If they have chosen promo_code and stock_class options, run the sql below minus the supp_code and min_name lines. If they have chosen promo_code and min_name options, run it without stock_class and supp_code. I think you get the picture.

Select item, loc, abccode
from sku
where loc = ?
  and item = ?
  and promo_code in (blah, blah, blah)
  and stock_class in (blah, blah, blah)
  and supp_code in (blah, blah, blah)
  and min_name in (blah, blah, blah)

 

[lcs01]

You did not say what if you have neither stock_class nor min_name options. I suppose you would want them all. Then here is one quick example:

my ($qry, $subqry);
$qry = "Select item, loc, abccode from sku where loc = ? and item = ? and promo_code in (blah, blah, blah)";

if($promo_code) {
  if($stock_class) {
   $subqry = " and stock_class in (blah, blah, blah)";
  }
  elsif($min_name) {
    $subqry = " and min_name in (blah, blah, blah)";
  }
  else {
    $subqry = " and stock_class in (blah, blah, blah) and supp_code in (blah, blah, blah) and min_name in (blah, blah, blah)";
  }
}

$qry .= $subqry;

 

[ssmith001]

Sorry, forgot to mention that they must choose at least one.

Thanks, I'll play around with your suggestion for a while.

 

[ssmith001]

Perhaps I should not have used promo_code in both of my examples. The only part that is the same every time I run the query would be this:

	$qry = "SELECT a.loc
		           , a.item
     					 , a.p_abc
					  FROM stsc.sku a
   						 , stsc.loc b
   						 , stsc.item c
        	 WHERE a.loc 	= '$loc'
        	   AND a.p_abc IN ($abc)";

It looks to me like there's no way to avoid a rather long messy IF statement. Am I correct?

 

[ishnid]

Use a hash as a dispatch table? This is just a guide really:

my %table = (
  promo_code => ' AND promo_code IN (blah, blah, blah)',
  stock_class => ' AND stock_class IN (blah, blah, blah)',
  supp_code => ' AND supp_code IN (blah, blah, blah)',
  min_name => ' AND min_name IN (blah, blah, blah)'
);

my $qry = q!SELECT a.loc, a.item, a.p_abc FROM stsc.sku aa, stsc.loc b, stsc,item c WHERE a.loc = '$loc' AND a.p_abc IN ($abc)!;

for ( param( 'param_name' ) ) {
   $qry .= $table( $_ );
}

 

[ssmith001]

I'm not sure I follow how this is supposed to work. Can you explain?

 

[ishnid]

Basically, I'm using a hash rather than if/elsif/else statements.

I start with the standard part of the query that you're always going to use (what starts off in $qry). Then, depending on what is passed into your form (i.e. for every value passed into my imaginitively-titled "param-name" parameter), the appropriate extra WHERE clause is appended to $qry. By the end of it, your query has been constructed correctly (hopefully).

 

[ssmith001]

This is what I'm using to test this and it's not working correctly. I am getting a syntax error message on this line "$qry .= $table( $_ );"

my $param_name = "promo_code";

$myquery = get_items_for_cat1($param_name);

print $myquery;


sub get_items_for_cat1
{
  $param_name = shift;

   my %table = (
     promo_code => ' AND promo_code IN (blah, blah, blah)',
     stock_class => ' AND stock_class IN (blah, blah, blah)',
     supp_code => ' AND supp_code IN (blah, blah, blah)',
     min_name => ' AND min_name IN (blah, blah, blah)'
   );

   my $qry = q!SELECT a.loc, a.item, a.p_abc FROM stsc.sku aa, stsc.loc b, stsc,item c WHERE a.loc = '$loc' AND a.p_abc IN ($abc)!;

   for ( param( '$param_name' ) )
   {
      $qry .= $table( $_ );
   }

}

 

[ishnid]

Eep. My fault: change to:

$qry .= $table{ $_ };

That's what I get for replying in a hurry.

 

[ssmith001]

I'm closer. Now I get this:

defined subroutine &main:aram called at Untitled1 line 24.

 

[ssmith001]

I meant "undefined

 

[ishnid]

In my code, "param" relates to the CGI module's "param" function. You'll have to import it if you want to use it that way. Note that my code isn't supposed to work as-is, just to give you an idea of how to approach it.

 

[ssmith001]

After reading more about the CGI module, it became apparent that I've perhaps mislead you. The form I'm referring to is an Access form. They users enter their data into Access, press a button which then enters the data into an Oracle table, which is where I grab it from. It looks to me like the "param" function is made to grab data off web forms. Is this correct?

By the time I reach this part of my code, I know exactly which options the user has chosen, I just needed a way to build the query dynamically, based on what they chose. I have a series of flags that I set (promocodes_found, stock_classes_found, dist_channels_found, etc) and I need to add the criteria based on these flags.

 

[ishnid]

I was being presumptuous about it being a web script alright. Basically, all I was doing there was getting the flags that the user had passed in (via a web form in that case) and adding to the $qry string based on what the input was.

You could achieve the same thing with a hash. e.g. if a user selects a "promo_code" flag, you could do this (the important thing about the names of the flags is that they should match the relevant portion of SQL in the %table hash I mentioned above):

$flags{ promo_code } = 1;

Then, once you've put all the relevant flags into %flags:

for ( keys %flags ) {
   $qry .= $table{ $_ };
}

 

[ssmith001]

Ok Ishnid, I was following you right up until your last reply then I lost you. These are the flags that I have set based on what the user has selected. I lost you on the %flags hash comment.

$promocode_found
$stockclass_found
$distchannel_found
$dlrsuppcode_found
$altdlrsuppcode_found
$minname_found

 

[ishnid]

What I mean is that instead of using an individual variable to store each flag ($promocode_found, $stockclass_found, etc.), use a hash to store all the flags that have been supplied by the user (that's the %flags hash I mentioned above). Come to think of it, it's probably better to just use an array to store those flags.

The %table hash stores the extra piece of code that needs to be tacked onto the end of your SQL query for each flag that can be supplied by the user. Once you have the flags stored in the array, you can just loop through them and add on the relevant piece of SQL code.

 

[ssmith001]

Great, I think we are on the same page now. I virtually came to the same array conclusion yesterday. I have posted my code and the output below because I still have an issue (listed below)

my $promocode_found      = 1;
my $stockclass_found     = 1;
my $distchannel_found    = 1;
my $dlrsuppcode_found    = 1;
my $altdlrsuppcode_found = 1;
my $minname_found        = 1;

my @param_array = ();
my $promocode_param_name 	= 'promo_code';
my $stockclass_param_name 	= 'stock_class';
my $distchannel_param_name 	= 'dist_channel';
my $dlrsuppcode_param_name 	= 'dlr_supp_code';
my $altdlrsuppcode_param_name 	= 'alt_dlr_supp_code';
my $minname_param_name 		= 'min_name';

my @promocode_array = ('010','010A','002');
my $promocodes_for_query = "'" . join("','", @promocode_array) . "'" if ($promocode_found);

push @param_array, $promocode_param_name if ( $promocode_found );
push @param_array, $stockclass_param_name if ( $stockclass_found );
push @param_array, $distchannel_param_name if ( $distchannel_found );
push @param_array, $dlrsuppcode_param_name if ( $dlrsuppcode_found );
push @param_array, $altdlrsuppcode_param_name if ( $altdlrsuppcode_found );
push @param_array, $minname_param_name if ( $minname_found );

$myquery = get_items_for_cat1(@param_array, $promocodes_for_query);

print $myquery;

sub get_items_for_cat1
{
  print "\n";
  print "Parameter Count: ", scalar(@_), "\n";
  print "\n";

  @parameter_array = @_;

  #print "==> @parameter_array \n\n";

  print $_[0],"\n";
  print $_[1],"\n";
  print $_[2],"\n";
  print $_[3],"\n";
  print $_[4],"\n";
  print $_[5],"\n";
  print $_[6],"\n\n";

   my %table = (
     promo_code => '
      AND promo_code IN ($promo)',
     stock_class => '
      AND stock_class IN ($stkclass)',
     dist_channel => '
      AND dist_channel IN ($distchan)',
     dlr_supp_code => '
      AND dlr_supp_code IN ($dlrsupcode)',
     alt_dlr_supp_code => '
      AND alt_dlr_supp_code IN ($altdlrsupcode)',
     min_name => '
      AND min_name IN ($minname)'
   );

   my $qry = q!SELECT a.loc,
       a.item
     , a.p_abc
     FROM stsc.sku a
        , stsc.loc b
        , stsc,item c
    WHERE a.loc = '$loc'
      AND a.p_abc IN ($abc)!;

   foreach $param ( @parameter_array )
   {
      $qry .= $table{ $param };
   }

   $qry_end = "
    ORDER BY a.loc, a.item";

   $qry .= $qry_end;

   return $qry;

}

Here's the output. What I was expecting to see, or at least what I wanted to see was this: AND promo_code IN ('010','010A','002'). I was trying to feed the value of $promocodes_for_query.

Parameter Count: 7

promo_code
stock_class
dist_channel
dlr_supp_code
alt_dlr_supp_code
min_name
'010','010A','002'

SELECT a.loc,
       a.item
     , a.p_abc
     FROM stsc.sku a
        , stsc.loc b
        , stsc,item c
    WHERE a.loc = '$loc'
      AND a.p_abc IN ($abc)
      AND promo_code IN ($promo)
      AND stock_class IN ($stkclass)
      AND dist_channel IN ($distchan)
      AND dlr_supp_code IN ($dlrsupcode)
      AND alt_dlr_supp_code IN ($altdlrsupcode)
      AND min_name IN ($minname)
    ORDER BY a.loc, a.item

 

[ishnid]

I think you may still be missing the point of what I'm trying to say. In the sample code you've posted, you have this:

my $promocode_found      = 1;
my $stockclass_found     = 1;
my $distchannel_found    = 1;
my $dlrsuppcode_found    = 1;
my $altdlrsuppcode_found = 1;
my $minname_found        = 1;

Presumably, this isn't how you set these in the actual script itself. If you can post the code where you're actually setting those, that would help.

Also, in the SQL itself: is that the actual query you'll really be using (i.e. to use the $abc, $promo, $stkclass etc. variables, which I assume are defined somewhere else)? If so, you should probably use `=' instead of `IN'.

 

[ssmith001]

I have the flags set in this sample code so that I can simulate that the flags had been set prior to arriving at this section of the code so I could make sure that the correct parts of the where clause were being appended as expected.

As far as the SQL statement question, yes, that is the actual statement I want to run. The variables $promo, $stkclass, $abc can all either have a single element or multiple depending on what the user has chosen. On the form I refered to earlier, if they choose the Promo Code button, they can then enter 1 or more promo codes. I then need to take all of these, string them together and place them into the $promo variable so when the SQL statement runs it will look like " and promo_code in ('promo1','promo2','promo3'), etc.

 

[ishnid]

Without seeing the code that sets those variables, I can't really explain any better what I'm getting at.

By embedding user-supplied data directly into your SQL statements, you're also making yourself susceptible to SQL injection attacks. Have a read of this for ideas on how to avoid that by using placeholders. It's aimed at web programming but it's relevant to any application that takes data from a user and uses it to query a database.