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

Answer 1

"It depends"

For my own projects, I tend to use a framework of some sort, which includes an abstraction layer. For smaller projects that aren't appropriate for a framewok, I just put the SQL right in the script (because it's small). The last time I did a sizeable application without a framework, I used PDO for it's easy prepared statement support, etc. It's a pretty easy-to-learn abstraction layer. I'd probably use it again, if I chose not to use a framework for some reason.

Answer 2

At the very least, use PDO. It provides a common interface to most database platforms, making it easier to switch database solutions, should you ever wish to.

I like to use an Object Relational Mapper (ORM), like propel: http://propel.phpdb.org/trac/

This maps your objects straight to the database schema - no SQL required. Mapping happens in an XML file, Querying happens through Criteria objects. It's possible to reverse engineer PHP classes from a database, or generate a database from the schema xml.

For more information about ORM in general, see http://en.wikipedia.org/wiki/Object-relational_mapping

Answer 3

What other patterns are there?

ActiveRecord -- you can see an example of this in CakePHP.

I think it highly depends on your needs - if you're building a small application, it's probably quicker to build a DIY database layer.

If you're building a big application with lots of tables and relationships, using something like CakePHP's ActiveRecord functionality will probably be quicker and easier.

I haven't used PEAR's abstraction layer yet but it also looks good. Zend also have a Database library in the Zend framework.

Answer 4

Use an MVC structure and a database abstraction layer. This way you can go to one place to completely rework the internals of a data access class whilst keeping the public interface constant.

Raw SQL throughout the code is going to be nigh on impossible to scale.

Answer 5

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

Something like Linq can't yet be implemented in PHP because the language lacks the necessary constructs. A kind of "fake linq" has been created, but this just uses strings and is not "real" linq. And even if it were, it is effectively just the equivilent of Linq to objects and there is nothing out there like Linq to SQL.

Answer 6

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.

Answer 7

For executing a command, I'd use functions for commonly executed SQL commands. These functions can then be used to make something more specific like get_users()

a) Make a function for each of the queries you might want to make, such as

db_select($opts)

where $opts is a hash array with keys:

['table_name', 'selection', 'condition', 'group_by', 'order_by', 'limit']

b) If you used SQL heavily, I might be tempted to make a SQL command builder, which takes a hash array and returns a command. Something like:

db_builder(array('select'=>array('customers','from'=>'bar','where'=>'foo=10')))

The above mentioned functions would use this in their implementations and so would you if you need a completely random statement, and hopefully the whole thing would be rock solid by reusing the command builder code everywhere.

Answer 8

I do a two-level handcoded approach:

first level is a simple set of functions:

• do_query(sql): returns the (open) result object.
• get_record(sql): returns the first record, the query is closed.
• get_datum(sql): returns the first field of the first record (query closed)

after that, i write my model layer, with specific functions for all data operations. usually a single file with sections for each conceptual object. the parameters are tied to the abstract data, not to the SQL structure. all SQL is in this layer.

Answer 9

If you want an object-oriented approach, here's what my approach has been recently.

I seperate things out into two classes,

First, a DatabaseTable class - takes a table name, a string that is that table's key. Secondly, I create a DatabaseObject class that represents a row in a DatabaseTable. The DatabaseObject has two routines setFromRow and getAsRow. SetFromRow will setup the object from an associative array of col=>value pairs, and get as row will essentially serialize the object as col=>value pairs.

The DatabaseTable uses setFromRow to manufacture DatabaseObjects when the select method is called on the table. Conversely, when it is told to update or insert data into its table, the DatabsaeTable will serialize the DatabaseObject using getAsRow.

Typically what happens is one inherits from DatabaseObject for their own particular object, defines setFromRow and getAsRow, and the DatabaseTable is told the name of the DatabaseObject to instantiate.

So what you end up writing, code wise is something like this

$dbTable = new DatabaseTable('tableName', 'uniqueid', 'InstanceType')
// dbTable manufactures an InstanceType for our use based on the select below
$dbRow = $dbTable->selectUsingId(15); 
print_r($dbRow);  // Dumps the InstanceTypeObject

This separates the representation of data (DatabaseObject) in my application from the management of the database tables (DatabaseTable). So my DatabaseObject can be, in C++ terminology, plain-old-data.

Of course you can go even further, as I have, by creating relationships between tables, and creating more ways to select, etc.

I should add that integrating what is essentially a procedural language (SQL) with what object-orientedness is not easy, so my method, I know, has its drawbacks and you are likely to get a lot of different answers each with their own drawbacks.

Answer 10

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 11

You may also want to consider using CodeIgniter, a nice little MVC framework for PHP. It has both an ActiveRecord implementation and a reasonable set of Database objects (including bound variables). I find its DB classes much nicer than the default PHP MySQL APIs, and a bit simpler than PDOs (certainly easier to install).

Answer 12

Any way is good, as long as you minimize the points where you need to alter your code when your database schema changes (input, filtering, validation, form generation etc.).

I like to keep it simple with PDO, parametrized SQL and classes that abstract the data set returned (::getActiveUsers(), ::getUserPersonalData() etc.).

Answer 13

Read what Mr. Lerdorf himself has to say about "Adding Structure" to a PHP site.

www.oracle.com/technology/pub/articles/php_experts/rasmus_php.html

Answer 14

It definitely will depend on how big of a web app you're writing. I'll add my thumbs-up for Doctrine (when used appropriately). If it's a farly small quick-n-dirty project, you could probably just get away with using PDO.