Using SQLite

Table of Contents generated with DocToc

• Preface
• Connecting to a database file
• Fetching data
• Executing INSERT or UPDATE queries
• Last INSERT id
• PDO is more than just SQLite

Preface

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');