CODEWITHSUNDEEP
phpsqlite
Mike
Mike

Reputation: 117

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

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.

Upvotes: 5

Views: 19914

Answers (4)

c9s
c9s

Reputation: 1917

FYI, if you need to parse the returned sql

I implemented a sqlite table parser for it:

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

Upvotes: 0

Naveen DA
Naveen DA

Reputation: 4390

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>';
        }
    }


?>

Upvotes: 0

Gil
Gil

Reputation: 39

<?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();
?>

Upvotes: -1

ciruvan
ciruvan

Reputation: 5213

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 />';
    }
?>

Upvotes: 16

Related Questions