Join David Powers for an in-depth discussion in this video Connecting to different databases with PHP Data Objects (PDO), part of Exporting Data to Files with PHP.
- View Offline
PDO, which stands for PHP Data Objects, is a database abstraction layer designed to overcome to problem of needing to use different functions, to communicate with each database system. The idea behind PDO is that you can switch a replication, to a different make of database without having to change your code. PDO supports a large number of databases. The main ones are listed here. So, in theory, you could switch from MySQL to Microsoft SQL Server or Firebird.
In practice, it's not quite so straightforward. One of the main drawbacks, is that each database system tends to use its own dialect of structured query language. Full portability is practical only if your queries use core SQL syntax. Each database system relies on a dedicated PDO driver. Many servers have the drivers from MySQL and SQLite, but exact availability depends on your hosting company or server administrator.
You can check what your server supports by running php info. Some drivers are still experimental. You can check the current status in the PHP online documentation at this address. To communicate to the database using PDO you need to supply a database source name or DSN. This is simply a string that begins with a prefix, which identifies the PDO driver you want to use. In other words, the type of database you're connecting to. The prefix is followed by a colon and a series of name values pairs, separated by semicolons.
Let's take a look at a few examples. MySQL uses host for the server and dbname for the database. If you're connecting to MySQL on a non-standard port, you need to specify that as a separate name value pair. The DSN for SQLite3 is the prefix SQLite followed by a fully qualified path to the database file. The syntax for Microsoft Sequel server is very similar to MySQL, but is uses server instead of host and database instead of DB name.
You can check all the options for each PDO driver in the PHP documentation at the address shown here.
- Connecting to the database with PDO or MySQL Improved
- Outputting data into a simple text or CSV file
- Generating a spreadsheet
- Creating columns and headers
- Using a class to generate XML
- Creating a page template