Gerald Host wrote:
>
> Rob Dixon wrote:
>
>> You don't want to use selectall_hashref because, as is the nature of hashes,
>> the key must be unique, which means the database table's key field that
>> provides it must also be unique. If you get this working, you will retrieve a
>> single record for each possible value of each key: presumably 'yes', 'no',
>> '', and NULL or something like that.
>
> I know. One of my key columns is an ID field so there will be a unique key.
It doesn't work like that. The hash level corresponding to ifFollowedProtocol
must be unique on its own, It's independent of anything in the nested hashes and
(in your code below) anything with the same 'medCategory' 'Medication' 'include'
and 'ifFollowedProtocol' will try to occupy the same slot in the hash at that
level and previous stuff will be overwritten.
By the way, is your list of key fields below correct? You said you had five key
columns in your original post.
>> It does look though as if you're pulling all your database data into a Perl
>> hash and trying to interrogate that. Surely, in your example above, you
>> should be writing somthing such as:
>>
>> my $data = $dbh->selectall_arrayref(qq(
>> SELECT id, medication
>> FROM table
>> WHERE col1 = 'yes' OR OR col1 = '' OR col1 IS NULL
>> ORDER BY id
>> ));
>>
>> foreach my $row (@$data) {
>> printf "%s: %s\n", @$row;
>> }
>
>
>
> nope:
>
> my $href =$$dbh->selectall_hashref(q{SELECT * FROM recommendations WHERE
> RxNumber=? ORDER BY medCategory, Medication, methodPreference}, [qw(
> medCategory Medication include ifFollowedProtocol ifCycling ID) ],
> undef,('12345'));
OK, that's a little clearer. But why so many key fields? In fact why not just
ID, since it's a unique one. By the way, an ORDER BY is pointless with
selectall_hashref, as hashes are unordered.
> So I'm still confused about what I need to do. I want to:
>
> 1. display the rows with ifFollowedProtocol eq '' or 'yes' in order of
> medCategory
> 2. display the rows with ifFollowedProtocol eq '' or 'no' in order of
> medCategory
>
> I thought I could do this with a hash of hashes...I know there are other
> ways, but I'd like to understand how to do it with this hashref using hash
> slices if it's possible.
Alright, lets try.two solutions. First mine, which seems to do what you want,
but has only one level of hash so is a lot easier to code for
my $href = $dbh->selectall_hashref(q{
SELECT *
FROM recommendations
WHERE RxNumber = ?
}, 'ID', undef, 12345);
foreach my $id (sort keys %$href) {
my $data = $href->{$id};
my $ifp = $data->{ifFollowedProtocol};
next if defined $ifp and $ifp ne '' and $ifp ne 'yes';
print $data->{ID}, "\n";
print $data->{Medication}, "\n";
}
And now one with all the key fields in as you had, and a hash slice
my $medcat_href = $dbh->selectall_hrefref(q{
SELECT *
FROM recommendations
WHERE RxNumber = ?},
[qw(
medCategory
Medication
include
ifFollowedProtocol
ifCycling
ID
)],
undef,('12345'));
foreach my $med_href (values %$medcat_href) {
foreach my $include_href (values %$med_href) {
foreach my $protocol_href (values %$include_href) {
foreach my $cycling_href (@{$protocol_href}{'yes', ''}) {
foreach my $id_href (values %$cycling_href) {
foreach my $data (values %$id_href) {
print $data->{ID}, "\n";
print $data->{Medication}, "\n";
}
}
}
}
}
}
which I've simplified from your code by taking the hash references out at each
stage instead of applying the full sequence of keys relative to the root
$medcat_href. Also, if you put the ID key first in the list, the problem of
duplicate 'ifFollowedProtocol' values is removed, but then the whole idea of
putting multiple keys in here becomes obviously pointless as every hash wihtin
the outermost one will have only a single key/value entry.
Now I expect I've misunderstood something here, but I hope some of this is of
some use to get you on track? Let us know.
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
