How can I use bind_colums to loop through my variables and find matching values?

[Harch84]

Hi guys really hoping someone can advise me with this.

I have a Perl script that connect to a PostgreSQL database and retrieves fields and rows from a database of bus timetables and bus stop information. The data I am extracting is the reference number of the bus stop, the route number this bus stop serves and the distance of a point to this bus stop. The select query returns rows like this in PostgreSQL:

stop_reference | service_id | distance
----------------+------------+------------------
6200200581 | 1 | 131
6200249240 | 2 | 148.32734070292
6200249230 | 2 | 247.695377429616
6200249250 | 2 | 394.507287638644
6200249220 | 2 | 394.81641303269

Now what I want to be able to do is to loop through each of the rows from this query in Perl, and to find where the service_id is the same. And make an IF statement that says when they are the same only return the row with the shortest distance.

This is the part of the Perl script that brings my query into Perl:

$originsql = qq{ SELECT a.stop_reference, b.service_id, distance........};
$sth = $dbh->prepare( $originsql );
$sth->execute();
$sth->bind_columns( undef, \$stops, \$service, \$distance );
while( $sth->fetch() )
{

Im hoping someone can help me to do this? If you need anymore information please let me know. Thanks

[Harch84]

OK answer was found already thanks anyway guys! Ill post it here for anyone else who is interested:

my $last_service_id = -8675309;    # number it cannot be
 
while ($sth->fetch()) {
    if ($orig_service != $last_service_id) {
        print "min dist for service '$orig_service' = '$orig_distance' at stop '$orig_stops' serving route number '$orig_route_number \n <p><p/>";
        $last_service_id = $orig_service;
    }
}
 

* The rows from the database were ordered by service first in the query. Hope this helps someone who has a similar problem.

[miller]

Very good. However, don't forgot to include your or die statement with your execute. It's simply a good habit to have:

$sth->execute() or die $dbh->errstr;
 

- Miller