7.6 Generating Unique IDs

As mentioned in Section 7.1, rows in a database table don't have any inherent order. In a spreadsheet, you can refer particular records such as "the first row" or "the last row" or "rows 15 to 22." A database table is different. If you want to be able to specifically identify individual records, you need to give them each a unique identifier.

To uniquely identify individual rows in a table, make a column in the table that holds an integer ID and store a different number in that column for each row. That way, even if two rows have identical values in all the other columns, you can tell them apart by using the ID column. With a dish_id column in the dishes table, you can tell apart two dishes each called "Fried Bean Curd" because the rows have different dish_id values.

PEAR DB helps you generate unique integer IDs with its support for sequences. When you ask for the next ID in a particular sequence, you get a number that you know isn't duplicated in that sequence. Even if two simultaneously executing PHP scripts ask for the next ID in a sequence at the exact same time, they each get a different ID to use.

You can have as many independent sequences as you want. To get the next value from a sequence, call the nextID( ) function. Example 7-29 gets an ID from the dishes sequence and then uses it to INSERT a row into the dishes table.

Example 7-29. Getting an ID from a sequence

$dish_id = $db->nextID('dishes');
$db->query("INSERT INTO orders (dish_id, dish_name, price, is_spicy)
    VALUES ($dish_id, 'Fried Bean Curd', 1.50, 0)");