How to connect to a SQL database in PHP

Today, I want to you to learn the right way to make a connection to a SQL database in a PHP project.

Prerequisites

---

• Be familiar with the PHP language
• Have a local PHP and MySQL/MariaDB environment
• [Optional] Have a GUI database management app (phpmyAdmin or others). If you don’t have/need this you can use CLI to connect to the database

Goal

---

I’ve seen a lot of tutorials that still teach deprecated techniques to connect to MySQL databases in PHP. My goal in this tutorial is to teach you a modern and secure way to connect to any SQL database in PHP using the PDO method.

In this tutorial, I’ll be working with a MySQL database. There’s not much difference between MySQL and SQL-like database systems. So, feel free to use any one you have.

Let’s start our tour.

The workspace

Create a folder for the project (mine is php-pdo):

mkdir php-pdo

Add index.php, config.php and connection.php:

touch index.php config.php connect.php

Also create a database (test):

CREATE DATABASE IF NOT EXISTS test;

Connecting to the database with PDO

To connect to a database, you can either use a database-specific extension like MySQLi for MySQL or the PHP Data Objects (aka PDO). The advantage of using PDO is that it can be used to connect to any database. For that reason, we will use PDO in this tutorial.

Now is the time to start using PDO. In connection.php, create an instance of PDO like this:

<?php
$connection = new PDO();

PDO() accepts these four parameters:

• DSN (data source name): type of database, hostname, database name (optional)
• host login
• host password
• additional options

Considering our database info, let’s replace these parameters with their real values:

<?php
$connection = new PDO("mysql:host=localhost;dbname=test", "root", "", [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  ];

• The line PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION instructs PDO to emit an exception in case of an error.
• You may have noticed that I’m not closing my PHP tag. It is optional, and it is sometimes better to leave it off if the last thing you have in your file is PHP code.

We are professionals, let’s code as professionals.

Refactor the code

Our connection works just fine, but we can still refactor the code. Let’s simplify our connection string with:

$connection = new PDO($dsn, $username, $password, $options);

In config.php, we add all the database info:

$host       = "localhost";
$username   = "root";
$password   = "";
$dbname     = "test";
$dsn        = "mysql:host=$host;dbname=$dbname";
$options    = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

Note:
If you use a database other than MySQL, you need to change mysql in ＄dsn = "mysql:host=＄host;dbname=＄dbname" to the name of your database (like pgsql).

Now, we can call config.php in connection.php.

<?php
function db_connect()
{
  require "config.php";
  $connection = new PDO($dsn, $username, $password, $options);
  return $connection;
}

We have created a function that returns the database connection object. Now, it is the time to:

Use the connection string

In index.php, we can use the db_connect() function like this:

<?php
require "connection.php";

$connection = db_connect();

?>

<p>Hello World</p>

The browser will print Hello World if everything is OK.

But what happens in the case of an error? Good question. Let’s see how to handle exceptions.

Handle exceptions

Simply speaking, exceptions are just errors. Remember that we’ve set PHP attributes to emit errors as exceptions. (PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION).

First, go to config.php and put something wrong. Let’s say I put the database password as blablabla. Now, on the browser, you will have this:

As you can see in the image above, the application has encountered an error. It gives the description of the error and also the line where the problem can be found. This can be interesting if we are still developing our app, but in production, it is a high-security risk. We don’t want to reveal to our visitors too much information about the error. You may notice that the application error tells a lot about our code: the file, the connection string, and the line.

To fix this issue, we need to use the try/catch block:

try {
        require "config.php";
        $connection = new PDO($dsn, $username, $password, $options);

        return $connection;
    } catch (PDOException $e) {
        die($e->getMessage());
    }

In a nutshell, the snippet above will first try to connect to the database, and if it encounters an error, it will catch it and print an explicit error message.

Conclusion

---

In this tutorial we’ve learned how to write a script to connect to a SQL database in PHP like a pro by using:

• PDO
• try/catch block to handle errors.

You can find all the code here.

Thank you for learning with me!