[ic] Order of columns for hashref returned from query?
Ron Phipps
interchange-users@interchange.redhat.com
Thu Apr 18 02:21:02 2002
> 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