SQLite3 Query to list all tables in database only shows one table

3
votes

I need to be able to read all the tables in an SQLite database without knowing what the tables will be called. I'm working on the following example.db

chinhook.db

Tables

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track.

Currently, I'm using the following...

<?php
    $db = new SQLite3('db/chinhook.db');
    $tablesquery = $db->query("SELECT name FROM sqlite_master WHERE type='table';");
    $tables = $tablesquery->fetchArray(SQLITE3_ASSOC);

    foreach($tables as $name){
       echo $name.'<br />';
    }
?>

I can only ever seem to get the name of the first table. I can call data from the other tables no problem, but the sqlite_master table only contains one of the table names. Others online seem to report this method as successful. Any Ideas?

Thanks in advance.

4
phpsqlite
What does fetchArray() method do? - Your Common Sense
Standard SQLite3 method for fetching the results of a query and placing them into either a numerical or associative array (or both). Similarly to the way mysql_fetch_array does. php.net/manual/en/sqlite3result.fetcharray.php - Mike
So, how many records it is supposed to fetch into a numerical or associative array? - Your Common Sense

4 Answers

10
votes

Try this:

<?php
    $db = new SQLite3('db/chinhook.db');
    $tablesquery = $db->query("SELECT name FROM sqlite_master WHERE type='table';");

    while ($table = $tablesquery->fetchArray(SQLITE3_ASSOC)) {
        echo $table['name'] . '<br />';
    }
?>
0
votes

In PDO Method 

$db  = new PDO('sqlite:Northwind.db');

$sql = "SELECT  `name` FROM sqlite_master WHERE `type`='table'  ORDER BY name";
  $result = $db->query($sql);
      if($result){
        while($row = $result->fetch(PDO::FETCH_ASSOC)){
          echo '<li>'.$row['name'].'</li>';
        }
    }


?>
0
votes

FYI, if you need to parse the returned sql

I implemented a sqlite table parser for it:

https://github.com/maghead/sqlite-parser

-1
votes
<?php
   $db = new SQLite3('db/chinhook.db');
   $tablesquery = $db->query("SELECT sql FROM sqlite_master WHERE name='foo'");
   $table = $tablesquery->fetchArray();
   echo '<pre>'.$table['sql'] . '</pre>';
   $db->close();
?>