Introduction - Sequences
Description
Sequences are a way of offering unique IDs for data rows. If you do most of your work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT.
It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table. To get the value of this unique ID use nextId(), if a sequence doesn't exists, it will be created automatically.
The sequence is automatically incremented each time nextId() is called.
Using a sequence
<?php
// Once you have a valid DB object named $db...
$id = $db->nextId('mySequence');
if (PEAR::isError($id)) {
die($id->getMessage());
}
// Use the ID in your INSERT query
$res =& $db->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");
?>
Note
When using PEAR DB's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR DB's methods to access sequences that were created directly in the DBMS.
If you have a compelling reason to ignore this advice, be aware that the
$seq_nameargument given to all of PEAR DB's sequence methods are modified before DB calls the underlying DBMS.
$seq_nameis passed through PHP's sprintf() function using the value from theseqname_formatoption as sprintf()'s format argument. The defaultseqname_formatis%s_seq. So, for example, if you useperson_id_sequenceas the$seq_name, PEAR DB will change that name toperson_id_sequence_seqwhen querying the DBMS about creating/accessing/updating the sequence.The
seqname_formatcan be modified when connect()'ing or via setOption().Please note that you may need to change the
seqname_formatoption to represent a quoted form of the sequence name if the sequence names contain characters that cannot be used unquoted within queries. For example, PostgreSQL users with uppercase table names will probably need to use"%s_seq"(note the quotes) for queries to work as expected.