SQLite adalah engine database yang paling banyak dipakai, digunakan di setiap smartphone dan di banyak komputer, dibundel dibanyak aplikasi yang digunakan sehari-hari. Meski aplikasi server seperti PHP lebih mengandalkan database server seperti MySQL atau PostgreSQL, SQLite dapat menjadi pilihan tepat untuk aplikasi yang berjalan di server tunggal dan membutuhkan engine database yang ringan.
SQLite3 adalah SQLite versi 3, peningkatan dari versi sebelumnya 2.8. Saat tulisan ini dibuat, rilis terakhir SQLite3 yang dapat dipakai adalah versi 3.29.0
Instalasi Modul dan Klien
Sebelum menggunakan SQLite di PHP, lakukan pemasangan modul sqlite3 dengan perintah berikut:
$ sudo apt install php-sqlite3
Jika membangun aplikasi PHP di windows, pastikan ekstensi sqlite3 diaktifkan, berikut baris kode di php.ini untuk mengaktifkan ekstensi sqlite3:
... ;extension=sockets extension=sqlite3 ;extension=tidy ...
Instal pula aplikasi klien untuk dapat bekerja menggunakan SQLite, seperti membuat database baru dan mengatur bentuk tabel. Di Linux, aplikasi klien dapat diinstal dengan perintah berikut:
sudo apt install sqlite3
Aplikasi klien untuk sistem operasi lain bisa diunduh melalui: https://www.sqlite.org/download.html.
Alternatif selain aplikasi klien berbasis baris perintah / CLI, aplikasi klien SQLite juga terdapat antarmuka berbasis grafis / GUI, salah satunya adalah SQLite Browser yang dapat diunduh melalui https://sqlitebrowser.org/
Buat Database
Sebelum melakukan pemprograman database SQLite menggunakan PHP, buat database baru dengan perintah sebagai berikut:
$ sqlite3 my.db SQLite version 3.29.0 2019-07-10 17:32:03 Enter ".help" for usage hints. sqlite>
Dari perintah di atas, akan dibuat database dengan nama my.db. Pada command prompt sqlite>, masukkan beberapa perintah berikut:
sqlite> .tables sqlite> .exit
Akses SQLite3 di PHP
Kelas SQLite3 digunakan sebagai interface untuk berinteraksi dengan database SQLite. Berikut contoh menampilkan versi SQLite3 yang digunakan oleh PHP.
<?php $ver = SQLite3::version(); echo $ver['versionString'] . "\n";
Simpan file di atas dengan nama version.php kemudian jalankan perintah:
$ php version.php SQLite3 version 3.29.0
Berikut contoh lain mengakses database SQLite3 yang telah dibuat sebelumnya:
<?php
$db = new SQLite3('my.db');
echo $db->querySingle('SELECT SQLITE_VERSION()') . "\n";
Simpan skrip di atas sebagai file access.php kemudian jalankan:
$ php version.php 3.29.0
Method exec()
Method exec() Menjalankan query tanpa hasil terhadap suatu database. Fungsi ini dapat digunakan untuk membuat tabel atau memasukkan record ke dalam tabel.
<?php
$db = new SQLite3('my.db');
$sql = "CREATE TABLE `people` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`name` TEXT,
`gender` TEXT,
`age` INT,
`city` TEXT,
`created_at` TEXT
)";
$db->exec($sql);
$db->exec("INSERT INTO people (`name`, `gender`, `age`, `city`, `created_at`)
VALUES ('Caydence Dillon','Female',NULL,'Amarillo','3/30/2019 4:37 PM');");
$db->exec("INSERT INTO people (`name`, `gender`, `age`, `city`, `created_at`)
VALUES ('Doug Baldwin','Male',52,'Indianapolis','1/24/2019 1:38 PM');");
$db->exec("INSERT INTO people (`name`, `gender`, `age`, `city`, `created_at`)
VALUES ('David Janes','Male',62,'Anaheim','2/11/2019 8:07 AM');");
$last_row_id = $db->lastInsertRowID();
echo "The last inserted row ID is $last_row_id \n";
Kode di atas membuat tabel people dan menambahkan 3 baris data ke tabel tersebut. Di bagian terakhir kode, terdapat method lastInsertRowID() untuk mengetahui ID terakhir dari baris yang telah dimasukkan ke dalam tabel. Jalankan kode di atas sebagai berikut:
$ php exec.php The last inserted row ID is 3
Untuk melihat data yang telah dimasukkan ke dalam database, gunakan konsol sqlite3 berikut:
$ sqlite3 my.db sqlite> .mode column sqlite> .headers on sqlite> select * from people; id name gender age city created_at ---------- --------------- ---------- ---------- ---------- ----------------- 1 Caydence Dillon Female Amarillo 3/30/2019 4:37 PM 2 Doug Baldwin Male 52 Indianapol 1/24/2019 1:38 PM 3 David Janes Male 62 Anaheim 2/11/2019 8:07 AM
Query Data
Dalam bahasa SQL, statement SELECT digunakan untuk mengambil data dalam tabel. Ada dua method yang dapat digunakan untuk query data menggunakan statement SELECT, yaitu method querySingle dan method query.
Method querySingle mengembalikan nilai dari kolom pertama yang dihasilkan query. Sebagai contoh:
<?php
$db = new SQLite3('my.db');
$name = $db->querySingle('SELECT name FROM people WHERE id=2');
echo "Name: $name";
Kode di atas akan menghasilkan nilai dari kolom name yang mempunyai id = 2, output yang dihasilkan:
Name: Doug Baldwin
Parameter kedua method querySingle dapat diset true untuk menghasilkan array dari seluruh baris pertama.
$person = $db->querySingle('SELECT name, gender, age FROM people WHERE id=3', true);
echo "Name: {$person['name']}, Gender: {$person['gender']}, Age: {$person['age']}";
Kode di atas akan menghasilkan output:
Name: Doug Baldwin, Gender: Male, Age: 52
Sedangkan method query() mengembalikan nilai objek dari kelas SQLite3Result. Method ini dapat digunakan untuk mengambil seluruh data yang dihasilkan dari statement SELECT. Sebagai contoh:
$res = $db->query("SELECT id, name, gender, age FROM people");
while ($row = $res->fetchArray()) {
echo "{$row['id']}. {$row['name']}: {$row['gender']} {$row['age']} \n";
}
Kode di atas akan mengasilkan:
1. Caydence Dillon: Female 2. Doug Baldwin: Male 52 3. David Janes: Male 62
Escape String
Gunakan method escapeString() untuk escape string sebelum eksekusi atau query statement SQL.
<?php
$db = new SQLite3('my.db');
$name = "O'Reilly";
$escaped_name = SQLite3::escapeString($name);
$db->exec("INSERT INTO people (`name`, `gender`, `age`, `city`, `created_at`).
VALUES('$escaped_name','Male',41,'Wien','2/1/2019 2:11 PM');");
$person = $db->querySingle("SELECT name, gender, age FROM people WHERE name LIKE '%{$escaped_name}%'", true);
echo "Name: {$person['name']}, Gender: {$person['gender']}, Age: {$person['age']}";
Parameter Binding Pada Statement SQL
Saat aplikasi berhadapan dengan input dari user, statement SQL dibuat menjadi dinamis. Faktor keamanan menjadi hal utama saat berhadapan dengan input yang dimasukkan user. Cara yang disarankan untuk membuat statement SQL secara dinamis adalah dengan menggunakan pengikatan parameter.
Query dengan parameter dibuat dengan method prepare(), digunakan untuk menyiapkan statement SQL. Method ini akan mengembalikan objek dari kelas SQLite3Stmt. Objek statement tersebut mempunyai method bindParam() dan bindValue() yang digunakan untuk mengikat / binding value ke placeholder.
Method bindParam() digunakan untuk mengikat parameter ke variabel statement.
<?php
$db = new SQLite3('my.db');
$stmt = $db->prepare("INSERT INTO people
(`name`, `gender`, `age`, `city`, `created_at`)
VALUES(:name, :gender, :age, :city, :created_at);");
$stmt->bindParam(':name', $name, SQLITE3_TEXT);
$stmt->bindParam(':gender', $gender, SQLITE3_TEXT);
$stmt->bindParam(':age', $age, SQLITE3_INTEGER);
$stmt->bindParam(':city', $city, SQLITE3_TEXT);
$stmt->bindParam(':created_at', $created_at, SQLITE3_TEXT);
$name = 'Abdul Radley';
$gender = 'Male';
$age = 57;
$city = 'Fremont';
$created_at = '9/18/2019 2:56 PM';
$stmt->execute();
$name = 'Daron Reyes';
$gender = 'Male';
$age = 37;
$city = 'Las Vegas';
$created_at = '6/6/2019 3:44 PM';
$stmt->execute();
Method bindValue() digunakan untuk mengikat nilai dari parameter ke variabel statement.
<?php
$db = new SQLite3('my.db');
$stmt = $db->prepare("INSERT INTO people
(`name`, `gender`, `age`, `city`, `created_at`)
VALUES(:name, :gender, :age, :city, :created_at);");
$stmt->bindValue(':name', 'Rick Martin', SQLITE3_TEXT);
$stmt->bindValue(':gender', 'Male', SQLITE3_TEXT);
$stmt->bindValue(':age', 59, SQLITE3_INTEGER);
$stmt->bindValue(':city', 'New Orleans', SQLITE3_TEXT);
$stmt->bindValue(':created_at', '1/11/2019 5:26 PM', SQLITE3_TEXT);
$stmt->execute();
$stmt->bindValue(':name', 'Peter Wilson', SQLITE3_TEXT);
$stmt->bindValue(':gender', 'Male', SQLITE3_TEXT);
$stmt->bindValue(':age', 55, SQLITE3_INTEGER);
$stmt->bindValue(':city', 'Louisville', SQLITE3_TEXT);
$stmt->bindValue(':created_at', '1/19/2019 7:13 AM', SQLITE3_TEXT);
$stmt->execute();