Example to connect to SQLite with PHP and return JSON

You can spend a long time googling for examples of how to do this with PDO. The real juice is in lines 17 and 18. I record it here for posterity.


<?php
/*
* Code to query an SQLite database and return
* results as JSON.
*/

// Specify your sqlite database name and path //
$dir = 'sqlite:mydir/myDatabase.sqlite';

// Instantiate PDO connection object and failure msg //
$dbh = new PDO($dir) or die("cannot open database");

// Define your SQL statement //
$query = "SELECT * FROM myTable";

// Iterate through the results and pass into JSON encoder //
foreach ($dbh->query($query) as $row) {
echo json_encode($row[0]);
}
?>
Advertisements

One thought on “Example to connect to SQLite with PHP and return JSON

  1. This produces a list of JSON objects that then need to be individually parsed. It would be better to do:
    $dbh = array();
    foreach ($dbh->query($query) as $row) {
    $dbh[] = $row;
    }
    echo json_encode($dbh);

    This way you will get all the rows into a single json array.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s