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

Can anyone look at my code below and tell me how to sort this!I need to make it sort by the variable $vStatus and then stop at ten results. This is just the search subroutine I have written, my email is dchenault@xpeditioner.com. Thanks for all your help! 
sub getdata{

my($query) = "SELECT * FROM dataprofiles where $searchquery";
    my($sth) = $dbh->prepare($query);
    $sth->execute || die("Couldn't exec sth!");
    while(@row = $sth->fetchrow)  {
        $vprofilenumber = $row[0];
        $vStatus = $row[1];
        $vPricePlusProduct = $row[2];
        $vKeywords = $row[3];
                $vUsername = $row[4];
        
    print <<EndHTML;
<html><head><title>Price Check</title></head>
<body>
<CENTER><FORM ACTION="http://www.xpeditioner.com/cgi-bin/merchant-mysq
+l-db/search.cgi" METHOD=POST>
   <CENTER><TABLE BORDER=0 WIDTH=600>
      <TR>
         <TD WIDTH=245>
            <P><FONT COLOR="#FFFFFF"><INPUT TYPE=hidden NAME=Username 
+VALUE="$vUsername" size=30 maxlength=40>
            <STRONG>\$$vStatus--$vPricePlusProduct</STRONG></FONT></P>
         </TD>
         <TD WIDTH=342>
            <P><INPUT TYPE=submit NAME=Submit VALUE="Click for Company
+ Information and Chat"></P>
         </TD>
      </TR>
   </TABLE>
   </CENTER>
</FORM></CENTER>

EndHTML
    }
$sth->finish;
}

[download]

Edit ar0n 2001-07-04

Replies are listed 'Best First'.
Re: Sorting
by voyager (Friar) on Jul 04, 2001 at 21:29 UTC
    As to sorting, you could either have the database do it (recomended) by having an ORDER BY column_name in your SQL statement. If you want Perl to do it, you'll need to get all the data back and sort it your self: 
    my $ref = $st->fetchall_arrayref;
my @sorted = sort { $a->[1] cmp $b->[1] } @$ref;

    [download]
    assuming you still want to sort on the 1st column. Enough of sorting.

    I assume you want one TR per row in your select's result set. But the way you have it coded, you'll produce one entire HTML document with one TR for each row returned from your database; you have too much code inside your WHILE. You need to only have the TR code in side your loop. Something like: 

    my $sth = ...
print [all your html including the TABLE tag ]

while (@row = ...   {
   print [ code for each row]
}

print [ rest of html doc ]

    [download]
[reply]
[d/l]
[select]
Re: Sorting
by dvergin (Monsignor) on Jul 05, 2001 at 04:24 UTC
    This does not relate to your question, but you can make your code more Perlish (and fun!) by grabbing the @row values as a group rather than one-at-a-time: 
    while(@row = $sth->fetchrow)  {
   ($vprofilenumber,
    $vStatus,
    $vPricePlusProduct,
    $vKeywords,
    $vUsername) = @row;
   print ...
   ...
}

    [download]
    (Season with spaces and linebreaks to taste.)

    If that is comfortable, you can go a step further and eliminate the intermediate @row array entirely: 

    while( ($vprofilenumber,
        $vStatus,
        $vPricePlusProduct,
        $vKeywords,
        $vUsername) = $sth->fetchrow) {
   print...
   ...
}

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom