CODEWITHSUNDEEP
phpsqlite
Sandy
Sandy

Reputation: 312

Retrieve table names from sqlite3 database with php

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?

Upvotes: 1

Views: 2643

Answers (2)

Giacomo1968
Giacomo1968

Reputation: 26014

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

Upvotes: 2

esqew
esqew

Reputation: 44710

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

Upvotes: 5

Related Questions