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

Alright, don't laugh. I am executing a query using Sybperl. The data returns from my dbresults in array elements. What I want to do is to sort the output based on one of the array elements. Here's a snippet of my code: 

$dbh->dbcmd($SQL);
$dbh->dbsqlexec;

   while ($dbh->dbresults != NO_MORE_RESULTS) {
         while (@data = $dbh->dbnextrow) {

        $Id = $data[0];
        $Status = $data[1];
        $StatusDate = $data[2];
        $Severity = $data[3];
        $Priority = $data[4];
        $ProjectId = $data[5];
        $SubSystem = $data[6];
        $Process = $data[7];
        $Name = $data[8];
        $EnteredBy = $data[9];
        $DateEntered = $data[10];
        $Contact = $data[11];
        $Closed = $data[12];
        $DateClosed = $data[13];
        $ClosedReason = $data[14];
        $Summary = $data[15];

[download]


I would like to sort this by $Name. Can you please help?

Thanks, Louis

Replies are listed 'Best First'.
Re: PERL CGI Newbie Question
by Ovid (Cardinal) on Dec 13, 2002 at 21:39 UTC

    fruiture and Foxcub are absolutely correct that you wish to do the sort by specifying an 'ORDER BY' clause in the SQL. Pushing the sort into the SQL is far more efficient than trying to do this in Perl. However, in the interest of showing you how to do this in Perl, I have to start by pointing out that you do not have a sortable data structure. What you want is some type of array of arrays or hashes that you can sort. Also, it looks like your nested while loops may have a logic error. Here's one way to approach this:

    $dbh->dbcmd($SQL);
$dbh->dbsqlexec;

my @fields = qw( Id Status StatusDate Severity Priority ProjectId SubS
+ystem
    Process Name EnteredBy DateEntered Contact Closed DateClosed
    ClosedReason Summary );
my (@results,@data);

while ($dbh->dbhresults != NO_MORE_RESULTS and @data = $dbh->dbnextrow
+) {
    my %results;
    @results{ @fields } = @data;
    push @results => \%results;
}

@results = sort { $a->{Name} cmp $b->{Name} } @results;

    [download]

    You now have an array of hash references sorted by 'Name'. See perldoc perlreftut for more information on using references.

    Cheers,
    Ovid

    New address of my CGI Course.
    Silence is Evil (feel free to copy and distribute widely - note copyright text)

[reply]
[d/l]
      Just as a side-note, to make this a little easier (yes, I'm lazy), you can have Sybase return a hash keyed on column name for you - using my %hash = $dbh -> dbnextrow(1);

      This means that with Ovid's code above as a basis you can do something like 

      my @results;
while (my %data = $dbh -> dbnextrow(1)) {
  push @results => \%data; 
}

      [download]

      I should point out that I haven't tested that (at least specifically here), though. Of course, if your're not using the DB column names as your hash keys, it'll all go pear-shaped ..

      Just out of interest, as I said.
      -- Foxcub

[reply]
[d/l]
[select]
        Thank you . This is exactly what I wanted to learn.
[reply]
Re: PERL CGI Newbie Question
by Tanalis (Curate) on Dec 13, 2002 at 20:06 UTC
    Easy - in your SQL, add the line 
    # $field is the database table column that you want to sort by
ORDER BY $field

    [download]
    after your select and condition statements.

    Everything you could ever want to do ever (well, maybe) with Sybase (SQL-wise) is usefully online - the Transact SQL manual is here.

    Hope that helps ..
    -- Foxcub

[reply]
[d/l]
      I'm sorry, I didn't clarify. I would like to do the sort via an array or a hash. I'm trying to learn how to do more things in perl for better understanding.

      Thanks again,
      Louis

[reply]
Re: PERL CGI Newbie Question
by fruiture (Curate) on Dec 13, 2002 at 20:05 UTC

    What do you want to sort by name? Your code does not create anything to be sorted...

    Anyway, you can sort the results of an SQL Query using SQL's 'ORDER BY'.

    --
    http://fruiture.de
[reply]

Back to Seekers of Perl Wisdom