perlquestion
jamroll
The code should suffice (the following snippet is in a module i call "search.pm". i recognize the code is not "secured". That will come, later...so please, avoid telling me it's insecure - i already know that. i just need this to first work as described in the comments of the subroutine. i wrote this code...and confused the bejeepers outta myself lol
<code>
########################
sub sql_execute($$) {
#*
# runs an SQL statement on the supplied db.
# must be connected, and must disconnect to commit changes.
# a reference to an array of hash references will be
# returned unless there's only one item in the array, then
# we give a hash reference instead of a one element array
# reference (which would contain only one hash reference).
# three different rvals: scalar, array ref, or hash ref
# - dependent on # of results, or kind of sql statement (insert, create, update, select, etc)
#*
my ($dbh, $sql) = @_; # the dbh && the SQL statement
if (not sql_db_valid($dbh)) { # sql_db_valid is a VERY simple check - it just checks if $db is a hash ref or not)
return 0; # invalid dbh
}
my @arr;
if ($sql =~ /^insert|update|delete/i) {
my $rv = $dbh->do($sql);
return $rv; # returns whatever $rv->do($sql) returns
} else {
my $rv = $dbh->prepare($sql);
if ($rv) {
$rv->execute();
# now, grab all the results from the query, and dump them into an array as hash references to each "hit"
while (my $row = $rv->fetchrow_hashref) { push @arr, $row; }
# if the array has only one element, then, it's kinda pointless to return a ref to the array
# so instead, let's just return a hash reference.
if ($#arr eq 1) {
my $hashRef = $arr[0]; # this ought to be a hash reference, no?
return $hashRef; # a hash reference when there is only one array element
} else {
if (not @arr) {
return 0; # 0 on error
} else {
# and array reference when there is more than 1 element in the array
# each element is a reference to a hash.
my $arrayRef = \@arr;
return $arrayRef; # an array reference when the array is > 1
} # if (not @arr) ... else ...
} # if (@arr eq 1) ... else ...
} else {
return 0; # error in SQL statement!
} # if ($rv) ... else ...
} # is ($sql =~ /^insert|update|delete/i)
#usage: my $rv = sql_execute($dbh, $sql);
}
</code>
then...in my main script:
<code>
.
.
.
use pm::search;
my $rv = "content-type: text/html\n\n";
my $dbh = sql_connect("ns.db");
.
.
.
my $st = "eye_clr"; # field to search for
my $sv = 1; # value to search for
$rv .= "Testing <i><b>search_item</b></i><br>\n";
my @users = get_users($dbh, 1); # gets JUST a list of user ID's - which uses the above "sql_execute" subroutine and works perfectly!
$rv .= "searching " . @users . " users: ";
foreach my $uid (@users) { $rv .= "$uid, "; }
$rv =~ s/, $/<br>\n/; # replace the last comma and space with a <br> and a new line
$rv .= "";
# the following should return JUST a hash reference! but nooooo....it returns convoluted results which just baffle me
my @results = search_item($dbh, \@users, $st, $sv) . "\n";
$rv .= "Number of array elements: <b>" . @results . "</b><br>\n";
foreach my $result (@results) {
$rv .= "$result<br>\n";
}
.
.
.
print $rv;
exit 1;
</code>
now, when i run this, the "Number of array elements:" says 1 (which is unexpected....i figured i would be getting a hash reference back - not an array reference with one stinkin element in it! and then, what's more confusing? The last "foreach" loop prints "3"! I expected it to print out three user ID's...not just a number....so clearly there's something i'm missing.
in some cases this works as I want it to. for instance, i have no issue getting a list of user ID's....that works perfectly. in other cases, though, i get these very strange and unexpected results.
can anyone review the above code, and perhaps suggest why i get a "3" and not the expected 3 user ID's???
Jarett