Hello, I have what must surely be a frequently-asked question, but I've looked everywhere I could think of and haven't found it really addressed.
I'm currently using DBD::MySQL and DBD::CSV (mostly for testing, it's a long story) to retrieve records from an organization's contact database in a CGI script. Occasionally, legitimate searches will return a large number records, so naturally I'm implementing a batch-type interface. My Perl code creates SQL queries that include a "LIMIT" clause so that no more than 20 records will be returned. So far, so good, so obvious. It makes a lot of sense from a user interface point of view to show the web user the _total_ number of records that match the search criteria, and possibly, calculated from that, the number of batches (think Google). However, all I can find in the DBD documentation is the rows() method, which, in face, only returns the number of rows returned by the current query -- which in this case will never be more than 20. I'm guessing most of you will not be surprised by this. I realize that I can do a second query without the "LIMIT" clause to get the total number of rows; I can even use "SELECT count(id)" so that it just returns one row with one value in it, the number of records. However, doing a second query has significant overhead, and in the case of the DBD::CSV driver, takes just as long as the first query -- which is a significant amount of time, believe me. I'd really like to know a better way to do this. Either to get the total number of rows, or perhaps a faster way to run queries on a CSV file since I may actually need to run this script on a 1400-record data file on a server with no database driver (don't ask). Even with the MySQL driver, it seems like there should be a clean way to get the total number of matches for a "LIMIT"ed query. I realize that there are constraints based on the database itself (this may not be adequately addressed by MySQL itself), and although I vaguely remember reading about a special MySQL command to get this information, I realize that a) that solution would still essentially require a 'second query', and b) not all databases probably implement it. However, it seems that this (total matches) would be a fairly important piece of information to make available to users of the DBD library. Don't many database apps need to do queries in 'batches'? Wouldn't the vast majority of those need to know the total number of matches? And wouldn't having to do a second query be a significant slowdown for most CGI apps, especially if the database server is, as it often is, on a separate server? It seems like this would be worth tweaking database drivers for. If I've missed something obvious, please let me know. As I said, I've searched everywhere, read the doc for DBD and both drivers, and what I could find on the dbi.perl.org site -- but I definitely could have missed something. Many thanks, AM Thomas
