in reply to Can't retrieve all records

Just keep doing the fetchrow_hashref till you get undef: 

while($track_ref = $sth->fetchrow_hashref ()){
#do something
}

[download]

Or better yet, use fetchall_hashref(): 

$track_ref = $sth->fetchall_hashref(<keyfield>);

[download]

Don't forget to specify a unique field to key the hash off of.

HTH

Replies are listed 'Best First'.
Re: Re: Can't retrieve all records
by b310 (Scribe) on Jun 03, 2003 at 21:01 UTC
    Hi,

    I modified by $sth statement to look like:

    $track_ref = $sth->fetchall_hashref ($customer_id);

    When I run my script, I'm receiving the following error message:

    Can't locate object method "fetchall_hashref" via package "DBI::st"

    Any ideas?
[reply]
      fetchall_hashref is a fairly recent addition to DBI. Consider having your version of DBI upgraded (if that's an option).

      The other way you can do it ... and there are pretier ways then this I'm sure, but it will work: 

      {
my ($dbh, $track_ref) = @_;

$dbh = WebDB::connect ();
my $sth = $dbh->prepare ("SELECT * FROM Shipments WHERE customer_id = 
+?");
$sth->execute($customer_id);

while($track_ref = $sth->fetchrow_hashref) 
  {
  .. do stuff with each row returned here
  }

}

      [download]
[reply]
[d/l]

      You might have an old version of the DBI module. Either upgrade it or use the first solution that pzbagel wrote you.

      Michele.

[reply]
      Hi,

      Someone else suggested that I use fetchrow_arrayref.

      I modified my subroutine to incorporate this suggestion and I'm having problems with it.

      Here's the code if someone can take a look and help me out.
      sub get_customer
{
my ($dbh, $track_ref) = @_;

$dbh = WebDB::connect ();
my $sth = $dbh->prepare ("SELECT * FROM Shipments WHERE customer_id = 
+?");
$sth->execute($customer_id);
while (my $track_ref = $sth->fetchrow_arrayref ())
print p $customer_id,
print p $customer_item_number,
print p $tracking_number,
"\n";
}

      [download]

      Thanks.
[reply]
[d/l]

        fetchrow_arrayref() returns you an array reference, so you should use it in a way like this:

        while (my $track_ref = $sth->fetchrow_arrayref ()) {
    print $$track_ref[0]; # This prints the first field
    print $$track_ref[1]; # This prints the second field
    # ...and so on
}

        [download]
        If you plan to use this method, it's a good practice to avoid SELECT *, and specify in the query all the fields in the order you expect them to be. It works even with the first syntax, however you're going to need to adjust your code if for some reason you recreate your table with a different field order or with additional fields inserted in the middle.

        Michele.

[reply]
[d/l]

In Section Seekers of Perl Wisdom