Re: mysql sorting

This is a problem I have come across several times, in fact often in the case of constructing HTML tables from a database.

First:

  SELECT
    group, somedata
  FROM
    table
  ORDER BY
    group, somedata
[download]

  my $lastgroup = undef;
  while ( my $row = $sth->fetchrow_arrayref() ) {
    if ( $lastgroup ne $row->[0] ) {
      print( $row->[0] . "\n" );
      $lastgroup = $row->[0];
    }
    print( "  " . $row->[1] . "\n" );
  }
[download]

Update: I think setting $lastgroup initially to undef may be dangerous.. potenially a warning could be generated about using an undefined value in a comparison using the ne operator. So here is an update to the above routine that I think is safer:

  my $lastgroup = undef;
  while ( my $row = $sth->fetchrow_arrayref() ) {
    if ( ( !defined( $lastgroup ) ) || 
         ( $lastgroup ne $row->[0] ) )
    {
      print( $row->[0] . "\n" );
      $lastgroup = $row->[0];
    }
    print( "  " . $row->[1] . "\n" );
  }
[download]

Comment on Re: mysql sorting Select or Download Code

Replies are listed 'Best First'.
Re: mysql sorting by perleager (Pilgrim) on Jun 01, 2005 at 07:47 UTC
Hey, To select the individual groups only once, you can also use the DISTINCT clause for the select statement. Then you can use the results in a prepared cache to find the results for each individual group. monarch seems to have a correct solution also. But I'd thought I post something about the DISTINCT clause because it solves your first problem really well. my $dbh = DBI->connect('DBI:mysql:DB', 'user_n', $pass) or die "Failed + connecting to DB " . DBI->errstr; my ($groups, $results); my $sth = $dbh->prepare_cached(<<SQL); SELECT DISTINCT group from table SQL $sth->execute or die "executing: ", $dbh->errstr;; $sth->bind_columns(undef, \$groups); #bind the results--only contains unique group results. my $sth_x = $dbh->prepare_cached(<<SQL); SELECT somedata FROM table where group = ? SQL my $found = 0; my $found_x = 0; print "<b>$groups</b><hr>"; while ($sth->fetch) { $found++; $sth_x->execute($groups) or die "executing: ", $dbh->errstr; #find results using the results from the first query $sth_x->bind_columns(undef, \$results); while($sth_x->fetch) { $found_x++; print "$results<br>"; } unless($found_x) { print "empty for this group"; } print "<br>"; my $found_x = 0; #reset 2nd search found for next group result query. } unless ($found) { print "no groups matched"; } [download] perleager	[reply] [d/l]

Replies are listed 'Best First'.

Re: mysql sorting
by perleager (Pilgrim) on Jun 01, 2005 at 07:47 UTC

monarch

my $dbh = DBI->connect('DBI:mysql:DB', 'user_n', $pass) or die "Failed
+ connecting to DB " . DBI->errstr;

    my ($groups, $results);
    my $sth = $dbh->prepare_cached(<<SQL);
SELECT DISTINCT group from table
SQL

    $sth->execute or die "executing: ", $dbh->errstr;;
    $sth->bind_columns(undef, \$groups);
    #bind the results--only contains unique group results.

    my $sth_x = $dbh->prepare_cached(<<SQL);
SELECT somedata FROM table where group = ?
SQL
            my $found = 0; my $found_x = 0;
                print "<b>$groups</b><hr>";

                while ($sth->fetch) {
                 $found++;

    $sth_x->execute($groups) or die "executing: ", $dbh->errstr;  
    #find results using the results from the first query
    $sth_x->bind_columns(undef, \$results);


        
                    while($sth_x->fetch) {
                    $found_x++;
                    print "$results<br>";
                    }
                    unless($found_x) { print "empty for this group"; }

        print "<br>";
        my $found_x = 0; 
        #reset 2nd search found for next group result query.
                }

        unless ($found) { print "no groups matched"; }
[download]

[reply]
[d/l]