PHP Data Objects (PDO) provide a consistent interface to different databases including MySQL, PostgreSQL, SQLite, SQL Server and a handful of other systems as well. Aside from database-specific SQL, the only other thing that isn’t consistent is how to connect to a database. Each database type requires a string to represent the database source (or a DSN). The DSN strings are fairly similar to each other and one DSN is not necessarily a drop in replacement for another. Let’s take a look at the DSN’s for the common Open Source databases out there. Please note that the [[variables]] are just placeholders for the actual values for your server:
MySQL
mysql:host=[[hostname]];port=[[port]];unix_socket=[[socket]];dbname=[[database]]
PostgreSQL
pgsql:host=[[hostname]];port=[[port]];dbname=[[database]];user=[[username]];password=[[password]]
SQLite
sqlite:[[hostname]]
You’ll notice that the only the DSN for PostgreSQL uses the username and password arguments. They are actually optional as when we make out connection we will pass the username and password along with the DSN. Additionally, MySQL only requires either the host and port or just the unix socket when connecting. To create a new PDO object we simply:
$pdo = new PDO($dsn, $username, $password, $attributes
The attributes parameter is optional and beyond the scope of this post. In a nutshell, that’s all you need to do to establish a database connection with PHP Data Objects. Next up we’ll discuss how to use prepared statements with PDO!
