Once You Know, You Newegg

Get two (or more) rows into array

This method uses Perl, mySQL and fetchrow_hashref.
This will allow you to get two or more rows from a mySQL database and have each row with its’ columns available for use later.

Per Row Method: We can get row1 and have access to all its variables like:
$cities[0]->{‘city’} is city from first row.
$cities[1]->{‘city’} is from the second row.
Or
Per Column Method: Each column can be in its’ own array and accessed like:
$all_cities[0] is first city in @all_cities array.
$all_cities[1] is the second city.

So, you can write:

my @cities;
while ($results = $query->fetchrow_hashref)
{
push @cities, $results;
}

and then perform any other assignments you want:

my $city = $cities[0]->{'city'}; # city from first row
my $city1 = $cities[1]->{'city'}; # city from second row

or:

my @all_cities = map { $_->{'city'} } @cities;

my $city = $all_cities[0]; # city from first row
my $city1 = $all_cities[1]; # city from second row

SO…..

#############################################################

#!/usr/bin/perl -w
use strict;
use CGI;
use CGI ‘:standard’;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
print CGI::header(-expires => ‘-1d’);
use DBI;
$|=1;

use vars qw($getstate $hookup $query $results $city $state $pages $category $lastupdate $progress $actual $city1 $state1 $pages1 $category1 $lastupdate1 $progress1 $actual1 @cities $city $city1 $state $state1);

$getstate = ‘Arizona’; ## actually comes in from a query eg: $getstate = shift;

my ($moe,$larry,$curly) = hookup(); ##the sub below
my $hookup = DBI->connect($moe,$larry,$curly) or die “$DBI::errstr”;

$query = $hookup->prepare(qq{SELECT `city`,`state`,`pages`,`category`,`lastupdate`,`progress`,`actual` FROM `statesettings` WHERE `pages` > `progress` AND `state` LIKE ? ORDER BY `lastupdate` ASC LIMIT 2});
$query->bind_param(1, “%$getstate%”);
$query->execute() or die “Query did not execute”;
##### end if no data #######
if(!$query->rows) {
print “We have no data to process! All Done.\n”;
$query->finish;
$hookup->disconnect;
exit;
}
###########################
while ($results = $query->fetchrow_hashref){

## The Per Row Method ###
## optionally if I want to print them: ##
foreach my $col (keys %{$results}) { print “$col: “.$$results{$col}.”<br />”; }
#OR preferably this will print each row neatly whether you include all data or not.
print ‘City:’ .$results->{‘city’}.'<br />’;
print ‘State:’ .$results->{‘state’}.'<br />’;
print ‘Progress:’ .$results->{‘progress’}.'<br />’;
print ‘Actual:’ .$results->{‘actual’}.'<br /><br />’;
## end of print option ##

push @cities, $results;
}

my $city = $cities[0]->{‘city’}; # city from first row
my $city1 = $cities[1]->{‘city’}; # city from second row
my $state = $cities[0]->{‘state’}; # state from first row
my $state1 = $cities[1]->{‘state’}; # state from second row
## and so on
print qq~city $city ~;
print qq~city1 $city1 ~;
print qq~state $state ~;
print qq~state1 $state1 ~;
## and so on

##Now to get even better! Assign all the variables to their names in these two lines.

my ($city,$state,$pages,$category,$lastupdate,$progress,$actual) = @{$cities[0]}{qw/city state pages category lastupdate progress actual/};

my ($city1,$state1,$pages1,$category1,$lastupdate1,$progress1,$actual1) = @{$cities[1]}{qw/city state pages category lastupdate progress actual/};

## OR The Per Column Method ##

my @all_cities = map { $_->{‘city’} } @cities;
my @all_states = map { $_->{‘state’} } @cities;
## and so on
my $city = $all_cities[0]; # city from first row
my $city1 = $all_cities[1]; # city from second row
my $state = $all_states[0]; # state from first row
my $state1 = $all_states[1]; # state from second row
## and so on
print qq~b city $city ~;
print qq~b city1 $city1 ~;
print qq~b state $state ~;
print qq~b state1 $state1 ~;
## and so on

################################################################
$query->finish;
$hookup->disconnect;
exit;
############### connect to the database ######################
sub hookup {
my $moe = ‘DBI:mysql:databasename:localhost:3306’;
my $larry = ‘username’;
my $curly = ‘password’;
return (“$moe”,”$larry”,”$curly”);
}

Comments are closed.