June 27th, 2003, 03:39 AM
Knowing Number of registers from a Select
Is there any way to know the number of registers that will come out from a given select?
I'm using PHP and PEAR abstraction layer with Firebird 1.0.3, and I tried query->numRows() but i got a message telling me that "db is not capable". Is that true that interbase is not capable?
June 27th, 2003, 04:16 AM
Lol - its more than capable. Send a standard query and use COUNT() to get your number of rows:
SOMETHING = SOMETHING would be some condition...
SELECT COUNT(*) FROM TABLE WHERE SOMETHING = SOMETHING
~ Joe Penn
June 27th, 2003, 04:52 AM
Any other way?
That requires two selects, one to know number of reg's, and another to get the result set, knowing that my select takes a large amount of time that wouldnt be an option.
June 27th, 2003, 07:55 AM
It's not a problem with firebird, rather a limitation of the php api. The IB api for php seems a bit immature compared to those for other RDBMS.
I would suggest storing the result set to an array and counting the array.
June 27th, 2003, 10:06 AM
Ok, so there is no way to know it directly from the api. But, is it possible from let's say DB Express components... , or even, is firebird capable of knowing the number of reg's from a select without having feched all of them (and without doing count(*)).
Thank you all.
June 27th, 2003, 06:43 PM
"No way" is a very dangerous answer to give, but it is temping. How could it know without counting them, taking into account that the correct answer could depend upon which transaction it was executed within?
Generally, doing a SELECT COUNT is an expensive operation in a client/server database, and may in many cases indicate that the person wanting to count rows (at least if we're talking about a number higher than the number of rows that fit onto the screen) still thinks in terms of desktop databases.
My advice is to limit your counting to situations were they are really needed (a progress bar is not such a situation) or for doing the count during or after the retrieval (I don't know PHP, but IBO does have a property named ReturnedRows or something).
June 27th, 2003, 08:23 PM
Unfortainatly, as Rod pointed out - the php/ibase api is missing quite a few of the handy functions found in php/pg and php/mysql. What do you need a count for? I see alot of people getting row counts and using them in conditionals:
Is this what you are looking for - to see if 1 or more rows were returned?
if ( mysql_num_rows( $query ) > 0 )
/* Do Something */
If so, this can be accomplished like this in fb/php:
It should be the same when using the pear methods also...
$query = ibase_query( /* The Query String */ );
Check To See If Scalar Is Resource
if ( is_resource( $query ) )
/* Evaluates to true if 1 or more rows were returned */
/* Will eval to false if query failed or 0 rows were returned */
~ Joe Penn
I need to exactly know the number of returned rows, I want to put a muli page list of records. So i need to know the number of rows to generate links at bottom of the page to navigate between the pages.
And yes, select count(*) is too expensive in time to use it.
I guess I'll have to wait for maintainer to implement returnedRows functionality on ibase api.
Thank you again,