PHP SQLite JSON Data Duplication

Question

I have the following PHP code:

$testMessage = "TESTMESSAGE";

$db = new SQLite3('messages.sq3');
$db->exec('CREATE TABLE messages(id INTEGER PRIMARY KEY, message CHAR(255));');
$db->exec("INSERT INTO messages (message) VALUES ('$testMessage');");

$results = $db->query('SELECT * FROM messages ORDER BY id DESC LIMIT 5');

while ($row = $results->fetchArray()) {
    print_r($row);
}

The resulting print_r:

Array ( [0] => 1 [id] => 1 [1] => TESTMESSAGE [message] => TESTMESSAGE )

Why is this data duplicated? Is this just the way the array is presented or are there really two copies of TESTMESSAGE string? Inspecting the sqlite file, I only see one actually stored there. I am trying to serialize the output via JSON and this duplication is carrying through to the serialization.

Answer 1

Both $row[1] and $row['message'] will give you the same data. This is because on technique uses the numerical index of the column and the other uses the name. They are both included in the column so that you can use either way to access them. It does not indicate any sort of duplication in the database itself.

Here you can see the documentation and how to tell PHP which version you want. By default it gives you both: http://php.net/manual/en/sqlite3result.fetcharray.php

Answer 2

The default is to have the data with both numeric and string keys, merged in the same array.

You need to use $results->fetchArray(SQLITE3_NUM) or $results->fetchArray(SQLITE3_ASSOC) to get numeric and string keys respectively. The default is SQLITE3_BOTH, which I've always hated.