in reply to Code factory

Here's something i've been using for quite a while: 
sub make_select_query {
    my ($table, $select_fields, $conditions) = @_;

    my $query = "SELECT ";

    if ( defined $select_fields ) {
        if ( ref($select_array) eq "ARRAY" ) {
            $query .= join ",", @$select_array;
        }
        else {
            $query .= $select_fields;
        }
    }
    else {
        $query .= "\*";
    }

    $query .= " FROM " . $table;

    # only put the where and conditions in the query string
    # if conditions are present
    if (keys %$conditions) {
        $query .=  " WHERE ";

        foreach my $key (keys %$conditions) {
            push @conds, $key . "=\'" . $conditions->{$key} . "\'";
        }
    }
    $query .= join " and ", @conds;

    return $query;
}

[download]
Of course this has a number of shortcomings, such as:
All conditions use equality. This could be worked around by having a condition hash like this instead: 
    ...
    $condtions = {
        "age"   =>   " >= 21 ",
        "sex"   =>   " = female ",
    };

[download]

But this is leading down the path that the more complicated your need, the less likely there will be a general solution that does exactly what you need.

Replies are listed 'Best First'.
Re^2: Code factory
by Coruscate (Sexton) on Jul 08, 2003 at 00:36 UTC

    My select sub (part of Coruscate::DB (not on cpan) package): 

    sub select {
 my ($self, $want, $query, @args) = @_;
 die "Not connected to a database!"
  unless defined $self->{'dbh'};

 my $results;

 if (ref $want eq "HASH") {
  my $sth = $self->{'dbh'}->prepare("SELECT $query");
  $sth->execute(@args);
  while (my $row = $sth->fetchrow_hashref()) {
   push @{$results}, {
    map { $_, $row->{$_} } keys %{$row}
   };
  }
 } elsif (ref $want eq "ARRAY") {
  $results = $self->{'dbh'}->selectall_arrayref("SELECT $query", {}, @
+args);
  undef $results if $#{$results} < 0;
 }

 return $results;
}

    [download]

    I absolutely love my little module. Extremely simple interface that can do everything. Sweet :)

    If the above content is missing any vital points or you feel that any of the information is misleading, incorrect or irrelevant, please feel free to downvote the post. At the same time, please reply to this node or /msg me to inform me as to what is wrong with the post, so that I may update the node to the best of my ability.

[reply]
[d/l]
      Instead of doing this: 
         push @{$results}, {
    map { $_, $row->{$_} } keys %{$row}
   };

      [download]
      Wouldn't it be better/faster to just do this: 
         push @{$results}, $row;

      [download]
      You already have a hash reference, why recreate it?
[reply]
[d/l]
[select]
        If I recall correctly, DBI recycles the hash reference with each iteration. What you end up with is an ARRAY of exactly the same HASH data. You should duplicate it, as was done with that first bit of code, or better yet:
        push(@$results, { %$row });
[reply]
[d/l]

In Section Seekers of Perl Wisdom