convert sqlite data to json using php

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"}]

fetchArray grabs both association and numerical array indexes. It defaults to SQLITE3_BOTH, use either SQLITE3_ASSOC or SQLITE3_NUM instead. — Ultimater
– Ultimater, Commented Nov 13, 2018 at 5:04
cloud916 check and try answer given below and let us know if any-one worked for you — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Nov 13, 2018 at 8:10

ℛɑƒæĿᴿᴹᴿ · Accepted Answer · 2021-06-10 21:19:25Z

4

Follow an example:

<?php
    $db = new SQLite3('info.db');
    $results = $db->query('SELECT * FROM info');
    $data = array();
    while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
        array_push($data, $row);
    }
    echo json_encode($data);
?>

edited Jun 10, 2021 at 21:19

ℛɑƒæĿᴿᴹᴿ

5,4765 gold badges42 silver badges61 bronze badges

answered Jun 10, 2021 at 16:56

sazzlebells

1012 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 13, 2018 at 5:13

Death-is-the-real-truth

72.3k10 gold badges59 silver badges105 bronze badges

Comments

Extrange planet · Accepted Answer · 2023-06-24 18:43:06Z

0

Easier than all that

<?php
  $db = new SQLite3('info.db');
  $result = $db->query('SELECT * FROM info');
  $rows->fetchArray(SQLITE3_ASSOC);
  echo json_encode($rows);
?>

answered Jun 24, 2023 at 18:43

Extrange planet

2864 silver badges14 bronze badges

Comments

Thomas Jeriko · Accepted Answer · 2018-11-13 05:22:07Z

-1

http://php.net/manual/en/pdostatement.fetch.php

say you can use this if you using pdo

$result = $sth->fetch(PDO::FETCH_OBJ);

$sth is your query

edited Nov 13, 2018 at 5:22

answered Nov 13, 2018 at 5:07

Thomas Jeriko

2223 silver badges13 bronze badges

Collectives™ on Stack Overflow

convert sqlite data to json using php

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related