Re: 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.

Comment on Re: Code factory Select or Download Code

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]
Re: Re: Re: Code factory by duct_tape (Hermit) on Jul 08, 2003 at 14:36 UTC
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]
Re: Code factory by tadman (Prior) on Jul 08, 2003 at 19:01 UTC
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]
Re: Re: Code factory by Notromda (Pilgrim) on Jul 09, 2003 at 01:55 UTC
Re: Re: Code factory by duct_tape (Hermit) on Aug 26, 2003 at 20:03 UTC


Don't ask to ask, just ask
	PerlMonks