DISCLAIMER: This works for Oracle.... anything else and you might have to change the grep statement below or maybe more. I would rather use bind variables('?') for portability, but they simply don't work for this.
I'm getting my values for my query with the following field generated by CGI.pm:
<select name="qPlatform" size="6" multiple> <option selected value="0">All</option> <option value="1NT">WinNT</option> <option value="22K">Win2K</option> <option value="3Sun">Solaris</option> <option value="4HP">HP-UX</option> <option value="5IBM">AIX</option> </select>
This is constructed along with several other multi-select boxes using a simple subroutine that uses a lookup table similar to the following for each multi-select box:
SQL> select * from PLATFORMTAB;
ID NAME ----- ---------- 0 Choose 1NT WinNT 22K Win2K 3Sun Solaris 4HP HP-UX 5IBM AIX
Since I load this into a hash later, I have chosen to force the sort order by prepending numbers to my keys. This approach makes it easy to update the form and all related queries with a simple manual sql statement updating or inserting rows into the lookup table; I don't want to have to edit the CGI when I make such a change. In other cases where I may need to update often I use numbers like 10, 20, 30, and so on in the lookup table. I load this lookup table into a hash, both for the purpose of creating the form element and for the purpose of constructing my query:
%Platform = @{$dbh->selectcol_arrayref("SELECT ID,NAME FROM PLATFORMTAB", {Columns=>[1,2]})};
My problem was that the form would return several arrays of arbitrary size, one for each multi-select box. I won't get into the form part here; ask for it if you want it... but the following line is where I read in the array from the form, making sure the array contains a '0' if nothing else:
@qPlatform = ($q->param("qPlatform") ? $q->param("qPlatform") : "0");
I then call the subroutine that constructs a query string, using references to the above hash and array:
$platformValueString = buildValueStrings([EMAIL PROTECTED], \%Platform);
So then my subroutine looks like this:
sub buildValueStrings {
my ($a) = shift;
my ($h) = shift;
for (@test = @$a) {
(($_ eq "0") ? (@$a = keys %$h) : "");
}
grep($_ = "'$_'", @$a);
return join(",", @$a);
}Then, after doing this for all of my query strings, I construct my SQL statement(I've added linefeeds in this email for readability; in my CGI it's one line):
$sql = "SELECT ID,OWNER,BASEVERSION,PLATFORM,DATABASE,DBCHARSET,MIGAPPSERVER FROM MASTERTABLE WHERE ACTIVE LIKE $qActive AND CUSTOMER IN ($customerValueString) AND MILESTONE IN ($milestoneValueString) AND OWNER IN ($ownerValueString) AND PLATFORM IN ($platformValueString) AND DATABASE IN ($databaseValueString) AND DBCHARSET IN ($charsetValueString) AND MIGAPPSERVER IN ($appServerValueString) AND ERPCSV IN ($csvValueString) AND ERPORACLE IN ($oracleValueString) AND ERPPSOFT IN ($peoplesoftValueString) AND ERPSAP IN ($sapValueString) ORDER by ID";
After that, it's a simple matter of doing the following:
$sthMaster = $dbh->prepare_cached($sql); $sthMaster->execute();
Maybe this will help someone, maybe not.... I have had some good help from this list though, and I wanted to give back. :)
Enjoy!
Dave A.
Peter J. Holzer wrote:
On 2003-09-23 20:30:10 -0700, Dave Anderson wrote:
I am having a heck of a time doing a "SELECT FROM TAB WHERE COL1='val' and COL2 IN ('1','2')" where the '1','2' is coming from an array that is returned from a multi-select box built with CGI $q->scrolling_list with "multiple" set to "true". I see that bind_param_array can take an array as a bind value, but I want to have several arrays of varying sizes in my query. bind_param_array will not do this, nor will it work for a SELECT.
bind_param_array is for use with execute_array. It will execute the same query for each value in the array.
$sthHistory = $dbh->prepare_cached('SELECT STATUS,COMMENTS,LABEL FROM STATUSCURRENTTAB WHERE ID in ?');
You cannot expand a single "?" to a list of values. You have to construct a query string with the appropriate number of placeholders first, then you can simply call execute:
$query = 'SELECT STATUS,COMMENTS,LABEL FROM STATUSCURRENTTAB WHERE ID in (' . join(',', map { '?' } @array) . ')'; $sthHistory = $dbh->prepare_cached($query); $sthHistory->execute(@array);
hp
