PHP Database Access Creating Unique Identifiers

PHP Database Access

Creating Unique Identifiers

Problem

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

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  Creating unique identifiers

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

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.

Example  Creating an auto-increment column with SQLite

        // 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  Creating an auto-increment column with MySQL

        // 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. Some database backends don’t support PDO::lastInsertId() at all. In that case, PDO::lastInsertId() causes an error with SQLSTATE set to IM001.