PHP : Database Access - [10.12] Assigning Unique ID Values Automatically

10.12.1 Problem

You want to use an incrementing sequence of integers for unique IDs. For example, you want to assign unique IDs to users, articles, or other objects as you add them to your database.

10.12.2 Solution

With PEAR DB, use DB::nextId( ) with a sequence name to get the next integer in a sequence:

$id = $dbh->nextId('user_ids');

10.12.3 Discussion

By default, the sequence is created if it doesn't already exist, and the first ID in the sequence is 1. You can use the integer returned from nextId( ) in subsequent INSERT statements:

$id = $dbh->nextId('user_ids');
$dbh->query("INSERT INTO users (id,name) VALUES ($id,'david')");

This inserts a record into the users table with an id of 1 and a name of david. To prevent a sequence from being created if it doesn't already exist, pass false as a second argument to nextId( ):

$id = $dbh->nextId('user_ids',false);
$dbh->query("INSERT INTO users (id,name) VALUES ($id,'david')");

To create a sequence, use createSequence( ); to drop a sequence, use dropSequence( ):

$dbh->createSequence('flowers');
$id = $dbh->nextId('flowers');
$dbh->dropSequence('flowers');

A DB_Error object is returned if you try to create a sequence that already exists or drop a sequence that doesn't.