Recipe 10.11. Creating Unique Identifiers

10.11.1. Problem

You want to assign unique IDs to users, articles, or other objects as you add them to your database.

10.11.2. Solution

Use PHP's uniqid( ) function to generate an identifier. To restrict the set of characters in the identifier, pass it through md5( ), which returns a string containing only numerals and the letters a through f. Example 10-28 creates identifiers using both techniques.

Creating unique identifiers

<?php $st = $db->prepare('INSERT INTO users (id, name) VALUES (?,?)'); $st->execute(array(uniqid(), 'Jacob')); $st->execute(array(md5(uniqid()), 'Ruby')); ?>

You can also use a database-specific method to have the database generate the ID. For example, SQLite 3 and MySQL support AUTOINCREMENT columns that automatically assign increasing integers to a column as rows are inserted.

10.11.3. Discussion

uniqid( ) uses the current time (in microseconds) and a random number to generate a string that is extremely difficult to guess. md5( ) computes a hash of whatever you give it. It doesn't add any randomness to the identifier, but restricts the characters that appear in it. The results of md5( ) don't contain any punctuation, so you don't have to worry about escaping issues. Plus, you can't spell any naughty words with just the first six letters of the alphabet (in English, at least).

If you'd rather give your database the responsibility of generating the unique identifier, use the appropriate syntax when creating a table. Example 10-29 shows how to create a table in SQLite with a column that gets an auto-incremented integer ID each time a new row is inserted.

Creating an auto-increment column with SQLite

<?php // the type INTEGER PRIMARY KEY AUTOINCREMENT tells SQLite // to assign ascending IDs $db->exec(<<<_SQL_   CREATE TABLE users (     id  INTEGER PRIMARY KEY AUTOINCREMENT,     name VARCHAR(255)   ) _SQL_ ); // No need to insert a value for 'id' -- SQLite assigns it $st = $db->prepare('INSERT INTO users (name) VALUES (?)'); // These rows are assigned 'id' values foreach (array('Jacob','Ruby') as $name) {     $st->execute(array($name)); } ?>

Example 10-30 shows the same thing for MySQL.

Creating an auto-increment column with MySQL

<?php // the AUTO_INCREMENT tells MySQL to assign ascending IDs // that column must be the PRIMARY KEY $db->exec(<<<_SQL_   CREATE TABLE users (     id  INT NOT NULL AUTO_INCREMENT,     name VARCHAR(255),     PRIMARY KEY(id)   ) _SQL_ ); // No need to insert a value for 'id' -- MySQL assigns it $st = $db->prepare('INSERT INTO users (name) VALUES (?)'); // These rows are assigned 'id' values foreach (array('Jacob','Ruby') as $name) {     $st->execute(array($name)); } ?>

When the database creates ID values automatically, the PDO::lastInsertId( ) method retrieves them. Call lastInsertId( ) on your PDO object to get the auto-generated ID of the last inserted row. Some database backends also let you pass a sequence name to lastInsertId( ) to get the last value from the sequence.

10.11.4. See Also

Documentation on uniqid( ) at http://www.php.net/uniqid, on md5( ) at http://www.php.net/md5, on PDO::lastInsertId( ) at http://www.php.net/PDO::lastInsertId, on SQLite and AUTOINCREMENT at http://www.sqlite.org/faq.html#q1, and on MySQL and is found AUTO_INCREMENT at http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html .