10.11. Assigning Unique ID Values Automatically
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.
Solution
With PEAR DB, use DB::nextId( )
with a
sequence name to get the next integer in a sequence:
$id = $dbh->nextId('user_ids');
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.
See Also
Documentation on DB::nextId( )
at
http://pear.php.net/manual/en/core.db.nextid.php,
DB::createSequence( )
at
http://pear.php.net/manual/en/core.db.createsequence.php,
and DB::dropSequence( )
at
http://pear.php.net/manual/en/core.db.dropsequence.php ...
Get PHP Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.