Using SQLite

Introduction

To administrate sqlite database use a tool like phpLiteAdmin or SQLite Browser.

Examples on this page use a set of helper functions for interacting with the PDO interface. Go download pdo.php and then include it:

include "./pdo.php"; 

pdo_sqlite extension needs to be enabled (it is by default).

Connecting to a database file

$db_file = "./my_database.sqlite3";
PDO_Connect("sqlite:$db_file");

Fetching data

All functions accept an optional parameters array as a second argument to a function. Use it to bind data to queries.

$fruits = PDO_FetchAll("SELECT * FROM fruits WHERE calories < :calories", array("calories"=>500));
// $fruits = array(array("name"=>"apple", "calories"=>150), array("name"=>"banana", "calories"=>400)); 

$calories = PDO_FetchOne("SELECT calories FROM fruits WHERE name = :name", array("name"=>"apple"));
// $calories = 150; 

$apple = PDO_FetchRow("SELECT * FROM fruits WHERE name = :name", array("name"=>"apple"));
// $apple = array("name"=>"apple", "calories"=>150); 

$fruits = PDO_FetchAssoc("SELECT name, calories FROM fruits");
// $fruits = array("apple"=>150, "banana"=>"400"); 

Executing INSERT or UPDATE queries

PDO_Execute("INSERT INTO fruits (name, calories) VALUES (:name, :calories)", array("name"=>"apple", "calories"=>150)); 

PDO_Execute("UPDATE fruits SET calories = 150 WHERE name = 'apple'");  

Last INSERT id

$id = PDO_LastInsertId();

PDO is more than just SQLite

PDO interface can be used with various database engines, for example to connect to MySQL database do so:

PDO_Connect('mysql:host=localhost;dbname=testdb;charset=UTF-8', 'username','password');```