Composing a SQL statement

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

Hi all,

I have a perl CGI form like thus:

X | Call No  | Call No textfield
X | Problem  | Problem textfield

The X's represent checkboxes.

In the backend, I have two arrays:

@fields: checked fields
@entries: text fields with entries.

my code for generating the SQL is below. My problem
occurs when one enters text into more than one text
field. For example, say I check "Call No" and "Problem"
and enter "111" and "seating" into their respective
text fields. The SQL becomes:

SELECT call_no, problem FROM HELP WHERE 
  ( call_no LIKE 111 problem LIKE seating )  

There needs to be an "AND" in the WHERE clause. The SQL
should read:

SELECT call_no, problem FROM HELP WHERE 
  ( call_no LIKE 111 AND problem LIKE seating )  


I'll be stuffed if I know how to do it.

Can the monks help me???

Thanks,

    Stacy.


***********


  $nfields = @fields;
  
  foreach $i (@entries) {
    if ($i ne '') {
      push @full_entries, $i;
    }
  }
    
  $sql = sprintf "SELECT %s FROM HELP WHERE (", join(", ", @fields);
  
  for ($j = 0; $j < $nfields; $j++) {
    if ($full_entries[$j] ne '') {
      $sql .= sprintf " %s LIKE %s ", $fields[$j], $full_entries[$j];
    }
  }
  
  $sql .= ")";
[download]

Comment on Composing a SQL statement Download Code

Replies are listed 'Best First'.
Re: Composing a SQL statement by snowcrash (Friar) on Apr 04, 2001 at 10:10 UTC
just join your "foo like bar" strings with " AND ". some other points i noticed: get rid of variables like $nfields, use @fields in scalar context or $#fields to increase readability you should probably quote strings in your select statements like "foo like 'bar'" don't use sprintf when you don't need it: eg. $sql .= "$foo like '$bar'"; cheers snowcrash //////	[reply]
(ar0n) Re: Composing a SQL statement by ar0n (Priest) on Apr 04, 2001 at 17:09 UTC
This may be slightly cleaner: `my @where; my @full_entries = grep { length } @entries; for (0..$#fields) { push @where, $fields[$_], $full_entries[$_] if defined($fields[$_] && $full_entries[$_]); } my $sql = "SELECT " . join(", ", ('?') x @fields) . " FROM HELP WHERE +(" . join(" AND ", ("? LIKE ?") x @fields) . ")"; $dbh->prepare($sql); $dbh->execute(@fields, @where);` [download] [ar0n]	[reply] [d/l]
Re: Composing a SQL statement by busunsl (Vicar) on Apr 04, 2001 at 09:44 UTC
Just change the " `%s LIKE %s`" to " `%s LIKE %s AND` ", this will insert the missing 'AND'. You have to remove the trailing 'AND' though, just like this (before you append the ')'): `$sql =~ s/AND$//;` [download]	[reply] [d/l] [select]