Using SQLite as an Alternative Data Source

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 5 months ago)

68 views

M
ySQL is a natural companion for PHP, but it is not
the only choice. PHP works equally well with a
number of database packages. The SQLite data-
base is especially interesting as a somewhat simpler alter-
native to MySQL. In this appendix you learn how to work
with SQLite databases in PHP. Specifically, you learn:
• Unique characteristics of SQLite
• How to work with the SQLite command line tool
• The syntax of the SQLite dialect of SQL
• How to connect to an SQLite database from within PHP
U
s
i
n
g
S
Q
L
i
t
e
a
s
a
n
A
l
t
e
r
n
a
t
i
v
e
D
a
t
a
S
o
u
r
c
e
B
A P P E N D I X
Introducing SQLite
Beginning with version 5.0, PHP ships with an alternative database engine called
SQLite. SQLite is an interesting database solution with a number of advantages:
• It does not require a server program to be running.
• The SQLite syntax is a simple and forgiving dialect of SQL.
• SQLite databases are typically fast and compact.
• SQLite support is built into PHP 5.0 on most installations.
• Servers that don’t allow access to MySQL are likely to allow SQLite access,
because it poses fewer security problems.
• It has its own command environment for manipulating databases.
Since PHP 5.0 no longer has built-in support for MySQL (it must be turned on),
you might think that SQLite is the heir apparent database partner with PHP.
SQLite does have a few disadvantages:
• The SQL language supported by SQLite is not as robust or sophisticated as
MySQL’s dialect.
• There is not yet nearly as much community support for SQLite as there is
for MySQL.
• While there are front ends for SQLite similar to phpMyAdmin, they are
not as plentiful or complete.
• The SQLite architecture (which stores databases in ordinary files on the
server) is likely to be less efficient when working with certain kinds of
data, especially large data sets or multiple simultaneous accesses to the
same database.
As of this writing, the future of the PHP/MySQL/SQLite love triangle is unclear.
SQLite is a worthwhile tool even if you are comfortable with MySQL. SQLite is use-
ful in some situations where MySQL access may be impossible. On the other
hand, MySQL’s entrenched base makes it unlikely to go away anytime soon.
30
P
H
P
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
For the purposes of this appendix, I’m assuming that you read Chapter 9, “Using
MySQL to Create Databases” and Chapter 10, “Connecting to Databases within
PHP.” This appendix describes how SQLite differs from MySQL, which is still
considered the most standard database to use with PHP. Please review those
chapters for more specific information on SQL syntax and the general approach
to connecting to a database from within PHP.
Working with SQLite’s
Command Line Tool
The main SQLite engine is installed with PHP (at least with version 5.0). The
dll
file is already installed with PHP 5.0. Download the stand-alone version as well,
which you can get at www.sqlite.org or from this book’s CD.
The
exe
file allows you to modify SQLite databases from the command line, much
like the MySQL command line console. Figure B.1 illustrates the SQL console.
Starting the MySQL Environment
You can’t simply click the SQLite icon to open it from within Windows. You must
specify the database file you want open.
1.Open a DOS window.
2.Use the
cd
command to navigate to the directory containing SQLite.
3.Type the SQLite command and the name of the database you want to
create or access.
HINT
31
A
p
p
e
n
d
i
x
B
U
S
i
n
g
S
Q
L
i
t
e
a
s
a
n
A
l
t
e
r
n
a
t
i
v
e D
a
t
a
S
o
u
r
c
e
FIGURE B.1
The SQLite console
isn’t any friendlier
than the one in
MySQL, but it is just
as powerful.
32
P
H
P
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
For example, I stored all of Chapter 9’s tables in a database called
ph2ed09.db
. To
open that file, type
sqlite ph2ed09.db
. If you indicate an existing database,
sqlite
opens that database. If the database doesn’t already exist,
sqlite
creates it for you.
Invoking SQLite Commands
SQLite has two main command types. Plain SQL commands are automatically
executed. The commands that control the SQLite environment all begin with a
period. You can get a listing of these commands by typing
.help
from within the
console.
Figure B.2 shows several basic commands operated on the
ph2ed09
file.

.tables
produces a list of the tables associated with whatever file you’ve
opened.

.schema
shows the
CREATE TABLE
command that builds a particular table.

.dump
displays the
.schema
information as well as
INSERT
statements
representing each of the records currently stored in the database.
•.
output
specifies a file for output. This routes to a file any output you
create, which can create a script for rebuilding a database.
Examining Queries in SQLite
You can type SQL commands into SQLite just like you do for MySQL. Most commands
are standard SQL (except for some variations in the table-creation commands).
Figure B.3 demonstrates a couple of queries in SQLite.
FIGURE B.2
Examine tables
in SQLite.

.mode
allows you to determine how query results are shown.

.list
is good for setting up comma separated values.

.line
and
.column
are useful when you want to examine query results.
Creating Tables in SQLite
You can create scripts to build and populate SQLite databases, much like in
MySQL. SQLite supports a simpler language, as you can see in the following
script. This script creates a SQLite version of the
phonelist
table from Chapter 9.
— build phone list table
DROP TABLE phonelist;
33
A
p
p
e
n
d
i
x
B
U
S
i
n
g
S
Q
L
i
t
e
a
s
a
n
A
l
t
e
r
n
a
t
i
v
e D
a
t
a
S
o
u
r
c
e
W
HAT

