Count Number of Records in sqlite3 recordset

Problem: I was using sqlite 2.1 db with php using SQLiteDatabase object in PHP, now I am upgrading to sqlite 3.x, so also want to use SQLite3 in PHP, There is an issue i'm facing that is pr开发者_JAVA技巧eviously in SQLiteDatabase Object i was easily counting number of records return by querying to databse, but now with SQLite3 i am unable to do the same.

Sample Old Code with SQLiteDatabase:

$base= new SQLiteDatabase('users.db');
$query = 'select id from users';
$results = $base->query($query, SQLITE_ASSOC);
echo sizeof($results);

Output this code generating

50

Sample Old Code with New SQLite3:

$base = new SQLite3('users.db');
$query = 'select id from users';
$results = $base->query($query);
echo sizeof($results);

But above code returns this:

1

Instead of 50 it is showing 1,

Q: How i can calculate the number of rows in recordset($results) in SQLite3?

I don't know the particualr API you're using, but chances are that $results there is holding just a "true" meaning that the query is being processed, I don't know how do you get your results, check your API. I think this is the link http://us.php.net/manual/en/book.pdo.php

apparently you have to do something similar to $rows->fetch(PDO::FETCH_OBJ)->rows; but in any case you need to fetch the results from the object.

Read your API.

Good luck

I just found an interesting article on how to do this. It's a bit of a bodge, but it works. Basically, use PDO with SQLite3 instead of using it directly. PDO gives you a 'fetchAll()' method that you can then call count() on.

Reference: http://blog.digitalneurosurgeon.com/?p=947

If you don't need the result itself, but just the number of rows, I recommend having SQLite do the count, i.e.:

$count=$base->querySingle("SELECT count(id) FROM users") 

will store the number of rows in the user table in the $count variable.

Apart from that, looping through a $results->fetchArray() and increment a counting variable will work.