How to connect to MySQL using PDO

  1. Example
  2. Credentials exlained
  3. Connection options explained
  4. Handling errors
  5. Creating the connection
  6. Don'ts
  7. Accessing the newly created connection
  8. Comments (14)

In this example we will learn how to properly connect to Mysql database using PDO. It is based on the information provided in the main article on PDO but with additional explanations.

Surprisingly, there is no single state-of-the-art connection example in the PHP manual. Instead, different connection options are discussed in different chapters, which makes it hard for the learner to get a single robust example that is ready to use. Below you will find such an example, as well as the explanation of all the options used.

Example

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$port = "3306";
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
$pdo = new \PDO($dsn, $user, $pass, $options);

Credentials exlained

First of all we are defining variables that contain connection credentials. This set is familiar to anyone who were using the old mysql_connect() function, save for $charset may be, which was rarely used (although it should have been).

Note it's a good idea to store connection variables ($host, $db etc.) in a separate file. This way you'll be able to have two versions of your code, one for the local server and one for the remote.

Connection options explained

Next we are creating an array with PDO options that are either critically important or just make your experience with PDO much better.

Handling errors

A database module shouldn't report errors on its own, but should rely on the site-wide error handler. So there must be no error reporting code. For more details refer to the article.

Creating the connection

Having all the options and credentials set, we can finally proceed to creating a connection. To do so we need to create an instance of PDO class for which we need to supply 4 parameters of which the first one, called "DSN" being most important.

DSN is a semicolon-delimited string, consists of param=value pairs, that begins from the driver name and a colon:

      mysql:host=localhost;dbname=test;port=3306;charset=utf8
driver^    ^ colon         ^param=value pair    ^semicolon  

Note that it's important to follow the proper format - no spaces or quotes or other decorations have to be used in DSN, but only parameters, values and delimiters, as shown in the manual.

Beside DSN, we are using $user, $pass and $options variables defined above.

We are wrapping the creation of the PDO instance into a try..catch statement in order to be aware of the possible error, but without the risk of revealing the database credentials.

Don'ts

Beside things that you should do, there are always things that you should do not. Some of them we will discuss below.

Accessing the newly created connection

  1. Put the code above in a distinct file, called, for example, pdo.php.
  2. Include (using require 'pdo.php';) this file into other files that require the database connection.
  3. Use the $pdo variable to access the PDO object.

  4. To use PDO inside functions, you must provide the PDO object as a parameter. For example:

    function getUserData($pdo, $id) {
            $stmt = $pdo->prepare("SELECT * FROM user WHERE id=?");
            $stmt->execute([$id]);
        return     $stmt->fetch();
    }

Related articles:



Got a question?

I am the only person to hold a gold badge in , and on Stack Overflow and I am eager to show the right way for PHP developers.

Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.

Click here to ask!

Latest article:
PHP Error Reporting

SEE ALSO:

Latest comments: