Join David Powers for an in-depth discussion in this video Creating a database source name, part of Accessing Databases with Object-Oriented PHP.
- View Offline
In this chapter, we'll take a look at basic database operations using PDO. Before you can do anything with PDO, you need to create a database source name or DSN. This is simply a string that identifies the database you want to connect to. It begins with a prefix indicating the database system you're using. The prefix is followed by a colon and a series of name/value pairs. That specify the location of the server, the database name, and any other details needed to establish the connection.
Each pair is separated by semicolons. Each database driver uses a different format for the DSN. Let's take a look at a few examples. The prefix for MySQL is mysql. The driver uses the host for the server and dbname for the database. If you're connecting to MySQL on a nonstandard port, you need to specify that as a separate name value pair. The dear send for SQLite3 is the prefix SQLite followed by a fully qualified path to the database file.
Prefix for Microsoft SQL Server is sqlsrv. The DSN uses server instead of host and database instead of dbname. These are just simple examples that don't cover all available options, such as changing the default character set. You can check which options are available for each PDO driver in the PHP documentation at the address shown here. Although the details for each database are different, only the DSN is database-specific.
All other PDO code is database-neutral. So if you switch databases, all you need to do is change the DSN. Everything else should work seamlessly as long your SQL doesn't use features unique to a particular database system.
- Connecting to a database with PDO or MySQLi
- Fetching a result set
- Executing simple non-SELECT queries
- Sanitizing user input
- Binding input and output values
- Passing an array of values to the execute() method
- Working with advanced PDO fetch methods
- Executing a MySQLi transaction
- Freeing resources that are no longer needed
- Submitting multiple queries
- Creating an instance of a class from a result set