CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Sort array which contains sql results

 



dontknowperl
Novice

Aug 19, 2008, 8:36 AM

Post #1 of 15 (4344 views)
Sort array which contains sql results Can't Post

 
Hello everyone, hoping someone can help me out with the following. I am trying to sort values which are stored in an array based on sql query results. What I am not sure
how to do is use that array which contains the rows in order to sort the data. FYI sorting cannot be done in SQL query since it will only sort the current record and not the end
result.

How would I use the array which I pushed all data to in order to sort?


Code
 
my @lines = read_file('/tmp/test.txt');
chomp(@lines);

my $sql = q|select * from table_name where srcaddr = ? or dstaddr = ?|;
my $sth = $dbh->prepare($sql) or die "Can't prepare SQL statement: $DBI::errstr\n";

foreach my $line (@lines) {
$sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
push(@all_rows, $ref);
}
my @sorted = map {$_->[0]} sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] || $a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} map {
[$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
print "$_\n" for @sorted;
}


The problem is that whenever I run the code I get the following shown below. I am guessing this is because I am using the array @all_rows which it doesn't know how to handle? I am saving the output/results from the SQL query to an array called @all_rows, now what I want to do is sort the data in column 2 in that array. What is the proper way to work/sort the data in the array @all_rows which contains all the sql results.

Thanks again for the help..


Code
ARRAY(0x96b0d80) 
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)
ARRAY(0x96b0d80)



shawnhcorey
Enthusiast


Aug 19, 2008, 10:07 AM

Post #2 of 15 (4340 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To
The problem is that whenever I run the code I get the following shown below. I am guessing this is because I am using the array @all_rows which it doesn't know how to handle? I am saving the output/results from the SQL query to an array called @all_rows, now what I want to do is sort the data in column 2 in that array. What is the proper way to work/sort the data in the array @all_rows which contains all the sql results.


This will display all levels in the arrays.


Code
use Data::Dumper; 

print Dumper \@all_rows;

print Dumper \@sorted;


__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 19, 2008, 11:54 AM

Post #3 of 15 (4331 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

Shawn thanks for the quick reply/help. When I use print Dumper \@sorted; All I get are empty variables which ends up getting stuck in a loop. When I printed with Dumper @sorted instead I got correct data but not all in one line so the sorting could not take effect one row per line. Shown below...Any ideas?? Thanks for the help again!!


Code
$VAR1 = [ 
'data here'
'data here'
'data here'
];


Current code

Code
 
my @lines = read_file('/tmp/test.txt');
chomp(@lines);

my $sql = q|select * from table_name where srcaddr = ? or dstaddr = ?|;
my $sth = $dbh->prepare($sql) or die "Can't prepare SQL statement: $DBI::errstr\n";

