Substituting text files for a database is like cutting a fish with a hammer. You might get it to work, but itâs going to be a really messy process. When your application needs a server-side storage mechanism but you canât rely upon the presence of a specific database, turn to SQLite. It correctly handles locking and concurrent accesses, the two big headaches with home-brewed flat files.
Since the SQLite database is bundled with PHP 5, now every PHP 5 script can read, write, and search data using SQL. SQLite differs from most databases because it is not a separate application. Instead, SQLite is an extension that reads from and writes to regular files on the hard drive. Any PHP users who have permission to manipulate files can use SQLite to edit a database, just like they can use GD to edit images.
Although the name SQLite hints at a less than full-featured product,
SQLite actually supports almost all of SQL92, the SQL standard
specification. Besides the usual INSERT
s and
SELECT
s, with SQLite you can also use
transactions, query using subselects, define your own functions, and
invoke triggers.
SQLite actually performs most actions more quickly than many other
popular databases. In particular, SQLite excels at
SELECT
ing data.
If your application does an initial (or
periodic) data INSERT
and then reads many times
from the database, SQLite is an excellent choice. The PHP web site
uses SQLite to handle some forms of searches.
Unfortunately, SQLite has some downsides. Specifically, when you update the database by adding new data, SQLite must lock the entire file until the alteration completes. Therefore, it does not make sense in an environment where your data is constantly changing. SQLite does not have any replication support, because thereâs no master program to handle the communication between the master database and its slaves.
Additionally, SQLite has no concept of access control, so the
GRANT
and REVOKE
keywords are not implemented. This
means you cannot create a protected table that only certain users are
allowed to access. Instead, you must implement access control by
using the read and write permissions of your filesystem.
SQLite is not for sites that are flooded with heavy traffic or that require access permissions on their data. But for low-volume personal web sites and small business intranet applications, SQLite lets you do away with the burden of database administration. SQLite is also perfect for log file analysis scripts and other applications that benefit from a database but whose authors donât want to require the user to install one. SQLite is bundled with PHP 5, so unless it has been specifically omitted, itâs part of every PHP 5 installation.
The SQLite home page (http://www.sqlite.org/) has more details about SQLiteâs features, limitations, and internals. A list of PHPâs SQLite functions is online at http://www.php.net/sqlite.
This chapter starts off with SQLite basics: creating databases, passing SQL queries to SQLite, and retrieving resultsâeverything you need to start using SQLite. It then moves on to alternative SQLite retrieval functions and interfaces, including a nifty object-oriented interface. After covering how to talk with SQLite, this chapter shows how to improve SQLite performance with indexes and how to gracefully handle errors. It closes with a few advanced features: transactions and user-defined functions, which help keep your data consistent and extend SQLite, respectively.
Itâs easy to get up and running with SQLite. Its design eliminates the need for any configuration variables, such as a database server name or a database username and password. All you need is the name of a file where the data is stored:
$db = sqlite_open('/www/support/users.db'); sqlite_query($db, 'CREATE TABLE users(username VARCHAR(100), password VARCHAR(100))');
This creates a
users
table stored in the database file located at
/www/support/users.db
.
When
you try to open a database file that doesnât already
exist, SQLite automatically creates it for you; you
donât need to execute a special command to
initialize a new database.
If you cannot seem to get SQLite to work, make sure you have both read and write permission for the location on the filesystem where youâre trying to create the database.
SQLite has even fewer data types than
PHPâeverythingâs a string. While you
can define a column as
INTEGER
, SQLite wonât complain if
you then INSERT
the string PHP
into that column. This feature (the SQLite manual declares this a
feature, not a bug) is unusual in a database, but PHP programmers
frequently use this to their advantage in their scripts, so
itâs not a completely crazy idea. A
columnâs type matters only when SQLite sorts its
records (what comes first: 2 or 10?) and when you enforce
UNIQUE
ness (0 and 0.0 are different strings, but
the same integer).
The table created in this example has two columns:
username
and password
. The
columnsâ fields are all declared as
VARCHAR
s because theyâre supposed
to hold text. Although it doesnât really matter what
type you declare your fields, it can be easier to remember what
theyâre supposed to hold if you give them explicit
types.
Add new rows to the database using
INSERT
and sqlite_db_query( )
:
$username = sqlite_escape_string($username); $password = sqlite_escape_string($password); sqlite_query($db, "INSERT INTO users VALUES ('$username', '$password')");
You must call
sqlite_escape_string( )
to avoid the usual set of
problems with single quotes and other special characters. Otherwise,
a password of abc'123
will cause a parser error.
Donât use addslashes( )
instead
of sqlite_escape_string( )
, because the two
functions are not equivalent.
To
retrieve data from an SQLite database, call sqlite_query( )
with your SELECT
statement and iterate
through the results:
$r = sqlite_query($db, 'SELECT username FROM users'); while ($row = sqlite_fetch_array($r)) { // do something with $row }
By default,
sqlite_fetch_array( )
returns an array with the
fields indexed as both a numeric array and an associative array. For
example, if this query returned one row with a
username
of rasmus
, the
preceding code would print:
Array (
[0] => rasmus
[username] => rasmus
)
As you can see, sqlite_fetch_array( )
works like
mysqli_fetch_array( )
.
When youâre using user-entered data in a
WHERE
clause, in addition to calling
sqlite_escape_string( )
, you must filter out SQL
wildcard characters. The easiest way to do this is with
strtr( )
:
$username = sqlite_escape_string($_GET['username']); $username = strtr($username, array('_' => '\_', '%' => '\%')); $r = sqlite_query($db, "SELECT * FROM users WHERE username LIKE '$username'");
Use sqlite_num_rows( )
to find the total number of rows returned by your query
without iterating through the results and counting them yourself:
$count = sqlite_num_rows($r);
You can call sqlite_num_rows( )
without retrieving
the results from SQLite. Remember, this function takes the query
result handle, like sqlite_fetch_array( )
.
If speed is a concern, use
sqlite_array_query( )
. This retrieves all the data
and puts it into an array in a single request:
$r = sqlite_array_query($db, 'SELECT * FROM users'); foreach ($r as $row) { // do something with $row }
However, if you have more than 50 rows and only need sequential
access to the data, use sqlite_unbuffered_query( )
:
$r = sqlite_unbuffered_query($db, 'SELECT * FROM users'); while ($row = sqlite_fetch_array($r)) { // do something with $row }
This is the most efficient way to print items in an XML feed or rows
in an HTML table because the data flows directly from SQLite to your
PHP script without any overhead tracking behind the scenes. However,
you canât use it with sqlite_num_row( )
or any function that needs to know the
âcurrentâ location within the
result set.
When you are done with the connection,
call sqlite_close( )
to clean up:
sqlite_close($db);
Technically, this is not necessary, since PHP will clean up when your
script finishes. However, if you open many SQLite connections,
calling sqlite_close( )
when
youâre finished reduces memory usage.
The SQLite function names are similar to the MySQL functions, but not identical. Table 4-1 provides a side-by-side comparison of the two.
Get Upgrading to PHP 5 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.