Using SQLite with PHP
SQLite APIs are available for a wide variety of languages. Among the most popular are:
Because SQLite is such an ideal database solution for web sites, I'll conclude this article by showing you how easy it is use SQLite in conjunction with PHP to create database-driven website solutions. Suppose your library database continues to grow, and you want to make it available online so your friends can browse the collection. As you'll see, a simple PHP script will do the trick, but first you need to make sure the SQLite extension is installed. If you're running PHP 5.0.X, the extension is enabled by default; therefore, if you're running a 5.0.X version, you have nothing further to do. If you're running PHP 5.1.X (which I presume you are), it's ideal to access SQLite through PDO. To do so, you'll need to open the php.ini file and uncomment the following lines:
extension=php_pdo.dll
extension=php_pdo_sqlite.dll
extension=php_sqlite.dll
Save and close php.ini, restart Apache, and you're ready to begin using PHP's SQLite API!
If you've used another database in conjunction with PHP, the SQLite API is quite similar, although even easier to use. You simply open a connection to the database by invoking the PDO class and then perform any necessary queries. The following example retrieves the book collection, and outputs each title and author to the browser:
<?php
$dbh = new PDO("sqlite:books");
foreach ($dbh->query("SELECT title, author
FROM books ORDER BY title") AS $row) {
printf("%s (%s) <br />", $row['title'], $row['author']);
}
?>
Producing HTML that looks like this:
Good to Great (Jim Collins) <br />
Purple Cow (Seth Godin) <br />
The Sun Also Rises (Ernest Hemingway)<br />
Beginning Ruby (Peter Cooper)<br />
Bobby Fischer Teaches Chess (Bobby Fischer et al.)<br />
Simple as that! SQLite is an amazingly powerful database with almost no administrative overhead. I'd love to hear what you're doing with it. Please email me at the address found in my bio below!
About the Author
W. Jason Gilmore is Apress' Open Source Editorial Director, and co-founder of IT Enlightenment. He's the author of several books, including the best-selling Beginning PHP and MySQL 5, Second Edition (Apress, 2006. 913pp.), and more recently, Beginning PHP and Oracle. Jason loves receiving email, so don't hesitate to write him at wjATwjgilmore.com.
IE 7
Firefox 2.0
