Sorting SQL results

[nickt9999]

Hi All,

I am after some help on SQL results and sorting.

A manual SQL query returns the following:

+--------+--------+
| userid | giftid |
+--------+--------+
|  42128 |      1 |
| 101932 |      1 |
| 105596 |      2 |
| 107736 |      3 |
| 109274 |      2 |
+--------+--------+

I am print the results to file using:

while($row_ref = $sth->fetchrow_hashref()) {
 print F "$row_ref->{$_} " for keys %$row_ref;
 print F "\n";
}

However it prints to file as this:

1 42128
1 101932
2 105596
3 107736
2 109274

Is there a way to get it to print to file in the way the results from the query are returned?

Thanks In Advance

Nick

[roboticus]

nickt9999:

The keys will come out of the hash in an arbitrary order. So you can either:

• Name the columns in alphabetic order and sort them:

  $SQL = "select userid a, giftid b ....";
  . . .
  print F "$row_ref->{$_} " for sort keys %$row_ref;
  

• Specify the order you want:

  print F "$row_rf->{$_} for qw( userid gifid );
  

• Or you could fetch an arrayref and print them in the order you specify in the SQL statement:

  while ($row_ref = $ST->fetchrow_arrayref) {
      print F "$row_ref->[$i] " for 0 .. $#{$row_ref};
  

  By the way, that could be written better as:

  print F join(" ", @$row_ref);
  

Update: changed keys to columns in first bullet point; Added while statement part to the third to illustrate fetchrow_arrayref.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[nickt9999]

Name the keys in alphabetic order and sort them:

$SQL = "select userid a, giftid b ...."; . . . print F "$row_ref->{$_}
+ " for sort keys %$row_ref;

Many thanks for this, it worked a treat.

Cheers

[choroba]

If you want to sort the keys of the hash numerically, tell Perl to do so:

print F $row_ref->{$_} for sort { $a <=> $b } keys %$row_ref;

Sorry, you want to print the userid first. Just do

print F $row_ref->{$_} for qw(userid giftid);

[nickt9999]

Sorry was typing my reply as you corrected yours.

I cannot use :

print F $row_ref->{$_} for qw(userid giftid);

as the column names will be dynamic based on what table I query.

Is there a way to order they by how they query returns them?

[Anonymous Monk]

fetchrow_arrayref works.

[runrig]

One general solution:

$sth->execute();
my $cols = $sth->{NAME_lc};
my %row;
$sth->bind_columns( \@row{@$cols} );
while ( $sth->fetch ) {
  print "$row{$_} " for @$cols;
  print "\n";
}

[sundialsvc4]

My general suggestion is that you should always use ORDER BY in your SQL statement, so that you specify the ordering that you require, if you care at all about the order.   Otherwise, the ordering of rows in any SQL query is defined to be:   “unpredictable.”   It can vary from query to query, and it certainly can be expected to vary as the table-size grows.

[roboticus]

sundialsvc4:

The ORDER BY clause tells the database to sort the rows into order. The OP had difficulty getting the columns in the correct order.

While the title of the post does suggest that an ORDER BY clause may be appropriate, sometimes you need to read the body of the post in order to compose a relevant reply.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[spandox]

Does his question involve the table format too?

use Text::ASCIITable;

Maybe be part of the solution....