Proper usage of SQLite3 in PHP

Question

What is the difference between:

$db = new SQLiteDatabase('name.db');

and

$db = new PDO('sqlite:name.db');

Since, I don't understand the big picture here, details are premature for me and the information available online appears to assume certain knowledge I seem to be lacking. Please don't just paste in links to the PHP manual. Less specific, more general concepts will be useful for me.

Also, do both of these approaches use SQLite 3, as opposed to SQLite 2?

Answer 1

Directly using the SQLite functions/classes, your PHP code will only be compatible with SQLIte.

Using PDO, your PHP code will be compatible with many database systems -- see PDO Drivers for a list of existing drivers.
Quoting the Introduction page of PDO :

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

This abstraction layer can be useful if you want your code to be compatible with more than one DB engine ; but note that :

• Using PDO, you cannot always use features that are specific to a database system
• Using PDO means your PHP code will be compatible ; it doesn't mean your SQL will be too : that's still your job.

For the question about SQLite and SQLite3 :

• PDO seems to support both -- see SQLite Functions (PDO_SQLITE)
  • To use one or the other, you should just have to use a different DSN.
  • Note : working with several distinct DB systems is the kind of thing for which PDO is great ;-)
• Using the specific DB extensions :
  • There is one extension for SQLite2 : SQLite
  • And one other extension for SQLite3 : SQLite3
    • Note that this one seems to only be included with PHP >= 5.3