Use PDO and sqlite3 to develop simple database applications
When developing Web applications, people often need to store data permanently for later retrieval and update. In addition to using MySQL or PostgreSQL, the two popular open source RDBMSs to store the data, you can also use SQLite which is simple to use and requires zero configuration. Probably most of your applications just work fine with sqlite. Following we provide a simple tutorial on how to access sqlite database with PHP.
Now suppose we manage a few web sites and record how many visitors and visits they have. We can create a table and populate a few entries as shown in the following SQL statements:
create table visits_summary (
site varchar(200),
visitors integer,
visits integer,
primary key (site)
);
insert into summary values('example.com', 20, 30);
insert into summary values('example2.com', 10, 40);
It is easy to run SQL statements with the command-line tool
provided by the sqlite3 distribution which is aptly named
"sqlite3." You can save the above SQL statements into
one file, e.g., visitors.sql so you can reuse
it. You can also download
the sample visitors SQL file.
and
the sample visitors DB file.
Then you can run
sqlite3 visitors.db
All your SQL statements executed later will affect the
sqlite3 database file visitors.db.
Once you are at the sqlite3 command prompt, you can run
.read visitors.sql
to create the table and load the initial entries. If it works, then running
select * from summary
will return something like this:
example.com|20|30
example2.com|10|40
To access database from PHP, the PDO (PHP Data Objects) extension is a popular choice with which you can write most of your code in database-independent way.
If you use PHP 5.1 and above, mostly PDO and PDO_sqlite are enabled by default in PHP installation. Otherwise you will have to install them manually. With PECL, you can just run the following commands:
pecl install PDO
pecl install PDO_sqlite
Then you can run
php -m |grep pdo
to verify that it is indeed installed and enabled successfully.
Sometimes you may have to modify your php.ini file
to add the following lines:
extension=pdo.so
extension=pdo_sqlite.so
You can find the location of your php.ini file
by running
php -i |grep php.ini
Now you can write PHP script to talk to your DB. First,
you need to copy the
visitors.db file to a location
that is accessible from your script but cannot be directly
accessible from the Web. For example, if your files
are located in
/home/joe/html, then the
visitors.db file
shouldn't be put under that directory. A preferable
location is /home/joe/db.
Then you may need to modify
the open_basedir variable in your
php.ini
configuration file to add
/home/joe/db to it.
You can refer to the document titled Execute arbitrary commands in shared Web hosting environment to see how to create directories outside your Web document root directory.
It is very easy to use PDO to query your database tables. The following code shows how to do that. For simplicity, we ignore any error checking.
$dbh = new PDO('sqlite:../db/visitors.db');
$query = "select * from summary order by visitors desc";
foreach ($dbh->query($query) as $row) {
print "Site: " . $row["site"] . "\t";
print "Visits: " . $row["visits"] . "\t";
print "Visitors: " . $row["visitors"] . "\t";
}
You can download the sample PHP script using PDO to access sqlite3 database.
You can also see a live demo of the sample script that uses PDO to access sqlite3 database.
In addition to SQL queries, you may also want to do some insertion,
deletion or updates to the rows in your tables. In these case,
the query function call does not apply. Instead,
you should use the exec function. It is also very
simple. The return value of the
exec shows the number of rows affected which you can
use to infer whether the call is successful or not.