Thank you, David, very much for your solution. With one minor change
("use DBD::mysql"), it worked perfectly. I am in awe of you ability to
do this.
I was hoping to use this as a subroutine, to build an array in memory,
then to use it repeatedly without rereading the database. Can your
solution be used for this? I think your "while' loop could be used to
build up this structure [I think this is a hash of arrays. Am I
correct?], but I'm not sure how to populate it.
Thanks, again, for the time you took to help me.
-Kevin
>>> "David Kirol" <[EMAIL PROTECTED]> 04/16/02 11:09AM >>>
Kevin,
The script below works on my (quickly constructed) approximation
of your
table/data. It somewhat skirts the issue you mentioned (getting started
with
hashes)
but it may be more to the point.
use DBI;
use DBD::Mysql;
use strict;
my $dbname = ""; # enter your db name
my $host = "localhost";
my $dbuser = ''; # user may be required
my $dbpw = ''; # pw may be required
my $mscs = "dbi:mysql:dbname=$dbname;host=$host;";
my $dbh = DBI->connect($mscs, $dbuser, $dbpw) or die "Connect fails to
$dbname\nError = ", DBI::errstr;
my $sql = "select * from method";
my $sth = $dbh->prepare($sql) or die "Prepare fails for
stmt:\n\t\t$sql\nError = ", DBI::errstr;
my $rv;
unless ($sth->execute) {
print"\n\tExecute fails for stmt:\n\t\t$sql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
print "\t\t$rv\n\n" if $rv;
my %row_ary;
my $row_ary;
my $key;
while ($row_ary = $sth->fetchrow_hashref) { # fetch as a Hash
Reference
foreach $key (sort keys %$row_ary) { # '%$' is used to
dereference the
scalar $row_ary to the hash
print"Key:$key Value $$row_ary{$key}\n"; # $$row_ary is
part of the deref
voodoo
}
print"\n";
}
$sth->finish;
$dbh->disconnect;
HTH
-----Original Message-----
From: KEVIN ZEMBOWER [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 10:15 AM
To: [EMAIL PROTECTED]
Subject: Need help getting started using hashes
I'm having a hard time understanding and getting started with hashes.
I
don't think I get the concept, and so can't go from the concept to the
actual code.
I have a MySQL database table called "methods" with three fields,
methodid, method, and sname. I'd like to read them into a hash, so
that
I could refer to them there (in memory) rather than repeatedly reading
the database.
Here's the output of the table:
mysql> select * from method;
+----------+------------------------------------+---------+
| methodid | method | sname |
+----------+------------------------------------+---------+
| 1 | Combined OCs | COC |
| 2 | Progestin-Only OCs | POC |
| 3 | DMPA/NET EN | DMPA |
| 4 | Norplant Implants | NI |
| 5 | Female Sterilization | FS |
| 6 | Vasectomy | Vas |
| 7 | Condoms | Condoms |
| 8 | TCu-380A IUD | TCu |
| 9 | Spermicides | Sperm |
| 10 | Diaphragm Cervical Cap | DCC |
| 11 | Fertility Awareness-based Methods | FABM |
| 12 | Lacational Amenorrhea Method (LAM) | LAM |
+----------+------------------------------------+---------+
12 rows in set (0.01 sec)
(I work in the field of reproductive health. Can you tell?)
I think I can read in the table with this code, but don't know how to
build it into a hash:
use DBI;
our $dbh = DBI->connect("dbi:mysql:cire:localhost", "cire",
"password",
{RaiseError => 1, AutoCommit => 0}) or die;
my $method_arrayref = $dbh->selectall_arrayref("SELECT methodid,
method, sname FROM method ORDER BY methodid");
[Don't know what to do here to build it into a hash, that I can
reference by explicitly stating the methodid, then using the method or
sname.]
Instead of the selectall_arrayref, I can use a while loop and read the
table one row at a time, using fetchrow_arrayref, but I still don't
know
how to use that to build the hash. Would this method be easier?
Finally, when I've got the hash built, how can I get a particular
method, by specifying it's methodid, or instance?
Thanks for your help and suggestions. Please let me know if I should
take this question to the DBI-users list.
-Kevin Zembower
-----
E. Kevin Zembower
Unix Administrator
Johns Hopkins University/Center for Communications Programs
111 Market Place, Suite 310
Baltimore, MD 21202
410-659-6139
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