S THE
SQL
ITE
E
QUIVALENT TO PHP
M
Y
A
DMIN
?
If you’ve gotten used to phpMyAdmin or another graphical MySQL front end,
you might wonder if similar tools are available for SQLite. I’ve run across a cou-
ple of such tools in my travels, but I haven’t found any of them as stable and
usable as phpMyAdmin on the MySQL side. You might try a Google search to
see if any good alternatives have popped up since this writing. The truth is, once
you learn how to connect to a SQLite database from within PHP, you can do
pretty much everything you need with PHP scripts, so you rarely need to worry
about the command line.
FIGURE B.3
You can display
query results in a
number of formats.
34
P
H
P
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
CREATE TABLE phonelist(
id INTEGER PRIMARY KEY,
firstname VARCHAR(15),
lastname VARCHAR(15),
email VARCHAR(20),
phone VARCHAR(15)
);
— enter some data
INSERT INTO phonelist VALUES(null,
‘Andy’,’Harris’,’aharris@cs.iupui.edu’,’123-4567’);
INSERT INTO phonelist VALUES(null, ‘Joe’,’Slow’,’jslow@noplace.net’,’987-
6543’);
— show results
SELECT * FROM phonelist;
SQLite does not support the same comment characters that MySQL supports in
its script files. SQLite perceives any line beginning with two dashes as a com-
ment. SQLite does not support the
IF EXISTS
clause, so any
DROP TABLE IF EXISTS
files in a MySQL script need to be shortened to
DROP TABLE
.
Primary key fields should be specified by
INTEGER PRIMARY KEY
. This allows SQLite
to duplicate the
AUTOINCREMENT
behavior of MySQL, meaning SQLite automatically
replaces any null value with an appropriate value.
If you look at the documentation, you find that SQLite is very easygoing about
field types. Technically, you don’t have to indicate any field types, and you can
put any type of data into any field. Still, it’s a good idea to specify standard field
types and stick with them, so your database is compatible with other database
management systems.
Once you create a script for building tables, you can use the
.read
command to
load that script into SQLite. This is a very useful way to transmit data to a new
server. I included a number of scripts for creating the various databases used in
this book on the CD. The script files end with the
.sql
extension to distinguish
them from the binary
.db
files actually used by SQLite.
Connecting to the SQLite Database
The PHP code that connects to the SQLite version of the
hero
database is very sim-
ilar to the code used for the MySQL version. As a general rule, you can replace any
TRICK
of the
mysql
functions for a similar
sqlite
function. There is an
sqlite
function
corresponding to each of the
mysql
database functions.
Getting a SQLite Connection
The following example PHP code connects to a SQLite database called
ph2ed09.db
and displays the
hero
data as an HTML table. I modified this from the
hero.php
program featured in Chapter 9.
<!doctype html public “-//W3C//DTD HTML 4.0 //EN”>
<html>
<head>
<title>SQLite Hero Demo</title>
</head>
<body>
<?
//Connect to the hero database using SQLite.
$db = sqlite_open(“ph2ed09.db”);
$query = “SELECT * FROM hero”;
$result = sqlite_query($db, $query);
print “<table border = 1>\n”;
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)){
print “ <tr> \n”;
foreach ($row as $field => $value){
//print “<td>$field</td>\n”;
print “<td>$value</td>\n”;
} // end foreach
print “</tr> \n”;
} // end while
print “</table> \n”;
?>
</body>
</html>
Opening the Database via the sql_open() Function
All the SQLite-specific functions begin with
sqlite_
. This should not surprise
you, as MySQL functions follow a similar pattern. The general approach for
35
A
p
p
e
n
d
i
x
B
U
S
i
n
g
S
Q
L
i
t
e
a
s
a
n
A
l
t
e
r
n
a
t
i
v
e D
a
t
a
S
o
u
r
c
e
connecting to any database is the same. However, some subtle but important dif-
ferences exist:
• SQLite doesn’t have a
connect()
function, but
sqlite_open()
fulfills the
same purpose.
• The
open
command does not require a server name, user ID, or password.
The database is a file on the Web server and is opened with the permis-
sions of the PHP script owner.
• It is unnecessary to select a database within SQLite, because the database
is implied by the file. If you want a different database in SQLite, store the
tables in a different file.
Fetching Data from the SQLite Database
The
sqlite_query
command works just like its MySQL cousin. However, PHP does
not currently support a
fetch_assoc()
function for the SQLite library. Not a prob-
lem: The same behavior can be derived from the
sqlite_fetch_array()
function.
Simply include the constant
SQLITE_ASSOC
as the second parameter of the
sqlite_fetch_assoc()
function to ensure the returned data is an associative array.
Summary
In this appendix you have added a second database management environment to
your arsenal. You learned the distinctive personality of SQLite, and the variation
of SQL syntax it supports. You learned how to modify your PHP programs so they
can work with SQLite databases.
36
PHP/MySQL
Programming
for the
Absolute
Beginner
CHALLENGES
1.Modify an existing MySQL script to use with SQLite.
2.Search for and install an SQLite alternative for phpMyAdmin.
3.Modify one of your PHP programs so it can work with SQLite.