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).

Social Networking

Hide Sites