jamroll has asked for the wisdom of the Perl Monks concerning the following question:
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
########################
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, c
+reate, 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 a
+n 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 arr
+ay element
} else {
if (not @arr) {
return 0; # 0 on error
} else {
# and array reference when there is more than 1 element in t
+he 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);
}
then...in my main script:
.
.
.
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 - whic
+h 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> a
+nd 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;
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
Re: DB search results using DBI...execute
by huck (Prior) on Mar 04, 2017 at 21:44 UTC
|
use strict; use warnings;
use Data::Dumper;
my @arr=({a=>1,b=>2},{a=>3,b=>4},{a=>5,b=>6});
print '$#arr :'.$#arr."\n";
print 'scalar(@arr):'.scalar(@arr)."\n";
print Dumper \@arr;
my @results = rvar('hash') . "\n";
print Dumper \@results;
print "Number of array elements: <b>" . @results . "</b><br>\n";
my @results2 = rvar('arr') . "\n";
print Dumper \@results2;
print "Number of array elements: <b>" . @results2 . "</b><br>\n";
sub rvar {
my $mode=shift;
my @arr=({a=>1,b=>2},{a=>3,b=>4},{a=>5,b=>6});
if ($#arr eq 2) {print "text eq works but weird\n"; }
if ($mode eq 'hash') {
my $hashRef = $arr[0];
return $hashRef;
}
elsif ($mode eq 'arr') {
my $arrayRef = \@arr;
return $arrayRef;
}
return 0;
}
result
$#arr :2
scalar(@arr):3
$VAR1 = [
{
'b' => 2,
'a' => 1
},
{
'b' => 4,
'a' => 3
},
{
'a' => 5,
'b' => 6
}
];
text eq works but weird
$VAR1 = [
'HASH(0x3f7f2c)
'
];
Number of array elements: <b>1</b><br>
text eq works but weird
$VAR1 = [
'ARRAY(0xb321bc)
'
];
Number of array elements: <b>1</b><br>
First, $#arr returns the index of the last element, not the count.
Second all you ever return from sql_execute is a scalar, it may be a hashref, or a arrayref, or a number but it is always a scalar.
Third, in my @results = search_item($dbh, \@users, $st, $sv) . "\n"; you force scalar mode with the . for concatenation, and the "\n" gets placed into the @results array appended to scalar result as its only item.
Fourth, you never showed us search_item so i am just sortof guessing but if search_item actualy did return an array the size of the array would be concatenated with the "\n" since scalar context was forced
Edit: added "index of the" to "returns the index of the last element" | [reply] [Watch: Dir/Any] [d/l] [select] |
|
my oops - here's the "search_item" code...
sub search_item($$$$) {
#*
# to search for a list of user IDs which match one criteria (ie: eye
+_clr=1)
# searches can only happen with the logged in user
# we must not include the logged in user in the search results!
# nor should we include those that are banned or security level 888
+or higher
#*
my ($db, $usersRef, $name, $value) = @_; # a DBH && a reference to a
+ list of users to search && the name of the item to search for && the
+ value the named item must be
my @searched; # a list of UIDs which match the given name=value pair
+s
my $query = "select ID from users where $name='$value';";
my $resultsRef = sql_execute($db, $query);
if ($resultsRef) {
if (ref $resultsRef eq "ARRAY") {
# we have multiple hits!
my @array = @$resultsRef;
foreach my $hit (@array) {
# $hit is a hash reference.
my %hash = %$hit;
push @searched, $hash{ID};
}
} else {
# we only have one hit!
my %hash = %$resultsRef;
push @searched, $hash{ID};
}
}
# it is very possible to have an empty "searched" array.
# this is totally ok.
return @searched; # an array of uid's which matched the specified cr
+iteria, minus the logged in user's uid
#usage: my @results = search_item($db, \@uList, "eyes", "1");
}
| [reply] [Watch: Dir/Any] [d/l] |
|
reviewed my code, and sure enough, that damn "\n" is there! now i feel REALLY dumb! lol - TYVM, huck, for pointing that out! i don't have a CLUE how i did that. Likely due to commenting, and modifications, and just missed it. now, i get a list of UID's, and not just a 3.
Thank you, thank you, thank you. I think i can finally move onto the next stage. MOST appreciated, Sire.
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
|
Re: DB search results using DBI...execute
by kcott (Archbishop) on Mar 05, 2017 at 06:40 UTC
|
G'day jamroll,
Welcome to the Monastery.
In scalar context, @array evaluates to the number of elements.
In list context, you'll get the actual elements.
Instead of concatenating with '.' (which forces scalar context),
you can just embed @array within interpolating quotes.
Here's an example showing all three of those scenarios:
$ perl -E 'my @x = ({}); say ">" . @x . "<"; say ">", @x, "<"; say ">@
+x<"'
>1<
>HASH(0x7fb6c68040b0)<
>HASH(0x7fb6c68040b0)<
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
ty, Ken. I was directed to that error. thought it was a typo when posting this msg, discovered it was in my code, and that fixed the problem. very much appreciate your input!
| [reply] [Watch: Dir/Any] |
Re: DB search results using DBI...execute
by poj (Abbot) on Mar 04, 2017 at 21:25 UTC
|
use Data::Dumper;
print '<pre>';
print Dumper \@results;
print '</pre>';
The code for search_item() looks to be more relevant to your question than sql_execute().
poj | [reply] [Watch: Dir/Any] [d/l] [select] |
|
yes! ty for that. not entirely certain how that helps...perhaps i'm misguided. i'm asking why the code gives back a 3...not if search_item() is more relevant than sql_execute(). frankly, your reply, although highly appreciated, makes little sense to me.
| [reply] [Watch: Dir/Any] |
|
my @results = search_item($dbh, \@users, $st, $sv) . "\n";
is slightly incorrect, btw - and not sure how the "\n" got in there....it's not there in my actual code.
so, it should read just:
my @results = search_item($dbh, \@users, $st, $sv);
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
|
|
Re: DB search results using DBI...execute
by clueless newbie (Curate) on Mar 04, 2017 at 23:34 UTC
|
| [reply] [Watch: Dir/Any] |
|
thanks for the link. packed with information! too much info, really. I'll stick with the prototyping. it would be just awful to have to recode my modules. it's working, and I see little need to do away with the prototypes.
| [reply] [Watch: Dir/Any] |
|
|