Using SQLite3 in PHP How to Count the Number of Rows in a Result Set

Using SQlite3 in PHP how to count the number of rows in a result set?

From the documentation:

public bool SQLite3::exec ( string $query )

Executes a result-less query against a given database.

This methods returns a boolean, not a result set. When you convert true to an integer it will become 1.

You should use SQLite3::query(). Example (untested):

$rows = $result->query("SELECT COUNT(*) as count FROM USERIDS");
$row = $rows->fetchArray();
$numRows = $row['count'];

---

Btw, naming the instance of the SQLite3 class $result can be misleading (especially in a DB environment). I would call it $db or $connection.

Count number of rows buffered in sqlite result set

As per your comment if you just want to return the count of records you could wrap your query in a SELECT COUNT(*) and change $dbhandle->query to $dbhandle->querySingle. This will work with or without LIMIT.

$dbhandle = new SQLite3("sqlitedb_111.db");
$selQuery1 = "SELECT COUNT(*) FROM (SELECT id,dbname,tabname,fieldname FROM scan_results ORDER BY id ASC LIMIT  0,10)";
$resQuery1 = $dbhandle->querySingle($selQuery1);
print count($resQuery1);

SQLite3 count rows

That should do it.

function products() {
    $loggedin = loggedin();
    $db = db();
    $games = $db->query('SELECT game_id, name, developer, price FROM game WHERE quantity > 0 ORDER BY game_id DESC');

    if (empty($games->fetchArray())) {
        echo "Sold out";
        return;
    }

    // Reset the result back to the first game
    $games->reset();

    while ($game = $games->fetchArray(SQLITE3_ASSOC)) {
        echo '<p>'.$game['name'].'</p>';  
    }
}

SQLite vs MySQL -rowCount()

rowCount() is only really safe to use on MySQL and a few others. It doesn't work well with portable applications like SQLite.

From the manual:

If the last SQL statement executed by the associated PDOStatement was
a SELECT statement, some databases may return the number of rows
returned by that statement. However, this behaviour is not guaranteed
for all databases and should not be relied on for portable
applications.

---

Related Topics