Writing database implementation independant code

Use the API of the database handlers

Common mistakes in SQL

Quoting literal string values

Use single quotes ( ' ) to quote literal string values, not double quotes ( " ). Certain database servers interpret a double quote as an identifier quote character.

Example

Wrong:

UPDATE ezcontentobject SET name="Foo" WHERE id=500;

Right:

UPDATE ezcontentobject SET name='Foo' WHERE id=500;

MySQL

By default MySQL allows double quotes to quote literal string values. However, you can turn on the ANSI_QUOTES SQL mode to make sure MySQL treats a double quote as an identifier quote character.

Aliases for columns and tables

Columns should be aliased with the AS keyword (example 1), tables should NOT be aliased with the AS keyword (example 2).

Example 1

Wrong:

SELECT COUNT(*) row_count FROM ezcontentobject;

Right:

SELECT COUNT(*) AS row_count FROM ezcontentobject;

Example 2

Wrong:

SELECT o.id, u.name FROM ezcontentobject AS o, ezcontentobject AS u WHERE u.id=o.owner_id;

Right:

SELECT o.id, u.name FROM ezcontentobject o, ezcontentobject u WHERE u.id=o.owner_id;