$sth->rows() is what you want, but you should be advised that $sth->rows() is not guaranteed to give you an accurate count until you fetch all of the rows in the result set. With MySQL you should be fine calling $sth->rows() before you fetch all of the rows from the database unless you set the mysql_use_result attribute on the sth, but then again you would have the same problem with php if you used mysql_use_result().

$sth = $dbh->prepare($sql);
while (my @results = $sth->fetchrow_array) {
    do_something(@results)
}
my $count = $sth->rows();
[download]

You probably will also want to 'use strict' and 'use warnings' in your code. They will make your code easier to debug because they keep you from shooting yourself in foot in some of the most common ways. One other thing you might want to look at is Template-Toolkit or HTML::Template both of wich will allow you to remove the html from you code, so that your program will be easier to read.

I guess I'm not being clear, excuse me.
I need the count before hand, not after. So I guess what I'm doing now is as good as it gets.
Thanks though.
dstefani

BTW: (strict, -w) all on :-) ...chunk of code...

While I'm still not sure why you need to know how many rows are being returned, you should look at fetchall_arrayref in DBI (look in the section 'Statement Handle Methods'). This will return your entire recordset into an array of array references (one per record).

In your original post, you mentioned that you are using the number of records returned to format your table when you paint the results. Do you really need the record count beforehand or can you define your rules for making your results look nice and use them dynamically while you work through your record set?

Oren

As tantarbobus as mentioned already, you can't have a count of rows beforehand with MySQL, unless you make sure you are using mysql_store_result (which is the default mode with DBD::mysql, BTW).

In PHP it's the same story. PHP is hiding complexity from you. If the method mysql_num_rows() works, it's only because PHP uses always mysql_store_result(). If you read the relevant explanation on MySQL manual you'll see that a count of rows is only possible after you are finished fetching them.

That said, If you want to be sure of the row number and you don't want to use a separate COUNT(*), then the only way is to use one of the "fetchall_*" or "selectall_*" methods from the DBI.

my $sth = $dbh->prepare($sql);
$sth->execute || $error->LogError("whatever ".$dbh->errstr, 0);
my $recs = $sth->fetchall_arrayref;
my $count = @$recs;
[download]

Once you have the records stored in an array, the number of records is given, as for any array in Perl, testing the array in scalar context.

Thank you all for your input.
I'm trying to learn accurate and elegant ways of coding, although at this point I'm about as elegant as my two year old with a bowl of soup.

I do appreciate your patients with a newbie.

Thanks,
dstefani

I've found that I've always been able to find a way to only need to know the number of rows after I've fetched them, no matter what language I was using. You might want to re-think your algorithm with that in mind.

Michael

## chunk 'O' code
@sql = (
    "SELECT COUNT(*)",
    "FROM sc_products WHERE name LIKE ? AND keywords LIKE ?"
);
@args = ("%$search_name%", '%Color%');

$sth = $dbh->prepare(join ' ', @sql);
$sth->execute(@args) 
    || $error->LogError("Error opening database connection f
+or product count in search.cgi".$dbh->errstr, 0);

# get my count
my ($count) = $sth->fetchrow_array;
$sth->finish;

$sql[0]  = "SELECT name, image2";
$sth = $dbh->prepare(join ' ', @sql);
$sth->execute(@args) || $error->LogError("Error opening database conne
+ction f
+or product count in search.cgi".$dbh->errstr, 0);

while(my @results = $sth->fetchrow_array)
{
## Use my count in here for some cool table formatting...    
print "<tr><td class=\"imgs\">...
}

$sth->finish;
$dbh->disconnect();
[download]