#!/usr/bin/perl -Tw
use strict;

my @select_array = ('column1', 'column2', 'column3');
my @where_array = ('something = 1', 'somethig2 > 30'); 

my $select_clause = join(', ',@select_array);
my $where_clause  = join(' and ',@where_array);
my $sql = "SELECT $select_clause FROM table WHERE $where_clause";

print "SQL = '$sql'\n";
[download]

-Blake

hoping someone can help me with this. I have to create SQL statements from 2 arrays. The first array holds the names of the columns I wish to select from the second holds the conditions.

Have a look at SQL::Generator.

But maybe you don't want to generate SQL yourself (I don't think you do like SQL, because you would probably not be trying this if you did), use DBIx::Abstract.

- Yes, I reinvent wheels.
- Spam: Visit eurotraQ.

my $query = "select "      . do {local $" = ", ";    "@select_array"} 
+.
            " from table " .
            "where "       . do {local $" = " and "; "@where_array"};
[download]

Abigail

push(@where_array, 'item1 = ?');
push(@params, $item1);
push(@where_array, 'item2 = ?');
push(@params, $item2);
push(@where_array, 'item3 = ?');
push(@params, $item3);

my $select = join(', ',@select_array);
my $where  = join(' and ',@where_array);

my $script = "SELECT $select FROM table WHERE $where";
. . . 
$sql->execute(@params);
[download]

Hope this helps,
MrCromeDome

the first line of code is borrowed directly from chromatic's DBI is OK article, and has been among the most helpful lines of code I've found in getting my SQL queries to behave the way I want them to:

my $values = join(', ', map { $dbh->quote($_) } @formdata{@fields});
[download]

uses a hash slice and the map function to make sure that whatever values are associated with the keys listed in @fields are quoted in such a way as to be properly understood by the SQL engine. Of similar use would be:

my $keys = join(', ' map {$dbh->quote_identifier($_) } @fields);
[download]

which would correctly quote your field names as appropriate for identifiers in an SQL statement.

You might also be able to use placeholders to speed the process up. If you are in a situation where the identifiers (left hand of the equal sign in your SQL query) are static, and only the values (right hand side of the equal sign in the query) are changing, you can use place holders and
$sth = $dbh->prepare_cached($sql)
passing the values as parameters when you execute the $sth. You can read more about this process in the article above, or at this node or in the documentation.

hope this added something,
--au

update: changed some things to improve formatting, clarity, and grammar... bah.

If you're using static "variables" rather than placeholders, your DB engine will have to re-parse your statement (with considerable overhead).

I guess you wouldnt really have to worry too much if you've only got a low load application.

This only applies to certain DBMS's. Oracle definitely pre-parses. MySQL definitely does not, nor does Postgres. Sybase probably does. However ,the other reason to use placeholders is to protect yourself from attacks. If you just blindly go using user-provided input, you better make sure to call $dbh->quote on every piece. Or you could use placeholders and let the DBD::* driver do the work for you. Plus if you ever switch from MySQL to Oracle, your SQL will immediately have a performance advantage over not using placeholders.

<code> # Fetch each row and print it
while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {
print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n";
} <code>

Is there any sort of a funky loop or something I could implement?

The code I am using requires foreknowledge of the amount of results to be outtputted.

There's a bunch of ways you could do this, but they mostly depend on how you'll be outputting your data. If you're just listing what you got out of the database you could just do this

while(my $rr = $sth->fetchrow_hashref()) {
    print "$_: $rr->{$_}\n" for keys %$rr;
}
[download]

Or if you're not too concerned about the field names you could just print out all the values like so

while(my @rows = $sth->fetchrow_array()) {
    print "Values: @rows\n";
}
[download]

Or if you want a list of enumerated the values

while(my @rows = $sth->fetchrow_array()) {
    print "$_: $rows\n" for @rows;
}
[download]

So as you can see you have plenty of options. Personally I'd recommend using the fetchrow_hashref() method as the brain maps better to names than sequences. See the DBI docs for more info on the methods mentioned above.
HTH

_________
broquaint

If I've understood your question, you just need to use $sth->rows, which returns the number of results from your query.

BCE
--Your punctuation skills are insufficient!

Regarding this point:
Having retrieved the values I now need to display the results. The code I am using requires foreknowledge of the amount of results to be outtputted. However I don't know how many are to be outputted until run time.

Is it the number of rows you need to anticipate? or the number of columns? or both? If the listing follows from the kind of query you asked about earlier, you already have the array of columns that you're selecting, so that can be used again for printing.

To know the number of rows in advance of printing them out, you could try one of the "fetchall" or "selectall" DBI methods; these return an array ref which you can then check with "scalar( @$array_ref )" to get the number of rows returned. Or, if you're worried that this might put too much overhead/memory-load on your system if the return on the query is really big, and you really want to know how many rows you're going to get before you print them, do a "pre-query" that starts with "select count(some_field) where $where_clause"... But in my own experience (on solaris/oracle) there seems to be less time/trouble in using a "fetchall" than in doing a big query two times (though I have never tested this rigorously).