melbourne chapter

Using two popular database abstraction packages for PHP, PEAR::DB and Adodb, you are able to generate a unique identifier that is only issued once. It provides this ability by creating sequence tables that will return the unique ID and automatically increment the index. People familiar with MySQL AUTO_INCREMENT would be familiar with this behaviour.

This helps in many situations, such as creating order numbers or unique reference numbers/receipts. You can also use it for your row identifiers. Since the classes work on the idea of portability between different Relational Database Management Systems (RDBMS) you should be incouraged to use them so that you do not rely on individual methods of geenrating the next ID (such as using AUTO_INCREMENT). You can therefore allow the drivers included in the abstraction packages to handle the best implementation for the current RDBMS.
Depending on the flavour of abstraction classes you use, you can generate your unique key doing the following:

PEAR::DB (visit PEAR::DB or the PEAR::DB End user documentation)

PEAR is a very common database abstraction that you can download from the above site. You can simply extract it to a lib folder in your application and use it to execute all your SQL and manage your record set results.

Pear provides the API for working with sequences with the methods:

• integer createSequence (string $seq_name);
• integer nextId (string $seq_name, boolean $onDemand = TRUE);
• integer dropSequence (string $seqName);

An example for generating a unique ID through the API would be as follows:

< ?php
// Once you have a valid DB object named $db...
$id = nextId('mySequence');
if (PEAR::isError($id)) {
die($id->getMessage());
}
// Use the ID in your INSERT query
$res =& $db->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");
?>

ADODB (visit ADODB or the ADODB Manual)

ADODB is another database abstraction that supports many different RBDMS. It is modelled off the Microsoft ADO database classes and provides a very extended package overall with many added benefits, such as Caching etc.

ADODB provides the API for working with sequences with the following methods:

• CreateSequence($seqName = ‘adodbseq’,$startID=1)
• GenID($seqName = ‘adodbseq’,$startID=1)
• DropSequence($seqName = ‘adodbseq’)

An example for generate a unique ID through the API would be as follows:

< ?php

// Lookup the value from the DB connection
$id = $db->GenID('mySequence');
if(empty($id)) die('Unable to generate sequence value);
 
// Use the ID in your INSERT query
$res = $db->Execute("INSERT INTO myTable (id, text) VALUES ($id, 'bar')");
?>