######################## 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); } #### . . . 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 search_item
\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/, $/
\n/; # replace the last comma and space with a
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: " . @results . "
\n"; foreach my $result (@results) { $rv .= "$result
\n"; } . . . print $rv; exit 1;