Join David Powers for an in-depth discussion in this video Connecting to a database with PDO, part of Accessing Databases with Object-Oriented PHP.
- View Offline
Now that we've seen how to create a database source name or DSN for PDO, let's connect to the OOPHP database. Because we'll be connecting to the database in every file, it makes sense to use an external file for the database connection. So in the top level of the testing site where you've copied the exercise files for this course, create a new folder called includes. Then inside the includes folder create a PHP file and save it as pdo_connect.php.
The file shouldn't have any HTML code in it, all it needs is the opening PHP tag. To use PDO to connect to MySQL, create a DSN like this. We'll save it as DSN. And it's a string, beginning with the MySQL prefix. A colon, then host=localhost;dbname=oophp.
If you're using a non-standard port for MySQL, you need to add the port as another name value pair. For example, if you're using the default MAMP ports on a Mac, the dsn needs to be changed like this. Let's just duplicate that line and then we'll add the end a semi-colon, port=8889. The order of the name value pairs after the prefix doesn't matter. The exercise files also contain an SQL like database.
So to create a DSN for that let's just go on to another line, dsn equals sqlite, then colon. That needs to be followed by the fully qualified path to the SQLite database file. The file is in the sqlite folder and it's called oophp.db. I'm using xampp on Windows, so my fully qualified path will look like this, c colon slash xampp, then htdocs oophp sqlite, and then the name of the database file which is oophp.db.
If you're on a Mac, you'll need to change that path. For example if you're using mamp, it'll look something like this. Instead of C:/xampp, you'd have a forward slash applications followed by MAMP, and then, it would be the same after that. But these are just examples. The DSN needs to match the database driver you're using and the name or location of the database. I'm going to be using both my SQL and SQlite but we can only use one at a time.
So, I'm going to comment out the last three dsn's. And then we need to create the database connection with a PDO object. So we'll save that as db. And it'll be new PDO, and the first argument to PDO is the database source there. So we've saved that as dsn. And if you're using SQLlight, that's all you need. But for databases that require a username and password, you need to add them as the second and third arguments.
So on MySQL, the username is oophp and the password is lynda, all in lower case. So that's all you need to do to connect to a database using PDO. So we can just save that and we can test it by opening the chapter 2, 02_02 folder inside there is pdo_test.php and if we open that, here on lines ten through to 15 is a little bit of PHP code that will test the connection.
We actually need to create the connection first. We'll do that above the doctype, so add in a PHP block above the doctype. And, when using pdo to connect to a database, you should always use a try catch structure. The try block runs the code, but if PDO throws an exception, you catch it in the catch block. And, this is particularly important with databases that are password protected because an uncaught PDO exception can expose your username and password.
So inside the PHP block at the top, we'll create a try block. After that we'll have a catch block. We're going to catch an exception. We'll call that exception e. And store any error message in error using the get message method. And the code that we want to run needs to go inside the try block. All we need to do is to include our PDO_connect.php file.
To get to the includes folder, we'll need to go up two levels. So, it needs to be require once, then dot dot slash dot dot slash followed by includes, and then pdo_connect.php. So we can just save that, and then go to the browser to test it. And there we are, connection successful.
I hope the same happened with you. Let's just go back to the editing program and if we open pdo_connect.php and introduce an error into our dsn. Let's say we get the wrong name for the database, save that and then go back to the browser and reload that. We then get this message here. We get the SQLSTATE, we get an error number, and then access denied for user oophp local hosted database, ophp, so that tells us what the error is.
So, if you do get an error, read the error message, that should be able to tell you how to correct the problem in your DSN. So let's go back to the editing program and correct that error. Put that o back in there and then I'm going to test this with SQLite. So I'll comment out this DSN and uncomment the SQLite one. I can leave the username and password in there. They'll simply be ignored by SQLite.
So let's just save that and go back to the browser. Refresh that. It says connection successful. There's no indication that we're connecting to a different database, but it's working fine. So now that we've connected to the database with PDO, we're ready to start interacting with it.
- 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