My problem is how to decide what element the hash is sorted on?

Before anyone can offer advice, you have to describe the hash, or schema

You're probably doing too much work anyway, you want to do the sorting through SQL and use DBIx::HTMLTable

Thank you for fast response. It's hard to know where to look for the problem...

 my $query = qq|SELECT
                   s.shiptofax, s.shiptocountry, s.shiptophone, s.ship
+toaddress1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, o.waybill, o.shipvia
          FROM shipto s, oe o
          WHERE ((trans_id =  $form->{"$form->{vc}_id"}) AND ( o.id = 
+$form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))
          UNION
          SELECT
           s.shiptofax, s.shiptocountry,  s.shiptophone, s.shiptoaddre
+ss1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, o.waybill, o.shipvia
          FROM shipto s
          JOIN oe o ON (o.id = s.trans_id)
          WHERE ((o.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))
          UNION
          SELECT
           s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddres
+s1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, a.waybill, a.shipvia
          FROM shipto s
          JOIN $table a ON (a.id = s.trans_id)
          WHERE ((a.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))|;


  if ($form->{id}) {
    $query .= qq|
                 EXCEPT
         SELECT
          s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddress
+1, s.shiptoaddress2,
         s.shiptocity, s.shiptostate, s.shiptozipcode,
         s.shiptocontact, s.shiptoname,
         s.shiptoemail, o.waybill, o.shipvia
         FROM shipto s, oe o
         WHERE ((s.trans_id = '$form->{id}') AND ( o.id = '$form->{id}
+'))
         |;
  }

  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);
  
  my $ref;
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_shipto} }, $ref;
  }
  $sth->finish;

  $dbh->disconnect;
[download]

This is the code I build the hash from. I would like to sort on 'shiptofax' but it doesn't get this way... Regards,

Just wanted to point out that you can make that SQL quite a bit less repetitive. Depending on the data stored in t2 and t3 this may or may not hold true

SELECT t1.col1, t2.col5
  FROM t1
  JOIN t2 ON t2.tid = t1.tid
  WHERE t2.col3 = 99 AND ...
UNION
SELECT t1.col1, t3.col5
  FROM t1
  JOIN t3 ON t3.tid = t1.tid
  WHERE t3.col4 = 42 AND ...
[download]

is often equivalent to

SELECT t1.col1, coalesce(t2.col5, t3.col5) AS col5
FROM t1
LEFT JOIN t2 ON t2.tid = t1.tid AND t2.col3 = 99
LEFT JOIN t3 ON t3.tid = t1.tid AND t3.col4 = 42
WHERE ...
[download]

which is probably much easier for the database server to execute. The EXCEPT can be done with a left anti-join.

But do heed the other nameless monk's advice. Taking column names directly from a form is a security hole.

Well, you should use DBI placeholders or quote, otherwise you'll fall for http://bobby-tables.com/

To sort through SQL by shiptofax, its usually a murder of appending

ORDER BY s.shiptofax
[download]

And then the results will be sorted, and you won't have to sort your array (you have an array of hashes , stored in a hash, you'd be sorting the array, which you won't have to do if you adjust your query with an ORDER BY clause)

Not sure what variant of SQL you're using, but you ought to be able to simplify the query:

SELECT s.*, o.waybill, o.shipvia

FROM shipto s, oe o 

WHERE trans_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')

    UNION

SELECT s.*, o.waybill, o.shipvia 

FROM shipto s
JOIN oe o ON (o.id = s.trans_id)

WHERE o.$form->{vc}_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')

    UNION

SELECT s.*, a.waybill, a.shipvia

FROM shipto s
JOIN $table a ON (a.id = s.trans_id)

WHERE a.$form->{vc}_id = $form->{"$form->{vc}_id"} AND
      to_date(s.shiptophone, 'YYYY-MM-DD') >= to_date(to_char(current_
+timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD')
[download]

If you explain what each of those tables has in it and what you're trying to generate, I may be able to suggest a better query structure. Incidently, is there a particular reason why you're storing your dates as timestamps? Dates are easy to manipulate using INTERVAL commands.

use Data::Dumper;
print Dumper(\%shipto);
print Dumper($ref);
[download]

Assuming of course that $ref points to a different data structure. And it would also be nice to see the SQL query you're building your data structures from. It's fairly easy to sort either in the SQL or in Perl.

The respons from

print Dumper(\%shipto);
[download]

was this:

$VAR1 = { 'country' => { 'label' => 'Namn', 'i' => 3 }, 
'name' => { 'label' => 'Klinik', 'i' => 11 }, 
'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
 'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
 'address2' => { 'label' => '', 'i' => 5 }, 
'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
'city' => { 'label' => 'Stad', 'i' => 7 }, 
'fax' => { 'label' => 'Personnr', 'i' => 2 }, 
'address1' => { 'label' => 'Adress', 'i' => 4 } };
[download]

and the response from 'ref' was:

$VAR1 = undef;
[download]

So the output of this is the first level of the hash. I want to sort on the contents in 'fax'!

As Anonymous stated, you can just use an ORDER BY command at the end of your SQL queries to sort your output by any field you prefer. And yes, you need placeholders (or at least some function to safe all your form values before inserting them into queries), and I'm guessing from looking at your query that that could be made a lot more compact as well. But here's a code sample that demonstrates how to sort on fax:

use strict;
use warnings;
use Data::Dumper;

my $form; $form->{'all_shipto'} = [
  {
    'country' => { 'label' => 'Namn', 'i' => 3 }, 
    'name' => { 'label' => 'Klinik', 'i' => 11 }, 
    'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
    'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
    'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
    'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
    'address2' => { 'label' => '', 'i' => 5 }, 
    'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
    'city' => { 'label' => 'Stad', 'i' => 7 }, 
    'fax' => { 'label' => 'A', 'i' => 2 }, 
    'address1' => { 'label' => 'Adress', 'i' => 4 } 
  },
  {
    'country' => { 'label' => 'Namn', 'i' => 3 }, 
    'name' => { 'label' => 'Klinik', 'i' => 11 }, 
    'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
    'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
    'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
    'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
    'address2' => { 'label' => '', 'i' => 5 }, 
    'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
    'city' => { 'label' => 'Stad', 'i' => 7 }, 
    'fax' => { 'label' => 'B', 'i' => 2 }, 
    'address1' => { 'label' => 'Adress', 'i' => 4 } 
  }
];

for (sort {
    $a->{'fax'} cmp $b->{'fax'}
} @{$form->{'all_shipto'}}) {
    print Dumper($_);
}
[download]