[DBI-users] how to count rows or put output to array?

Both questions are in the manual, which you can reread many times w/o
fully grasping all the details.

<snip>
my $dbh = DBI->connect('<your connect string>', $user, $pass);
my $sql = 'Select field1, field2 from temp';
my $sth = $dbh->prepare($sql);

$sth->execute();

while (my @row = $sth->fetchrow_array()) {
print join("\t", @row), "\n";
}

$dbh->disconnect();
In the above code use:

my $sql = 'SELECT COUNT(*) FROM temp';

or:

my $i = 0;
while (my @row = $sth->fetchrow_array()) {
$i++;
}

HTH,
Paul

yes - but this will result in a additional select and therefor more database
activity :-(
very difficult in my code... i will have a look if i can change the code.
isn't it possible without a "while" ?


Alex

Not a huge cost if it's against an index....
How did you plan on *retrieving* the data if not through a loop? If you
plan on actually getting all the data, you'll have to loop through all the
rows I think. Maybe you could post a code fragment?

code.
here is the old code... based ob a file

my @data=split(/\r?\n/,$part[2]);

if ($data[2] ne '') {
my @datas=split(';',$data[2]);
print "Counted Items ";print $#datas+1;print" Items<BR>\n";
print "Inside: ";foreach (sort @datas) {print "Item: $_ ";} print "<BR>\n";
}



Alex

Not sure I see your problem. Rather than splitting from a file, you're
selecting from an array. Instead of doing a foreach{} loop you just do a
while loop. Doesn't change the logic at all.

my $sql = 'SELECT field FROM DATA ORDER BY field';
my $sth = $dbh->prepare($sql);
$sth->execute();

while (my $row = $sth->fetchrow_array()) {
$i++;
$html .= "Item: $row ";
}

print "Counted Items $i Items<BR>",
"Inside $html<BR>";

oh *g* - thats the idea :-) - a point for appending the $html string... i
will change this... sometimes if you look too long on a code you get
blockhead... :-)


thx
Alex

If you only need to get the table size,
musql_affected_row is the function to call,
i'm not sure of the function name under the perl
APi,

But you may need more, I got exactly that problem when trying to select a
few items
from a database while displaying only a few of them
(take as example google and the pages system)


It happens basically you select items using a MAX option
(saying that you want only MAX items, and a starting item options
(something like LIMIT 10 30 output up to 30 rows starting from row 10),
note that in such cases the order
is relevant.


In this case some databases allow you to access the total number
of selected items before the conditionnal
on the maxnumber (and starting item)

As example with Mysql 4 some function (select found_rows()) return this
number (which is different from the number of rows in the table that you
get).

mysql_affected_rows returns the size of the table that you get,
or the number of rows affected by an update, delete or anything.


Note that if you use a count(*) you will get troubles
if you also use a "group by" modifier,

Select count(*) from Student, Class where Student.ID=Class.StudentID
and Class.Name like "Maths%" group by Student.ID
will return a table where for each sutdent we find the number of
Maths courses she/he attempts (Eg if we have Maths1 and Maths2 etc ..)
and not the total number of row in the resulting table.

Of course, you can do it all with array references if your a glutton for
punishment. I can never remember how and always have to look it up with
working code.

my $rs = $dbh->selectall_arrayref("select * from table");
# count
print "count: $#$rs\n";
# each row
my $rr;
foreach $rr (@$rs) {
print "col1: $rr->[0] col2 $rr->[1]\n";
}

The book is a useful reference, too.

Dave

On Jan 29, alex scribed:

one user told me - and i read about, too. this row counter will not give
correct results on some database servers... i'm not sure if mssql will work
correctly, but this may create problems if you like to program platform
independend.


Alex

Shouldn't you look into "selectall_arrayref", then you would get all
rows as an arrayref, and each element of this is an arrayref to all
the columns returned.

my $aryref = $dbh->selectall_arrayref("SELECT * FROM temp");
if(!defined $aryref and !@$aryref) {
print "Nothing to show!\n";
die; # or preferbly something else
}
print scalar @$aryref, " rows found\n";

#The lines are:

foreach my $cols (@$aryref) {
print "Col:",join(";",@$cols),"\n";
}

# You might use map instead, but it is a sort of loop anyhow.

# Untested!

... and I usually do a map iterating over the col values to set undef
cols to ex. "" like:

my @cols = map {!defined($_) && $_ = '';$_} @$cols;

... still after memory, and untested!