By Andy Harris

MySQL is the most popular database manager for PHP, but there are other alternatives. There are other database packages that work much like MySQL, but in a different way. There are also some new ideas floating around in the web data world that may very well gain traction.

A number of alternatives to MySQL have gained prominence recently. One of the most interesting of these is a database package called SQLite. It’s a lighter-weight alternative to MySQL that still has the power to handle sophisticated data problems.

Whereas MySQL requires a separate server, SQLite is designed to be called without a separate server, which allows it to be embedded in other programs. SQLite is available for free download from the SQLite download page.

Most standard builds of PHP (including XAMPP) already have support for SQLite built-in. Google Chrome also already has SQLite installed, which can be accessed through JavaScript. See this code for an example of the WebSQL mechanism. (You’ll need to use View Source to see the JavaScript code. This example uses the Web SQL mechanism, which is interesting, but may not be supported in the future.)

One advantage of the PDO mechanism is how easy it is to switch to a new data backend. All you need to do is modify the connection string. Rather than specifying mysql as the data connection type, specify sqlite::memory to build a temporary SQLite connection in memory, or sqlite:/file/path/to/database/file to store your database in a file. You can use the ATTR_PERSISTENT constant to require the database to stay constant across multiple requests (which is probably what you want).

For example, this command would open a new SQLite database:

$pdo = new PDO('sqlite::memory', user, pwd, 
       array(PDO::ATTR_PERSISTEND => true); 

After you’ve created the database, the PHP code for managing a SQLite database is just like that for writing a MySQL database. This is one of the biggest advantages of the PDO mechanism. (In older versions of PHP, you had to learn a new set of PHP commands for each database.)

The SQL dialect used in SQLite is very similar to that used in MySQL, but there are a few subtle differences. (The mechanism for creating an auto-numbered field is simpler in SQLite, and data types are more fluid.) However, if you begin with MySQL code, you’ll find that things work very much in the same way.

You might need to modify your php.ini file to enable SQLite. Look at the php.ini file for a line that says

"extension=php_pdo_sqlite.dll"

This line will probably exist in your file, but will be commented out. Remove the comment characters to enable this extension.

SQLite does not interact with PHPMyAdmin, but it has a number of similar tools that allow you to interact with the database. Sqliteman is a very powerful visual data manager much like PHPMyAdmin. It’s a standalone program, available for all major operating systems for free.

Another recommended plug-in is SQLite Manager. This is not a standalone program, but a plug-in for the Firefox web browser. It’s not quite as full-featured as PHPMyAdmin, but it’s more than adequate for quick database work.

Another alternative database mechanism is sometimes called the NoSQL movement. This approach uses a more flexible data storage mechanism that doesn’t require formal SQL queries (although many noSQL implementations also include support for standard SQL). Generally these mechanisms use a form of XML or (increasingly) JSON as the storage mechanism. The localStorage mechanism in HTML5 is one such implementation. The indexed database API, which is now under consideration by the W3C web standards body, is another. You’ll also run across mentions of the NoSQL mechanism in systems like CouchDB.

For the moment, traditional SQL databases are the way to go, but new ideas are coming, and the way to do web data may change.