b310 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I have a table that can contain multiple entries for a specific customer number.

Currently, my script retrieves the first record it comes across for that customer number.

Can someone give me a hand on how I can extract all the records for a specific customer number? I pasted all the necessary code.

Thank you in advance.

This section grabs the form parameters....
my $customer_id;
$customer_id = param("customer_id");

[download]

This section extracts the table data based on customer id...
{
my ($dbh, $track_ref) = @_;

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

[download]

Again, my current code extracts only one record not all. Any help is greatly appreciated. Thanks.

Replies are listed 'Best First'.
Re: Can't retrieve all records
by pzbagel (Chaplain) on Jun 03, 2003 at 20:35 UTC

    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

[reply]
[d/l]
[select]
      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]

Back to Seekers of Perl Wisdom