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

Question

currently I am using:

$result = new SQLite3(sprintf("users/USERIDS_DB.sqlite"));

$numRows = $result->exec ("SELECT count(*) FROM USERIDS");

echo sprintf("the number of rows are: %d", $numRows);

but the result is 1 when it should be 6 (the number of rows I created using firefox sqlite3 addon)

Can anybody help please?

Answer 1

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.

Answer 2

$db = new SQLite3('filename.db3');
$count = $db->querySingle("SELECT COUNT(*) as count FROM tablename");
echo $count;

Answer 3

$result = $db->query("SELECT * FROM db_name")
$row=$result->fetchArray(SQLITE3_ASSOC);
    // check for empty result
    if ($row != false) {
      // do something here if record exists
    }

Answer 4

<?php
    function SqliteNumRows($query){
        $numRows = 0;
        while($rows = $query->fetchArray()){
            ++$numRows;
        }
        return $numRows;
    }
?>

This really helped me it works actually you may try it