PHP PDO DSN Connection String

PHP's Data Objects, known as PDO, are well covered in the PHP online documentation here but the format of the DSN connection string is covered individually for each driver so I've summarised them here in this post for quick reference.

PDO DSN Connection String Format

The format of the string works like this, where the names in the {curlies} should be substituted for the actual values. At the very least the dbtype and dbname are needed and everything else is optional. If the host is not specified then localhost is assumed.

{dbtype}:dbname={dbname};host={dbhost};port={dbport}

The username and password can be passed as separate parameters to the PDO constructor, but can also be included in the DSN string for some database drivers. For cross compatibility purposes it's probably best to leave the username and password out of the DSN and pass it as the second and third parameters to the PDO constructor.

Valid values for {dbtype} include the following, along with the possible parameters that can be included in the DSN string. I have linked the name of the driver through to the appropriate manual page for the driver's DSN for further reference.

• mysql for MySQL (host, port, dbname, unix_socket)
• pgsql for Postgres (host, port, dbname,user, password)
• sqlite for SQLite (see notes below)
• mssql or sybase or dblib for SQL Server and Sybase (host, dbname, charset, appname, secure)
• firebird for Firebird and Interbase (dbname, charset, role)
• informix for Informix (requires an odbc.ini file; refer to the linked manual page)
• OCI for Oracle (dbname, charset)
• odbc for ODBC (DSN, UID, PWD)
• ibm for IBM DB2 (DSN or DRIVER, DATABASE, HOSTNAME, PORT, PROTOCOL)

Connecting to a SQLite database

Connecting to a SQLite database is a little different because the database is either a disk file or in memory. A valid DSN for SQLite looks like this for a disk file:

sqlite:/path/to/sqlite/file

And for a memory based SQLite database:

sqlite::memory:

Related posts:

• A gotcha with PHP's PDO::FETCH_CLASS
• PHP PDO class to set the MySQL auto_increment_increment
• Handling errors when connecting to a database using PHP PDO
• Fetching data using PHP and PDO with bound placeholders