CODEWITHSUNDEEP
perldbi
cajwine
cajwine

Reputation: 3160

Get rows from database for multiple search values

Need perform a database query for multiple values. So have something like:

my(@ids) = get_ids_from_the_webform();

and need get all rows where the id is any of values from the @ids.

It is must doing with multiple queries, like:

my @result; 
for my $id (@ids) {
    my $sth = prepare(".....");
    $sth->execute($id);
    my $rows = $sth->rows();
    @result = remove_duplicates(\@result, $rows);
}

Or is here some easier way?

Exists any perl module what has implemented some easy "logic based queries" for easy searching? (e.g. like the above "get rows where their ID is val1 OR val2 OR val3" and similar, or need construct the SQL statement myself manually?

Upvotes: 0

Views: 170

Answers (1)

clt60
clt60

Reputation: 63922

You sure don't want sequentally query the DB, but want craft one SQL with all WHERE clausules.

Maybe someone will suggest more "advanced" solution, but for the start you should check the SQL::Abstract module, what can generate for you some complex queries.

From the docs:

my %where = (
   requestor => 'inna',
   worker => ['nwiger', 'rcwe', 'sfz'],
   status => { '!=', 'completed' }
);

my($stmt, @bind) = $sql->select('tickets', '*', \%where);

The above would give you something like this:

$stmt = "SELECT * FROM tickets WHERE
            ( requestor = ? ) AND ( status != ? )
            AND ( worker = ? OR worker = ? OR worker = ? )";
@bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');

So, you don't need manually craft the SQL, but need craft one perl hash with the query, what is of course much eaasier.

Upvotes: 1

Related Questions