PHP PDO + Sqlite, show indexes on table

Question

I'm trying to show the indexes on a table using sqlite and PHP.

The manual here ( https://www.sqlite.org/cli.html ) suggests:

.indexes ?TABLE?

However, using

$pdo->query('.indexes my_table');

Shows:

PDOException: SQLSTATE[HY000]: General error: 1 near ".": syntax error

Is it possible to execute these dot commands using PHP and PDO?

edit: example code:

    $this->pdo->query('DROP TABLE IF EXISTS test');
    $this->pdo->query('CREATE TABLE test (id INT PRIMARY KEY NOT NULL, col1 text NOT NULL, col2 text NOT NULL)');
    $this->pdo->query('CREATE INDEX col1 ON test (col1)');
    $result = $this->pdo->query('PRAGMA table_info(test);')->fetchAll(\PDO::FETCH_OBJ);
    print_r($result);

This does not show the indexes. The following errors:

    $this->pdo->query('DROP TABLE IF EXISTS test');
    $this->pdo->query('CREATE TABLE test (id INT PRIMARY KEY NOT NULL, col1 text NOT NULL, col2 text NOT NULL)');
    $this->pdo->query('CREATE INDEX col1 ON test (col1)');
    $result = $this->pdo->query('.indexes test')->fetchAll(\PDO::FETCH_OBJ);
    print_r($result);

This returns an empty array:

    $this->pdo->query('DROP TABLE IF EXISTS test');
    $this->pdo->query('CREATE TABLE test (id INT PRIMARY KEY NOT NULL, col1 text NOT NULL, col2 text NOT NULL)');
    $this->pdo->query('CREATE INDEX index_col1 ON test (col1)');
    $result = $this->pdo->query('PRAGMA index_info(test);')->fetchAll(\PDO::FETCH_OBJ);
    print_r($result);

possible duplicate of [How can one see the structure of a table in sqlite?](http://stackoverflow.com/questions/4654762/how-can-one-see-the-structure-of-a-table-in-sqlite) — Luna, Aug 07 '15 at 14:46
[See the top-voted answer](https://stackoverflow.com/a/8050126/250076) — Luna, Aug 07 '15 at 14:46
Unfortunately that does not show the indexes, only the column types. I have updated the question with example code — Tom B, Aug 07 '15 at 14:54
Oh, sorry. Thought that was equivalent? What about `PRAGMA index_list(table-name)`? I've added it to my answer — Luna, Aug 07 '15 at 14:57
aha! Thank you! Yes, I had tried index_info which I'd seen elsewhere but that didn't work. The command is index_list. — Tom B, Aug 07 '15 at 14:58

score 3 · Accepted Answer · answered Aug 07 '15 at 14:54

3

Is it possible to execute these dot commands using PHP and PDO?

No, they only work in the SQLite command line interface.

I'm trying to show the indexes on a table using sqlite and PHP.

PRAGMA index_list(table-name)

answered Aug 07 '15 at 14:54

Luna

1,447
1
18
32

score 2 · Answer 2 · answered Sep 17 '20 at 06:34

2

-- List all indexes
SELECT * FROM sqlite_master WHERE type = 'index';
-- List all indexes on table XXX
SELECT * FROM sqlite_master WHERE type = 'index' and tbl_name = 'XXX';

answered Sep 17 '20 at 06:34

Erik Bachmann

91
5

PHP PDO + Sqlite, show indexes on table

2 Answers2