I've got the following code:

$sqlsrchresults = "SELECT * FROM geog WHERE name_en LIKE '%$A::name_en%' ORDER BY name_en";

$sth = $dbh->prepare($sqlsrchresults);
$sth->execute;

I need to return the number of results. I've tried:

$no_of_rows = $sth->numrows;

Which doesn't work. I don't really want to run another sql statement, COUNT() (which does work), because of performance reasons.

Is there a way, surely?!!, to do this?

Thanks

Wil

$no_of_rows = $sth->rows;

BTW, don't use "select *", if you just want to count the results, a "select id from ..." is much faster than returm all fields.

Thanks for that. Works perfectly. I can't find any Perl/SQL reference on the web. It seems like I'll have to dig into my pockets to order a book. Has anyone found any useful reference on the web?

I don't quite understand your second statement. I don't think this applies to me. I'm actually searching the whole database for fields that match a search criteria. I just needed to return the number of results returned on the top of the page.

When returning individual results, I am using SELECT $id FROM talbe_name, if that's what you meant.

Thanks

Wil

Have you had a look at "A Short Guide to DBI"?
http://www.perl.com/pub/a/1999/10/DBI.html

It is, as it says, short (and basic), but may work as a starting point for you.


Matt G

Yes, thank you Matt. I'm in more of a need of quick reference material. Like those in a nuttshell books. There, I said it; I've got to get a book!

Thanks anyway!

Wil

http://www.mysql.com

has references listed in its site (if you are using MySQL as your
database storage application).

Perldocs is a great web site as already mentioned by others.
========================================
Buh Bye!

Cheers,
Me

Yeah, I've been snooping around the mysql.com and msql web site. What I'm really after is more Perl/SQL tutorials, articles etc. There seems to be plenty of PHP/SQL articles out there. It seems that PHP is the scripting language of choice when it comes to SQL and database work these days then?

Cheers

- wil

Actually, don't use "select id" if all you want is a count, but rather use "select count(*)" instead, and then do:

my ($numrows) = $sth->fetchrow_array;

Be careful about using ->rows() to get results. Look at the warning in the DBI docs.

Cheers,

Alex
--
Gossamer Threads Inc.

Thanks for the heads up re ->rows().

However, I need a COUNT of the number of results returned. Surely a pure COUNT(*) by itself would return the number of results in the database. I need the number of filtered results.

Thanks

Wil

I'll just post this from the DBI manual for other lurkers.

Generally you can only rely on a row count after a do() or non-select execute(). Some drivers only offer a row count after executing some specific operations (e.g., update and delete).

It is generally not possible to know how many rows will be returned from an arbitrary select statement except by fetching and counting them all. Also note that some drivers, such as DBD::Oracle, implement read-ahead row caches for select statements which means that the row count may appear to be incorrect while there are still more records to fetch.

You can count using the same conditions as any other select:

$sqlsrchresults = "SELECT COUNT(*) FROM geog WHERE name_en LIKE '%$A::name_en%' ORDER BY name_en";

/Csson

Yes. but the you are executing two queires on the databse and slowing down your pogram.

Wil

- wil