Retrieve table names from sqlite3 database with php

Question

I have a working version of this code as below which the result can be seen here.

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

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

The issue is I only have two tables in this database USERS and ADMIN_LOGIN but I am getting sqlite_sequence appearing. Is there a way I can only show the table names and not sqlite_sequence?

esqew · Accepted Answer · 2014-05-10 04:08:51Z

5

Use PHP to check if the table is named sqlite_sequence, and if it isn't then output the table's name.

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

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

answered May 10, 2014 at 4:08

esqew

45k28 gold badges139 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giacomo1968 · Accepted Answer · 2014-05-10 15:33:39Z

2

What about changing your initial query to ignore sqlite_sequence as a name != 'sqlite_sequence condition?

// Display all sqlite tables
$db = new SQLite3('data.db');
$sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'sqlite_sequence';";
$tablesquery = $db->query($sql);

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

Or you can skip all tables with sqlite* prefix like this using NOT LIKE:

// Display all sqlite tables
$db = new SQLite3('data.db');
$sql = "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite%';";
$tablesquery = $db->query($sql);

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

edited May 10, 2014 at 15:33

answered May 10, 2014 at 4:20

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

1 Comment

CL. Over a year ago

You should skip all tables beginning with sqlite_ (such as the sqlite_statX tables).

Collectives™ on Stack Overflow

Retrieve table names from sqlite3 database with php

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related