How to connect to MySQL using PDO
- Example
- Credentials exlained
- Connection options explained
- Handling errors
- Creating the connection
- Don'ts
- Accessing the newly created connection
- Comments (15)
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).
$host
stands for the database host. In case of the local development, it is most likely be127.0.0.1
orlocalhost
. In case of the live site, the actual hostname should be provided by the site admin / hosting provider. Note that connecting through IP address could save you a headache or two, so if you have a trouble with "localhost", try to use 127.0.0.1 instead.$db
is the name of the database in MySQL (the value that you were passing intomysql_select_db()
). On your local server it could be anything, while on a live site again it should be given to you by the admin / provider.$user
- a database user$pass
- a database password$charset
is a very important option. It is telling the database in which encoding you are sending the data in and would like to get the data back. Note that due to initially limited support of unicode in theutf8
MySQL charset, it is now recommended to useutf8mb4
instead.
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.
PDO::ATTR_ERRMODE
- this is a cornerstone option that should be always set toPDO::ERRMODE_EXCEPTION
. It tells PDO to throw an exception every time a query failed, so you won't have to get the error manually after every query call as it was used to be withmysql_query()
PDO::ATTR_EMULATE_PREPARES
- this option tell PDO whether to use an emulation mode or not. It is agreed upon that in general it's better to turn it off, however in some cases it is convenient to have it turned on. Luckily, this setting could be changed in runtime usingPDO::setAttribute()
method, so let's make it turned off by default as a connection option, with the possibility to fall back later.PDO::ATTR_DEFAULT_FETCH_MODE
- this option is used simply for convenience. Although the fetch method can be always set right in the fetch function call (like$row = $stmt->fetch(PDO::FETCH_ASSOC);
), it is convenient to set it once for all and then just omit it in particular fetches. Besides, when iterating over a statement using foreach there is no place where we can set the fetch mode, so again it is convenient to set it beforehand. The two most popular fetch modes arePDO::FETCH_ASSOC
andPDO::FETCH_OBJ
which make PDO to fetch the resulting row as an associative array or as an object.
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.
Don'ts
Beside things that you should do, there are always things that you should do not. Some of them we will discuss below.
PDO::ATTR_PERSISTENT
. Although this option is rather popular in the copy-pasted cargo cult PDO examples, a learner should always avoid it. There are too many drawbacks (that are outside of the scope of this article) whereas no advantages for a small site at all. This option should be used after a strong consideration only, and by no means as a blindly copy-pasted option just in case.- using
die()
,echo
or any other output operator for the caught exception. As it is explained in the relevant article, PHP error reporting, an error message should never be printed unconditionally, but only according to the site-wide settings. And this should be done in the site-wide handler only.
Accessing the newly created connection
- Put the code above in a distinct file, called, for example,
pdo.php
. - Include (using
require 'pdo.php';
) this file into other files that require the database connection. - Use the
$pdo
variable to access the PDO object. -
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:
- SELECT query with PDO
- INSERT query using PDO
- UPDATE query using PDO
- PDO Examples
- Authenticating a user using PDO and password_verify()
- How to check if email exists in the database?
- Select the number of rows using PDO
- How to create a WHERE clause for PDO dynamically
- DELETE query using PDO
- How to create a prepared statement for UPDATE query
- Getting a nested array when multiple rows are linked to a single entry
- How to execute 1000s INSERT/UPDATE queries with PDO?
- Adding a field name in the ORDER BY clause based on the user's choice
- INSERT helper function for PDO Mysql
- PDO Examples
- PDO Examples
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote