Counting Rows Returned by an SQLite3 Query in PHP

Tested with PHP 5.3.1

Strangely, when running queries against an SQLite3 database, there is no obvious way to know how many rows were returned by a specific query. After much hunting, I found a solution in the PHP manual, of all places.

Let’s say we have a table with 4 columns (id, seed, name, score), with 3 rows. The rest you need to know is in the code comments, really.

$db = new SQLite3('test.sqlite', SQLITE3_OPEN_READONLY); // opens our sqlite3 database, assigns it to $db
$result = $db->query('SELECT * FROM scores ORDER BY score ASC'); // runs the query against our database and returns all the reselts to $result
$count = 0; // sets up our $count variable
while (list() = $result->fetchArray(SQLITE3_ASSOC)) { // this loop will only run while results exist
	$count++; // if a result exists, adds to the $count
$result->reset(); // returns the array pointer back to the beginning, so we can access results in the order they were returned
if ($count > 0) { // if anything was returned by the query
	echo "<pre>id\tseed\tname\tscore"; // displays our comlumn headings
	for ($i = 0; $i < $count; $i++) { // the loop that will display the row data retrned by the query
		echo "<br>";
		$res = $result->fetchArray(SQLITE3_ASSOC); // pulls the one row into $res and automatically moves the $result pointer ahead by one
		echo $res[id]."\t".$res[seed]."\t".$res[name]."\t".$res[score]; // spits out the row to the browser
else die("scores--no results"); // nothing was returned by the query
/* browser output:
	id	seed	name	score
	22	1		Dangel	7
	23	1		Dangel	9
	21	1		Dangel	10	

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>