Expose your development to more possibilities by using PHP with SQLite

by Contributor Melonfire | Apr 28, 2006 5:43:00 PM

Tags: Scripting languages, Databases, Contributor Melonfire, PHP, SQLite, database

Takeaway: This tutorial will show you how to use PHP to interact with an SQLite database, by introducing you to the important methods supported by the SQLite API, and providing a simple script template for you to use in your development.

One of the innovations in PHP 5.x is the inclusion of the SQLite database engine. SQLite is a fully self-contained, portable file-based database engine that can be used for most SQL operations without incurring the overhead of client-server communication. This SQLite API is activated by default in PHP 5.x, which means that you can get up and running with SQLite instantly.

This tutorial will show you how to use PHP to interact with anSQLite database, by introducing you to the important methods supported by the SQLite API, and providing a simple script template for you to use in your development. It assumes that you have a working Apache and PHP installation.

Editor's Note: The two code listings mentioned in this document are available in a more easily manageable text form by downloading this file.

It is not strictly necessary to have the interactive SQLite program installed on your system; however, for simplicity in creating the initial set of tables for this tutorial, you should download and install this program. Then, create an example table for your SQL queries, by creating an empty text file, executing the binary with the file name as the parameter and entering the following commands (Listing A) at the interactive command prompt:

Listing A

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, country TEXT);
sqlite> INSERT INTO users VALUES (1, 'john', 'IN');
sqlite> INSERT INTO users VALUES (2, 'joe', 'UK');
sqlite> INSERT INTO users VALUES (3, 'diana', 'US');

Once the table has been created, proceed to build a script template using PHP SQLite methods. (Listing B)

Listing B

<?php
// set access parameters
$db = "users.db";

// open database file
// make sure script has read/write permissions!
$conn = sqlite_open($db) or die ("ERROR: Cannot open database");

// create and execute INSERT query
$sql = "INSERT INTO users (id, username, country) VALUES ('5', 'pierre', 'FR')";
sqlite_query($conn, $sql) or die("Error in query execution: " . sqlite_error_string(sqlite_last_error($conn)));

// create and execute SELECT query
$sql = "SELECT username, country FROM users";
$result = sqlite_query($conn, $sql) or die("Error in query execution: " . sqlite_error_string(sqlite_last_error($conn)));

// check for returned rows
// print if available
if (sqlite_num_rows($result) > 0) {
while($row = sqlite_fetch_array($result)) {
echo $row[0] . " (" . $row[1] . ")\n";
}
}

// close database file
sqlite_close($conn);
?>

There are four simple steps to follow when executing an SQL query with the PHP SQLite extension:

Begin by initializing a database handle with a call to the sqlite_open() function. The path and file name of the database (remember, SQLite is file-based, not server-based like MySQL) is passed to the function as an argument.
Create the SQL query string, and execute it with the sqlite_query() function. The result object of these methods will be different depending on the query type and whether or not it was successful. Successful SELECT queries return a result object; successful INSERT/UPDATE/DELETE queries return a resource identifier; and unsuccessful queries return false. The sqlite_error_string() and sqlite_last_error() methods can be used to catch errors and display appropriate error messages.
For SELECT queries, the result object may be processed further to extract data from it. Used in a loop, the sqlite_fetch_array() function retrieves each record as a PHP array. You access individual fields of the record by calling the appropriate key of the array.
End the session with a call to the sqlite_close() function.