By using database abstraction, you can write database-driven code that should be able to work with a multitude of back ends, simply by changing the DSN used to connect to the database.
However, not all database systems are the same, so you need to consider the design of database tables and SQL statements in order to make sure that your code is as widely supported as possible.
The most important consideration is to make sure that your SQL is written for the lowest common subset of the SQL language available to all the database back ends you want to be compatible with. For example, SQL that contains subqueries will not work with MySQL 4.0 or earlier. Similarly, you should avoid SQL commands that are specific to certain database systems, such as LIMIT or CREATE SEQUENCE.
The DB class includes some portability mode settings that can ease the transition from one database back end to another. These modes are indicated by a series of constants, shown in Table 20.3, that you can set by using the setOption method with the required options, combined with a logical OR operator. The following statement shows an example:
$db->setOption('portability', DB_PORTABILITY_ERRORS | DB_PORTABILITY_NUMROWS);
Working with Quotes
You can use the DB method quoteSmart to enclose a value in quotation marks so that it can be safely inserted into a column. String values are enclosed in quotes, and any characters that need to be delimited are automatically taken care of.
The following example builds a SQL statement by using quoteSmart to ensure that the apostrophe in the string does not interfere:
$sql = "INSERT INTO phrases (phrase) " . "VALUES ( " . $db->quoteSmart($text) . " )";
The following is the value of $sql when the previous statement is executed, using the MySQL driver:
INSERT INTO phrases (phrase) VALUES ( 'Let\'s get ready to rumble' )
The output and the delimiting rules used depend on the database you are connected to.
The way sequences are implemented in different database engines varies considerably. In MySQL, for instance, you use the AUTO_INCREMENT attribute on a table column, and in SQL Server it is called an IDENTITY field. In Oracle you use CREATE SEQUENCE to create a database object that tracks the sequence value independently of any table.
The DB class uses its own set of functions to manage sequences so that using any kind of auto-incrementing field does not tie your code to one particular database back end.
To create a new sequence, you use the createSequence method on a database object, with a unique sequence identifier. After the sequence has been created, the nextId method can be called with that identifier to return the next sequential value.
The following example creates a sequence called order_number and displays the first sequence value:
$db->createSequence("order_number"); echo $db->nextId("order_number");
Subsequent calls to nextId for this sequence return incremental values.
To drop a sequence when you no longer have a use for it, you call the dropSequence method.
MySQL implements the LIMIT keyword in SQL statements, which you can use to restrict the number of rows returned by a query. This is nonstandard SQL, and other database systems do not include this feature.
The DB class includes the limitQuery method, which you can use to emulate the LIMIT clause in a SQL statement for maximum compatibility. This method is called in the same way as a query, but it takes two additional arguments: to specify the starting row and number of rows to be returned.
The following example returns five rows from the query's data set, beginning at row 11 (where row numbering begins at zero):
$res = $db->limitQuery("SELECT * FROM mytable", 10, 5);