What is the best way to access a database from PHP? [closed]

Question

When accessing a MySQL-database from PHP there seems to be several ways:

Sprinkle the code with raw SQL-statements
Use the VO-pattern from Java (e.g. DB_DataObjects from PEAR)
DIY and write a script that auto-generates one PHP class per database 

Apart from this I have also read about LINQ in .NET but have not seen it implemented in PHP.

What other patterns are there?

If you started building a website from scratch today, which one would you choose?

Clarification: This is not about DB abstraction layers (PDO, MDB2). The pattern discussed here is something you build upon PDO or MDB2.

Answer 1

What you're looking for is an Object-Relational Model (ORM). There's a couple different ones out there:

• Propel
• Doctrine

If an ORM is too much for your project, then you would just fall back to a generic DB interface like PDO and build prepared statements manually.

Answer 2

I'd choose the MDB2 database abstraction layer from PEAR - it provides a nice, abstracted method to deal with the database. I recommend it, since it allows you to write portable code which can be ported to a different database server without many changes required (for a basic script, just changing the connect call is likely to be sufficient). It is a merge of the old DB and Metabase abstraction layers (DB is still supported for bugfixes, but has been superceded by MDB2).

It offers features like prepare + execute emulation for DBs that don't support it properly, and allows you to use placeholders which is good practice to avoid SQL injection problems.

It will work with: mysql / mysqli, pgsql (PostgreSQL), oci8 (Oracle), sqllite, msql, mssql (Microsoft SQL Server), sybase, informix, fbsql, ibase, odbc.

Have a look at the MDB2 documentation to see how it works.