How to Use the PHP Text Database API

Running SQL on a Text Based Database in PHP

Mar 19, 2009
Mark Alexander Bain

Using the PHP Text Database API - Mark Alexander Bain

Not all web servers have a database (due to technological or financial constraints). However, the PHP programmer can still use SQL with the PHP Text Database API.

One of the most powerful aspects of PHP is the ease with which a programmer can connect to a database and manipulate its contents. With just a few lines of code the developer can run a SQL query on the database and return the information to a the PHP application user.

However, a database may not always be available. For example the cost of web hosting may increase if a database (such as MySQL) is included in a package. In this case the PHP programmer will need to look at an alternative way of storing information, and one such solution in the PHP Text Database API.

The PHP Text Database API allows the developer to use text files just as if they were tables in a database, and the programmer can run SQL queries on these tables just like any other database. There are some limitations (such as only three data types) but the PHP Text Database API can be a quick and easy answer for any developers that do not have access to the more commonly used databases.

Installing the PHP Text Database API

The PHP Text Database API (or PHP-TextDB API) can be downloaded from http://www.c-worker.ch/txtdbapi/index_eng.php and is stored in a zip file. When the file is decompressed it will create a new directory (something like php-txt-db-api-0.3.1-Beta-01).

This folder contains all of the files required for the PHP Text Database API, including an extensive help file. It can be renamed if required (for example to php-txt-db-api) and can be moved anywhere that is accessible to PHP - it does not have to be in the htdocs area of the web server.

Once the files have been downloaded then configuration is very simple.

Configuring the PHP Text Database API

The PHP Text Database API folder contains an important file named txt-db-api.php. This is the API's master include file and it contains two vital variables that must be set:

$API_HOME_DIR - this must be set to the directory that contains the PHP Text Database API files
$DB_DIR - this is the folder that will contain any databases that are created

For example these may look something like:

$API_HOME_DIR="C:\\php-txt-db-api\\";

$DB_DIR="C:\\database\\";

This means, of course, that the developer can either have one global txt-db-api.php or an individual one for each PHP application that they develop.

Creating a New Database

Before creating a new database it is, of course, necessary to include the txt-db-api.php file:

include "txt-db-api.php";

It is then a good idea to ensure that the database directory actually exists:

if (!file_exists(DB_DIR)) {

mkdir (DB_DIR);

}

The database can then be created in one of two ways. The first is by using the SQL create database statement:

$db_name = "environmental_archaeology";

if (!file_exists(DB_DIR . $db_name)) {

$db = new Database(ROOT_DATABASE);

$sql = "CREATE DATABASE " . $db_name;

$db->executeQuery($sql);

}

Or by using the mkdir statement:

if (!file_exists(DB_DIR . $db_name)) {

mkdir (DB_DIR . $db_name);

}

The next stage is to create the tables for the database.

Creating a New Database Table

Each table is created by using the SQL create table statement, however it must be remembered that the PHP Text Database API only accepts 3 data types

inc - an auto-incremental field
int - an integer field
str - a text field

For example:

$db = new Database($db_name);

if (!file_exists(DB_DIR . $db_name . "/samples.txt")) {

$sql = "CREATE TABLE samples

(id inc, ngr str, description str, date_sampled int)";

$db->executeQuery($sql);

}

With the table in place then its contents can be viewed and manipulated by using SQL statements.

Working with Database Tables

Although the files are just text files SQL can still be used on them, for example to insert data:

$sql = "insert into samples (ngr, description, date_sampled)

values

('NT20450078','Early Iron Age pot',20090319)";

$db->executeQuery ($sql);

$sql = "insert into samples (ngr, description, date_sampled)

values

('NT20440079','Late Iron Age pot',20090319)";

$db->executeQuery ($sql);

and also to display the contents:

$sql = "SELECT * FROM samples";

$rs = $db->executeQuery($sql);

echo "There are " . $rs->getRowCount() . " samples:<br>\n";

while ($rs->next()) {

$data = $rs->getCurrentValuesAsHash();

echo $data['id'] . " " . $data['ngr'] . " " . $data['description'] . "<br>\n";

}

And so, although the PHP Text Database API is not a replacement for a databases such as MySQL, it is a good substitute when they are not available.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...