使用php将sqlite数据转换为json

Question

I'm trying to convert sqlite query into json. I have the following table with two columns name and age. When I print the query the format doesn't seem to be correct. why am I getting an extra key value pair?

<?php
$db = new SQLite3('info.db');

$results = $db->query('SELECT * FROM info');
while ($row = $results->fetchArray()) {

    $jsonArray[] = $row;
}

echo json_encode($jsonArray)
?>

output

[{"0":"billy","name":"billy","1":"20","age":"20"}]

desired output

    [{"name":"billy","age":"20"}]

Answer 1

Change query to get only those column which are required:

$results = $db->query('SELECT name,age FROM info'); 
// if you want all column then only use *

And then use SQLITE3_ASSOC

while($row = $results->fetchArray(SQLITE3_ASSOC)){ 

Reference:- SQLite3Result::fetchArray

Parameters

mode

Controls how the next row will be returned to the caller. This value must be one of either SQLITE3_ASSOC, SQLITE3_NUM, or SQLITE3_BOTH.

SQLITE3_ASSOC: returns an array indexed by column name as returned in the corresponding result set

SQLITE3_NUM: returns an array indexed by column number as returned in the corresponding result set, starting at column 0

SQLITE3_BOTH: returns an array indexed by both column name and number as returned in the corresponding result set, starting at column 0