#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Catalonia - Spain
    Posts
    6
    Rep Power
    0

    Question 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?

    TIA,

    Ben
  2. #2
  3. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2002
    Location
    Washington, DC
    Posts
    2,692
    Rep Power
    22
    Lol - its more than capable. Send a standard query and use COUNT() to get your number of rows:
    Code:
    SELECT COUNT(*) FROM TABLE WHERE SOMETHING = SOMETHING
    SOMETHING = SOMETHING would be some condition...
    ~ Joe Penn
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Catalonia - Spain
    Posts
    6
    Rep Power
    0

    Unhappy 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.

    Yours,

    Ben
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Catalonia - Spain
    Posts
    6
    Rep Power
    0
    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.

  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Oslo, Norway
    Posts
    11
    Rep Power
    0
    "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).
  12. #7
  13. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Jun 2002
    Location
    Washington, DC
    Posts
    2,692
    Rep Power
    22
    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:
    Code:
    if ( mysql_num_rows( $query ) > 0 )
    {
          /* Do Something */
    }
    Is this what you are looking for - to see if 1 or more rows were returned?

    If so, this can be accomplished like this in fb/php:
    Code:
    $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 */
    }
    else
    {
          /* Will eval to false if query failed or 0 rows were returned */
    }
    It should be the same when using the pear methods also...
    ~ Joe Penn
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Catalonia - Spain
    Posts
    6
    Rep Power
    0
    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,
    BEN

IMN logo majestic logo threadwatch logo seochat tools logo