Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Sorting SQL results

by nickt9999 (Acolyte)
on Oct 11, 2013 at 17:36 UTC ( #1057929=perlquestion: print w/ replies, xml ) Need Help??
nickt9999 has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on Sorting SQL results
Select or Download Code
Re: Sorting SQL results
by choroba (Abbot) on Oct 11, 2013 at 17:57 UTC
    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);
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

      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?

        fetchrow_arrayref works.
Re: Sorting SQL results
by roboticus (Canon) on Oct 11, 2013 at 18:03 UTC

    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.

      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

Re: Sorting SQL results
by sundialsvc4 (Monsignor) on Oct 15, 2013 at 03:35 UTC

    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.

      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.

        Does his question involve the table format too?
        use Text::ASCIITable;
        Maybe be part of the solution....
Re: Sorting SQL results
by runrig (Abbot) on Oct 16, 2013 at 20:38 UTC
    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"; }

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1057929]
Approved by toolic
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (7)
As of 2014-08-02 10:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (56 votes), past polls