Record counting
A similar feature in ADOdb is the RecordCount() function. Although not natively supported by all database vendors, this method retrieves the number of records returned by a query. Where applicable, ADOdb will return the value provided by the database call and, should this not be available, it will emulate the result by counting the records in the buffered results. As this can affect performance in unsupported databases, the feature can be disabled if required. In such circumstances, the PO_RecordCount() function can be used (the “PO” being short for “portable”).
Generating insert identifiers
If one of the databases supported by the application lacks support for auto-incrementing fields, you are left with little choice but to use sequence tables. In most cases, a single table can be used to store sequences for each data table. The following script describes such a table.
CREATE TABLE sequences (
-- sequence is a reserved word
sequence_name varchar(100) primary key,
sequence_id int
);
In such a table, the name of the sequence is used as a primary key and the integer field stores the current value of the sequence. Using the sequence is thus a matter of writing a simple function to increment the sequence counter for the given sequence and return this value for use by the next INSERT statement. Consider the following example.
function Get_next_id($sequence){
$rs = get_one(“SELECT sequence_id FROM sequences
WHERE sequence_name=’”.$sequence.”’”);
$id = $rs[‘sequence_id’];
if (execute_query(“UPDATE sequences SET sequence_id = sequence_id + 1
WHERE sequence_name=’”.$sequence.”’
AND sequence_id = “ . $id)){
return $id + 1;
}else{
return Get_next_id($sequence);
}
}
In this example, we avoid the need to lock tables or use a transaction by using a self-referential function. Although this may not be the most robust methodology, we ensure that another process hasn’t updated our sequence in between the SELECT and the UPDATE statements. In so doing, we maintain integrity of the sequence in the most portable way. High traffic applications that experience interruptions in this method would need to wrap the process in table or row locks or, preferably, use a stored procedure if possible.