How to connect to a MySQL Database using PHP
There are two ways to connect to a MySQL database using PHP. There is the procedural method and the OOP method. Both are valid but arguably the OOP method, using PHP’s Database Object (PDO) is a more robust method in terms of error handling and the amount of in-built methods it has.
It could be said that if you are building an application using a procedural structure then use the procedural method to connect to the DB but I would make an exception in this case and go for the most up to date connection method every time. However you may come across the mysqli method in older applications so in this post I will demonstrate practical examples of both.
$link = mysqli_connect('localhost', 'root', 'password'); if (!$link) { echo 'Unable to connect to the database server.'; exit(); } if (!mysqli_set_charset($link, 'utf8')) { echo 'Unable to set database connection encoding.'; exit(); } if (!mysqli_select_db($link, 'bookstore')) { echo 'Unable to locate the bookstore database.'; exit(); }
What’s going on here?
- Step 1: We create $link variable which holds the username, password and name of the server.
- Step 2: We then create a conditional statement to test that we can establish a connection to the database server. If we can’t we need to alert the user that there is a problem and redirect the user to an error message.
- Step 3: Our next objective is to set the charset of the DB. This is optional but highly recommended. Charsets determine how characters will be encoded. ISO-8859-1 is the default for MySQL but UTF-8 is the more robust method and used in most modern web applications.
- Step 4: Finally we then need to establish a connection to the database its self. If the connection fails alert the user.
Use of exit()
One interesting point of note here is the use of the exit() function. This is optional but highly recommended and the reason for this is that it stops any further processing within that specific area of code. This is particularly helpful when handling errors. For example if we could not connect to the database we would not any further processing of code or functions being called on the assumption of a successful connection. This could generate further errors which could make the application harder to debug. Also – if there was a malicious attempt to compromise your application not having the exit function could expose more clues about your code to the bad guy than you intended.
PDO (OOP)
PDO is the object orientated programmable alternative (OOP) to connecting to a database. In brief OOP is not a language but a concept used in programming to organise and control the flow of code. It is often the defacto method used in the construction of modern applications.
Some languages will only allow OOP – such as Java, C++. PHP on the other hand allows more flexibility and you can choose to write your application in either method. Both are equally as valid. For example WordPress, the most popular Blog platform on the planet is almost all written in procedural code!
A practical example
$dbConnection = new PDO('mysql:host=hostname;dbname=database', 'username', 'password');
What’s going on here?
Here we are creating a new PDO object which takes the database name, username and password as pre-requisites and will attempt to make a connection to it. It will return a variable if the connection is successful. We need to store this variable hence the $dbConnection var.
Now the next question is what happens if the connection fails? Well if that happens PHP will throw what is referred to as an exception.
What are exceptions?
Exceptions in simple terms occur when the program is not able to carry out the task its supposed to do.
We handle those exceptions by using what is called a try/catch statement. It’s a bit like an if/else statement – you basically try something and if that doesn’t work we need to handle the fallout from that.
A practical example
try { //Try something } catch (ExceptionType $e) { //Now deal with it if it fails }
The catch in many ways is the safety net. In PHP this is really handy because as per the if/else statement in the procedural example it is always a good idea both from a usability and a security perspective to handle errors gracefully rather than present a whole load of PHP errors to the user that may reveal more than you were intending!
A practical example
try { $pdo = new PDO('mysql:host=localhost;dbname=booksdb', 'booksdbuser', 'mypassword'); } catch (PDOException $e) { echo 'Unable to connect to the database server.'; exit(); }
Notice the exit() function again?
Getting more from your Catch Statements
So far we have been able to display a simple error message if we cannot connect to our database. But what happens if the MySQL server is down? The problem we have is that PHP falls into ‘silent’ mode if nothing is set to specifically alert the user of the error. In other words nothing gets displayed.
One way to get round this is to use PDO’s setAttribute – what this does is it captures the error code and sets it using the ERRMODE_EXCEPTION.
A practical example
try { $pdo = new PDO('mysql:host=localhost;dbname=booksdb', 'booksdbuser', 'mypassword'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec('SET NAMES "utf8"'); } catch (PDOException $e) { echo 'Unable to connect to the database server.'; exit(); } echo 'Database connection established.';
What is going on here?
Here we are adding the PDO setAttribute property and saying to PHP if there is an error report it by setting it to the ERRMODE_EXCEPTION variable.
Notice the $e in the PDOException – what does this do? This is in fact an object. So far we have generated a very straight forward error but that on its own is not enough if you want to be able to have a better idea at establishing the cause of the problem.
By using $e->getMessage PHP will not only display that there is an error but also provide a more detailed reason as to why this is. This is great when working in a development environment because you can much better idea of how to resolve the problem when debugging. However, in production, it’s probably not a great idea to air your dirty linen in public!
A practical example
catch (PDOException $e) { $output = 'Unable to connect to the database server: ' . $e->getMessage(); include 'output.html.php'; exit(); }
What have we explored?
We have compared procedural and OOP methods for connecting to a MySQL database. Both are valid but it is advisable to use the most up to date methods possible in application development thus OOP is arguably the best option. Not least because of its more sophisticated error handling procedures.
Another great reason for using the OOP method of DB connection is its a great way to dip your toe in to the world of OOP. OOP is here to stay and any aspiring web application developer needs to know and use OOP at some point – so it may as well be now!