In this lesson I will show you how to connect to a MySQL database using a PDO connection. When I first started using PDO many (many!) years ago there were literally no tutorials available and documentation was poor and what little there was used extremely abstract examples. Many users were still using the old MySQL extension.
WHAT IS PDO?
PDO is an acronym for PHP Data Objects. PDO is a database abstraction layer. For many students this means nothing. What PDO offers is a method of connecting and working with different databases by changing a single line of code (see Line 8 of Step 2 below).
You may have heard of the CRUD acronym:
Create
Read
Update
Delete
Each of these functions requires specific code when working with the MySQLi extension. These specific functions would then need to be amended if the database was changed to something other than another flavour of MySQL. Using PDO allows a single line of code to be amended.
STEP-BY-STEP GUIDE: HOW TO CONNECT TO A MYSQL DATABASE USING PDO
STEP 1
First, we need to set up some connection variables as follows:
Line 3: this is the location of the database server. Usually, this is just ‘localhost’ as many websites and database servers are in the same local environment. However, this could be an IP address or URL. You should ask your host about this information if you are unsure. In most cases, using ‘localhost’ will work.
Line 4: this is the name of the database. For some cheaper hosting plans, your database name may already be defined. If you have access to your admin panel or phpMyAdmin then you can usually create your own database.
Line 5: the username. Again, this may be linked to your hosting account. If creating your own database using something like phpMyAdmin then the default username may be ‘root’.
Line 6: the user password. If using phpMyAdmin then the default password is usually blank or ‘root’. Again, check with your hosting company if you are not using a local development environment.
It is worth mentioning at this point that you should never leave your username and password as the defaults on a live environment. You should also restrict access to the connection script (or the access details at least) to a script which is placed outside of the public folder.
STEP 2
In this step we shall create our Data Source Name or DSN. It is comprised of a 3 parts:
the database driver
the host
the database name
You can probably see why we created variables in Part 1.
Line 8: note the use of mysql as the database driver. The benefit of using PDO is that all we will need to change is the database driver.
STEP 3
We are now ready to create the connection using the PDO class:
STEP 4
The issue we have at the moment is that we have no way of knowing if we have successfully connected to the database (unless an error is displayed).
We could use a try catch block:
Lines 10 - 12: we set up the try part of the block. If the connection is successful then the message on Line 12 is displayed.
Line 13: the catch part of the block. In this we care catching any returned PDOExceptions as storing them as $error.
Line 14: The error message is displayed.
Note that the returned error can be quite long and difficult to read. For example, this is the returned error when the username is incorrect:
Connection failed: PDOException: SQLSTATE[HY000] [1045] Access denied for user 'root2'@'localhost' (using password: YES) in /Applications/MAMP/htdocs/index.php:11 Stack trace: #0 /Applications/MAMP/htdocs/index.php(11): PDO->__construct('mysql:host=loca...', 'root2', 'root') #1 {main}
We can work with this error as follows:
Line 14: we have added the ->getMessage() method to retrieve a cleaner error message which now reads as follows:
Connection failed: SQLSTATE[HY000] [1045] Access denied for user 'root2'@'localhost' (using password: YES)
You could also add your own custom messages by grabbing the error code and using IF conditions (or SWITCH statements) as follows:
If you would like to learn more then become a member of Coding.Academy and get full unlimited access to all courses and materials.