foreach my $line (@lines) {
$sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
push(@all_rows, $ref);
}
my @sorted = map {$_->[0]} sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] || $a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} map {
[$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
print Dumper @sorted;
#print Dumper \@all_rows;
}



shawnhcorey
Enthusiast


Aug 19, 2008, 12:09 PM

Post #4 of 15 (4328 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To
Shawn thanks for the quick reply/help. When I use print Dumper \@sorted; All I get are empty variables which ends up getting stuck in a loop. When I printed with Dumper @sorted instead I got correct data but not all in one line so the sorting could not take effect one row per line. Shown below...Any ideas?? Thanks for the help again!!


But what did you get for @all_rows?

__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 19, 2008, 12:28 PM

Post #5 of 15 (4327 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

print both Dumper @sorted or Dumper @all_rows shows the same data
one row per line
Same as above.

thanks for the help again


shawnhcorey
Enthusiast


Aug 19, 2008, 12:59 PM

Post #6 of 15 (4325 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To
print both Dumper @sorted or Dumper @all_rows shows the same data
one row per line
Same as above.

thanks for the help again


Try replacing your push statement with:


Code
  push @all_rows, [ @$ref ];


__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 20, 2008, 5:23 AM

Post #7 of 15 (4279 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

No same thing.


Code
foreach my $line (@lines) { 
$sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
push @all_rows, [ @$ref ];
}
my @sorted = map {$_->[0]} sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] || $a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} map {
[$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
print Dumper @sorted;
#print Dumper @all_rows;
}


Results look like this


Code
$VAR1 = [ 
'data',
'data',
'data',
'data',
'data',
];

$VAR2 = [
'data',
'data',
'data',
'data',
'data',
];



(This post was edited by perluser01 on Aug 20, 2008, 6:05 AM)


shawnhcorey
Enthusiast


Aug 20, 2008, 6:05 AM

Post #8 of 15 (4275 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To
No same thing except this time there are other $VARs that show up in the results.


You're missing the second '@'

push @all_rows, [ @$ref ];

__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 20, 2008, 7:00 AM

Post #9 of 15 (4272 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

lol, when I originally pasted the code I pasted the old one. But I modified the code that I pasted on the site to reflect the correct change. But I did have the second @ in the code that I test.
Any other ideas?

thanks,


KevinR
Veteran


Aug 20, 2008, 9:51 AM

Post #10 of 15 (4261 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post

If you are doing this:


Code
print Dumper \@all_rows;


that data is not yet sorted.

What you should do is post what the real data looks like from the Dumper output and explain what you are trying to sort.
-------------------------------------------------


dontknowperl
Novice

Aug 20, 2008, 10:28 AM

Post #11 of 15 (4258 views)
Re: [KevinR] Sort array which contains sql results [In reply to] Can't Post

Kevin thanks for the reply. Here is the current code that I am working with. Below the code is what the results look like. The only thing I am confused about is that the results correct and accurate but the row that has the first IP address should be sorted but it is not. What am I doing wrong? Do I need to add another sort? Not sure.


Code
my @lines = read_file('/tmp/test.txt');  
chomp(@lines);

my $sql = q|select * from table_name where srcaddr = ? or dstaddr = ?|;
my $sth = $dbh->prepare($sql) or die "Can't prepare SQL statement: $DBI::errstr\n";

foreach my $line (@lines) {
$sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
push(@all_rows, $ref );
}
my @sorted
= map { join( ' ', @{ $_->[0] } ) }
sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] ||
$a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} map {
[$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
print Dumper ( @sorted );
}


Exact output when running the script.

Code
$VAR1 = '2008-07-26 10.10.10.1 3.3.3.3 80 hxxp://3.3.3.3 200 OK'; 
$VAR14 = '2008-07-26 192.168.1.32 11122 3.3.3.3 80 hxxp://4.4.4.4 200 OK';
$VAR29 = '2008-07-26 1.1.1.1 11122 3.3.3.3 80 hxxp://3.3.3.3 200 OK';


Should be sorted or end up looking like this. FYI if I print using print Dumper ( @sorted ); I get results like shown above, when I use print "$_\n" for @sorted; I get the results without the $VAR =

Code
2008-07-26 1.1.1.1 11122 3.3.3.3 80 hxxp://3.3.3.3 200 OK 
2008-07-26 10.10.10.1 3.3.3.3 80 hxxp://3.3.3.3 200 OK
2008-07-26 192.168.1.32 11122 3.3.3.3 80 hxxp://4.4.4.4 200 OK


(This post was edited by perluser01 on Aug 20, 2008, 11:19 AM)


shawnhcorey
Enthusiast


Aug 20, 2008, 11:54 AM

Post #12 of 15 (4240 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To


Code
        my @sorted 
= map { join( ' ', @{ $_->[0] } ) }
sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] ||
$a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} map {
[$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
print Dumper ( @sorted );



In: map { [$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
$_ is a reference to an array yet it's not dereferenced. Too see the parts for the sort:


Code
my @part1 =  map { [$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows; 
my @part2 = sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] ||
$a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} @part1;
my @sorted = map { join( ' ', @{ $_->[0] } ) };
print Dumper \@part1, \@part2, \@sorted;


I don't think @part1 has what you think it does.

__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 21, 2008, 6:44 AM

Post #13 of 15 (4190 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

Hey Shawn thanks again for the reply/help. Unfortunately the results are still printing out unsorted. What I think the problem is that I am trying to apply the sort to an array_ref and not to the actual data? Does that sound right? Not sure, I've been looking at this so long that I am not sure at this point.

Here is the current code along with a sample of the results on thing that I had to change was the following line
my @sorted = map { join( ' ', @{ $_->[0] } ) };
my @sorted = map { join( ' ', @{ $_->[0] } ) } @sort;

When using the first line I got an error.


Code
foreach my $line (@lines) { 
$sth->execute($line, $line) or die "Can't execute SQL statement: $DBI::errstr\n";
my @all_rows;
while ( my $ref = $sth->fetchrow_arrayref()) {
push(@all_rows, $ref);
}

my @split = map { [$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;
my @sort =
sort {$a->[1] <=> $b->[1] ||
$a->[2] <=> $b->[2] ||
$a->[3] <=> $b->[3] ||
$a->[4] <=> $b->[4]} @split;
my @sorted = map { join( ' ', @{ $_->[0] } ) } @sort;
print Dumper @sorted;
}

RESULT SAMPLE
$VAR1 = '2008-07-26 10.10.10.1 3.3.3.3 80 hxxp://3.3.3.3 200 OK';
$VAR14 = '2008-07-26 192.168.1.32 11122 3.3.3.3 80 hxxp://4.4.4.4 200 OK';
$VAR29 = '2008-07-26 1.1.1.1 11122 3.3.3.3 80 hxxp://3.3.3.3 200 OK';



shawnhcorey
Enthusiast


Aug 21, 2008, 7:00 AM

Post #14 of 15 (4188 views)
Re: [perluser01] Sort array which contains sql results [In reply to] Can't Post


In Reply To
Hey Shawn thanks again for the reply/help. Unfortunately the results are still printing out unsorted. What I think the problem is that I am trying to apply the sort to an array_ref and not to the actual data? Does that sound right? Not sure, I've been looking at this so long that I am not sure at this point.


Code
        my @split =  map { [$_, split(/\./, (split /\s+/, $_)[1])]} @all_rows;



In this statement, $_ is a reference to an array. "(split /\s+/, $_)" does not make sense. It should be something like "(split /\s+/, $_->[191])" where 191 should be replace with the correct column index.

__END__

I love Perl; it's the only language where you can bless your thingy.

Perl documentation is available at perldoc.perl.org. The list of standard modules and pragmatics is available in perlmodlib.

Get Markup Help. Please note the markup tag of "code".


dontknowperl
Novice

Aug 21, 2008, 7:41 AM

Post #15 of 15 (4169 views)
Re: [shawnhcorey] Sort array which contains sql results [In reply to] Can't Post

Any other ideas?

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives