[ic] Order of columns for hashref returned from query?

Kyle Cook interchange-users@interchange.redhat.com
Thu Apr 18 04:22:00 2002


At 11:15 PM 4/17/02, you wrote:
> > From: interchange-users-admin@interchange.redhat.com
>[mailto:interchange-
> > users-admin@interchange.redhat.com] On Behalf Of Kyle Cook
> >
> > At 06:15 PM 4/17/02, you wrote:
> > >I'm working on a piece of code that will be called by CommonAdjust to
> > >only return the columns in the pricing table which are populated with
> > >data for the given item.  This will force the quantity discount
> > >mechanism to discount quantity orders which are equal to or exceed a
> > >previous price break.  I had this code working before, but it used a
> > >$Tag->data call for each column which was inefficient to do.  Below
>is
> > >the code as it stands right now.  It selects the data then outputs
>the
> > >columns.  It will be modified to only output the columns that have a
> > >value once I find a solution to the problem.
> > >
> > >[perl tables=pricing]
> > >                         my $code = $Scratch->{tmp_code};
> > >                         my $available_breaks =
> > >"q2,q3,q4,q5,q6,q7,q8,q9,q10,q12,q15,q25,q50,q100";
> > >                         my $sql = "SELECT " . $available_breaks . "
>FROM
> > >pricing WHERE sku ='" . $code . "';";
> > >                         my $sql_results = $Tag->query( {     sql =>
> > >$sql, hashref => 'price_results' } );
> > >                         my $hash = $sql_results->[0];
> > >                         my $out = "";
> > >                         my $key, $value;
> > >                         while ( ($key, $value) = each %$hash ) {
> > >                                 $out .= " $key => $value ";
> > >                         }
> > >                         #foreach $key (sort keys %$hash) {
> > >                         #       $out .= " $key => $$hash{$key} ";
> > >                         #}
> > >                         return $out;
> > >                 [/perl]
> > >
> > >The problem with this code is the ordering of the hash does not match
> > >the ordering of the column list in the select statement.  It is
>output
> > >in this order:
> > >
> > >q15,q9,q25,q50,q2,q3,q10,q4,q5,q12,q6,q7,q100,q8,
> >
> > Ron,
> >
> > My bad, I missed the part where only some columns will be used, hmmm
> > next best try would be using a sort subroutine like:
> >
> > foreach $key (sort {$$hash{$a} <=> $$hash{$b}} keys %$hash) {
> >
> > This will force the keys to be compared numerically, ignoring the
> > 'q' at the beginning.
> >
> > Kyle Cook
>
>Thanks for the help Kyle!  I tried your suggestion and the results were
>sorted on the value instead of the key.  I searched for examples of
>sorting on the key and found some, but all the keys in the examples were
>strings.  My attempts to modify your example to do a numerical search on
>the keys did not work either.  Looks like I need to do some reading on
>hashes and sorting :)
>
>While I was relaxing it hit me, why not just split on the comma in my
>available_breaks and then loop over those values and key into the hash
>to see if the there is a price break.  Below is the code that I came up
>with:
>
>                 [seti tmp_code][item-code][/seti]
>                 [perl tables=pricing]
>                         my $out = "pricing:price_group,";
>                         my $code = $Scratch->{tmp_code};
>                         my $available_breaks =
>"q2,q3,q4,q5,q6,q7,q8,q9,q10,q12,q15,q25,q50,q100";
>                         my $break;
>                         my $sql = "SELECT " . $available_breaks . " FROM
>pricing WHERE sku ='" . $code . "';";
>                         my $sql_results = $Tag->query( {     sql =>
>$sql, hashref => 'price_results' } );
>                         my $hash = $sql_results->[0];
>                         my @breaks = split (/,/, $available_breaks);
>                         foreach $break (@breaks) {
>                                 if ($$hash{$break} ne '') {
>                                         $out .= "$break,";
>                                 }
>                         }
>                         return $out;
>                 [/perl]
>
>Thanks again and I'll post the usertag once it's complete :)
>-Ron


Sorry, thats what you get from someone up way past his bed time :)

Should have been:


foreach $key (sort {$a <=> $b} keys %$hash) {

Kyle