Listing available tables in SQLite using php

<?php
$dbhandle = sqlite_open('db/test.db', 0666, $error);

if (!$dbhandle) die ($error);
    
$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";
$result = sqlite_query($dbhandle, $query, SQLITE_NUM);
if (!$result) die("Cannot execute query.");

while (sqlite_has_more($result)) {
    $row = sqlite_fetch_array($result);
    echo "table name is:        $row[0], 
          sql used to create :  $row[1]";
    echo "<br>";
}

sqlite_close($dbhandle);

?>

Output look like

table name is: scanftree,
sql used to create : CREATE TABLE scanftree(Id integer PRIMARY KEY,Name text UNIQUE NOT NULL)

Explanation

---

$query = "SELECT name, sql FROM sqlite_master WHERE type='table'";

We use the sqlite_master table to obtain the list of tables for the database. The name column of the sqlite_master table gives us the table name. The sql column gives us the SQL used to create that table.

while (sqlite_has_more($result)) {
    $row = sqlite_fetch_array($result);
    echo "table: $row[0], sql: $row[1]";
    echo "<br>";
}

The while loop goes through the rows of the result set. We use a new function. sqlite_has_more() returns TRUE if there are more rows available from the result set, or FALSE otherwise.