www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: [RESOLVED] mysql_num_rows equivalent for sqlite3?

  1. #1
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62

    resolved [RESOLVED] mysql_num_rows equivalent for sqlite3?

    Hi, everyone,

    In a dim and distant past I wrote my own database abstraction layer, with the vague idea that one day that might prove useful in using different RDBMS's. It's trivial, really - it lets you do a dbQuery() or a dbNumRows() which then gets translated to mysql_query() and mysql_num_rows() respectively. The idea here was obviously that the mysql specific calls could later easily be changed into something else.

    Today is the moment of truth: I need to come up with a way to use SQLite 3 as a drop-in replacement for MySQL. (Yes, the SQL dialect will be different of course, and there will be different limitations based on the 'lite' aspect of SQLite, but that's a fact of life.) So far it's going well and I have converted most of the MySQL stuff to SQLite3 already.

    But now I'm running into the fact that no equivalent of mysql_num_rows() has been inmplemented in the SQLite3 support of PHP5. The thing is, mysql_num_rows does not advance the row pointer. In other words, after calling mysql_num_rows() you can still do something like
    Code:
    while ($row = mysql_fetch_array($qid))
      var_dump ($row);
    but offhand I cannot see a good way to do this in SQLite3. Note that the idea here is to pass a result resource from a previous SELECT query to the num_rows() function, as one would do with mysql_num_rows(). When all is said and one I need to be able to call my own dbNumRows($qid) the same for MySQL and SQLite - I have a lot of legacy code that uses it.

    Suggestions anyone? Thanks - all replies are appreciated!

    // Frank
    Last edited by frankvw; 03-25-2011 at 02:54 PM.

  2. #2
    Join Date
    Jul 2007
    Location
    Wisconsin
    Posts
    468
    I'm not familiar with SQLite3, so I don't know what functions are all available, compared to mysql. It's a little more resource-intensive, depending on how many rows are returned... but

    PHP Code:

    function dbNumRows($result$database)
    {
    // $result would be the return from your dbQuery() function
    // $database allows for "mysql", "sqlite3", or whatever rdbms you need

        
    if ($database=="mysql")
        {
            
    $return mysql_num_rows($result);
        }
        else if (
    $database == "sqlite3")
        {
            
    $rc 0;
            while (
    $result// don't know what syntax you need here to grab the individual row from the result set
            
    {
                
    // loop through the rows returned, counting as you go.
                
    $rc++;
            }
        
    $return $rc;
        }
        else
        {
            return = 
    FALSE;
            exit;
        }
        return 
    $return;


  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,338
    If you do something where you loop through a SQLite3Result object to count the rows, you could then use the reset() method to reset the row counter back to the first row. Or maybe you could do your fetch loop and dump the result rows into an array, then just count() the array. Then you could loop on the array to actually fetch the results, avoiding having to do another fetch loop. Whichever way you go, you could extend the applicable SQLite3 classes so that you can incorporate this within the class, perhaps.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    I haven't used sqlite, but the documentation at php.com might be helpful: http://php.net/manual/en/ref.sqlite.php

    In particular, http://www.php.net/manual/en/functio...e-num-rows.php

    Is that what you need?
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,338
    Except he's using the SQLite3 classes.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    touche.

    EDIT: I'm surprised to see a technology drop a [core] feature like that ...
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,338
    Quote Originally Posted by svidgen View Post
    touche.

    EDIT: I'm surprised to see a technology drop a [core] feature like that ...
    Only a guess on my part is that maybe the PHP implementation is incomplete? But I've never worked with SQLite so don't really know much about it at all.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62
    Quote Originally Posted by OctoberWind View Post
    // loop through the rows returned, counting as you go.
    $rc++;
    The problem with that is that if you do that after a select it _will_ return the number of rows, but it will also advance the row pointer, which mysql_num_rows does not.

    // FvW

  9. #9
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62
    Quote Originally Posted by NogDog View Post
    If you do something where you loop through a SQLite3Result object to count the rows, you could then use the reset() method to reset the row counter back to the first row.
    I have had a look at that, but I'm not sure I understand its behaviour fully - especially whether or not that only works with prepared statements. But that's more a SQLite issue than a PHP matter, I suppose.

    I'll do some more homework and fiddle with it. If that doesn't work, I'll have to come up with a really kludgy work-around (such as storing the last SELECT query, and massaging it into a COUNT() query) but that's a last resort kind of thing. :-)

    // FvW
    Last edited by frankvw; 03-27-2011 at 08:19 AM.

  10. #10
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62
    Quote Originally Posted by NogDog View Post
    Only a guess on my part is that maybe the PHP implementation is incomplete? But I've never worked with SQLite so don't really know much about it at all.
    It appears that the PHP implementation isn't the problem - the feature seems to have been dropped from the SQLite3 API. It is not considered a PHP bug, because

    "SQLite and SQLite3 have different APIs. SQLite3 doesn't implement buffered results so the total number of results isn't available. This is down to the fact that libsqlite really doesn't implement it either and its emulated by the old SQLite extension."

    But the general consensus seems to be that while these may be good and valid reasons for dropping this feature, it is sorely missed.

    // FvW

  11. #11
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,338
    The reset() method just sets the result row pointer back to 0 (the first result row). So after you loop through the results to get the count, calling the reset() method will make it so that if you loop through the result again, it will restart from the first result row.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  12. #12
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62
    Quote Originally Posted by NogDog View Post
    The reset() method just sets the result row pointer back to 0 (the first result row). So after you loop through the results to get the count, calling the reset() method will make it so that if you loop through the result again, it will restart from the first result row.
    OK. With that you have already helped me along a great deal. So thank you!

    However, there is still a problem. With MySQL one could do something like:

    PHP Code:
    $qid mysql_query ('SELECT * FROM tbl1');
    $if (mysql_num_rows ($qid)) {
      
    $row1 mysql_fetch_array ($qid);
      
    var_dump ($row);     // Output: contents of row 1
    }
    echo 
    mysql_num_rows ($qid) . "\n";          // Output: n
    while ($row mysql_fetch_array)
      
    var_dump ($row);                               // Output: contents of row 2-n 
    (code not tested or syntax-checked)

    Now the SQLite3 equivalent for mysql_num_rows() in my own abstraction layer looks like this:

    PHP Code:
    function dbNumRows ($qid) {
      
    $numRows 0;
      while (
    $rowR dbFetchArray ($qid)
        
    $numRows++;
      
    $qid->reset ();
      return (
    $numRows);

    While this is an ugly kludge, slow and heavy and very expensive in terms of system resources, at least it works... So given no alternative I am willing to accept that for the sake of backward compatibility for now.

    However, the real issue is that the above will behave differently from mysql_num_rows() in one significant aspect:

    PHP Code:
    $qid dbQuery ('SELECT * FROM tbl1');
    $if (dbNumRows ($qid)) {
      
    $row1 dbFetchArray ($qid);
      
    var_dump ($row);     // Output: contents of row 1
    }
    echo 
    dbNumRows ($qid) . "\n";          // Output: n - 1
    while ($row dbFetchArray)
      
    var_dump ($row);                               // Output: contents of row 1-n 
    (code not tested or syntax-checked)

    Note the difference: the while() loop at the bottom retrieves rows 2-n in the first example but 1-n in the second, while the second call to dbNumRows() returns n-1 rather than n.

    So. What I would need here is a way to preserve the row pointer, then $db->reset() it to that previous state instead of to zero.

    I could of course maintain a counter which is updated by dbFetchAffray() and friends... but if there is a more elegant (more efficient, better, simpler, etc.) way I would love to do that instead.

    Any suggestions? :-)

    // Frank

  13. #13
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,338
    How about approaching it this way by extending the SQLite3 class. (I don't have SQLite3 installed, so did not test it.)
    PHP Code:
    <?php
    class MySQLite3 extends SQLite3
    {
       public 
    $numRows;
       public function 
    query($sql)
       {
          
    $result parent::query($sql);
          
    $this->numRows 0;
          while(
    $result->fetchArray()) {
             
    $this->numRows++;
          }
          
    $result->reset();
          return 
    $result;
       }
    }

    $db = new MySQLite3();
    $result $db->query('select * from foo');
    if(
    $db->numRows) {
       
    $row $result->fetchArray();
       
    // do something with $row
    }
    echo 
    $db->numRows;
    while(
    $row $result->fetchArray()) {
       
    // do other stuff with $row
    }
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  14. #14
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    It may be prudent to perform a set of benchmarks here. Benchmark a corresponding count(*) query as compared to looping through the results of the "source" query once. If there's no performance benefit to doing so, the optimal way to get what you need is probably to simply export the rows into an array immediately, and then operate henceforth on the array instead of the result handle.

    Benchmark this:
    PHP Code:
    // count the rows first -- you can bypass additional processing if you
    // know you won't be getting any rows back 
    $db = new MySQLite3();
    $result $db->query('select count(*) from foo');
    while(
    $row $result->fetchArray()) {
       
    // do other stuff with $row
       
    $numrows $row[0];
    }

    if (
    $numrows 0) {
      
    $result $db->query('select count(*) from foo');
      while(
    $row $result->fetchArray()) {
         
    // do other stuff with $row
         
    process($row);
      }

    ... against this:

    PHP Code:
    $db = new MySQLite3();
    $result $db->query('select * from foo');
    $rows = array();
    while(
    $row $result->fetchArray()) {
       
    // do other stuff with $row
       
    $rows[] = $row;
    }

    if (
    sizeof($rows) > 0) {
      foreach (
    $rows as $row) {
        
    process($row);
      }

    Since I tend to use all of my returned rows anyway, I would just default to the 2nd option for myself -- escape that kludgy result reference interface as soon as possible and just deal with an array.
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  15. #15
    Join Date
    Jan 2006
    Location
    South Africa
    Posts
    62

    Benchmark this? :-)

    Quote Originally Posted by svidgen View Post
    Benchmark this:
    I couldn't agree with you more. Iterating through a loop to retrieve rows and count them, then discard them, is so wasteful in terms of system resources that it's not even funny any more.

    Worse, in order to do it right (i.e. be reliable, accurate and not causing other code to break) has proven very cumbersome as well. It requires saving the last query in a global variable, updating a counter whenever an SQL operation is performed that advances the row pointer, and what not. Something that complicated is bound to become a major pain in the backside sooner or later.

    So I have abandoned that idea for now and gone the COUNT(*) route. This involves taking the last SELECT query and massaging it to return the number of rows. So, if the last query is something like this:

    Code:
    SELECT itemid, subject, datetime(time, 'unixepoch')
    FROM entries
    WHERE itemid > 10
    AND itemid < 20
    OR itemid = 200
    then all I have to do is replace everything between the SELECT and FROM with COUNT(*):

    Code:
    SELECT COUNT(*)
    FROM entries
    WHERE itemid > 10
    AND itemid < 20
    OR itemid = 200
    Which should be a simple regexp. BUT... when queries become more complex (what with compound selects, limit and offset clauses, join ops and what not) I am not at all confident that the results obtained this way will still be accurate. Some serious testing would seem warranted. :-)

    // Frank

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles