Earth Info

SQLite [http://www.sqlite.org] is a very handy tool, essentially it creates a database in a file. SQLite V3 should be installed automatically with PHP5.

I find SQLite very handy when I want to make standalone code that uses a database, and because it doesn’t require an external application(such as Mysql) it makes it very portable. I hunted around the net but couldn’t find a good starting point with PHP, so did a little research and knocked one up.

This sample program shows the basics of creating a database, a table, inserting records, and then displaying them. (note: The syntax for SQLite V2 is very different from V3 so check to make sure which version you are running)

// SQLite V3 - Test Program
// A simple test program to :-
// 1) Create a database file
// 2) Create a table
// 3) Create an index
// 4) Start a transaction
// 5) Insert some data
// 6) Complete the transaction
// 7) Display the records sorted by the index

$dbname = "testDB";
$tablename = "testTABLE";

if ($db = new PDO("sqlite:$dbname")) {
	$query = @$db->query("SELECT * FROM $tablename");
	if ($query === false) {
		$db->query("CREATE TABLE $tablename (
		           id INT NOT NULL ,
		           rand INT NOT NULL ,
		           PRIMARY KEY ( id ));");
		echo "Created $tablename \n";

		$db->query("CREATE index testINDEX on $tablename ( rand)");
		echo "Created index \n";

	} else {
                echo "$tablename and $dbname Already exists\n";
	}

	$db->query("delete from $tablename");
	echo "All records deleted\n";

	$db->beginTransaction();
	for ($n=1;$n<=10;$n++) {
		$random_num = rand(0,100000);
		$sql="INSERT INTO $tablename ( id , rand )	VALUES ($n, $random_num );";
		$db->query($sql);
	}
	$db->commit();
} else {
	die($err);
}

echo "Test Data Insert Complete\n";
$r = $db->query("select * from $tablename order by rand");
while ($row = $r->fetch(SQLITE_ASSOC)) {
	echo $row["id"]."\t".$row["rand"]."\n";
}

This program stores all its data in a file called “testDB”.
There’s also a handy command line program to access your database file, so after running the program

sqlite3 testDB

allows you to manage the database. (similar to the mysql command line tool).