In this example we will discuss how to properly connect to Mysql database using PDO.
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.
$host = '127.0.0.1'; $db = 'test'; $user = 'root'; $pass = ''; $charset = 'utf8mb4'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, $options);
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).
$hoststands for the database host. In case of the local development, it is most likely be
localhost. 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.
$dbis the name of the database in MySQL (the value that you were passing into
mysql_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
$charsetis 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 the
utf8MySQL charset, it is now recommended to use
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 to
PDO::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 with
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 using
PDO::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 are
PDO::FETCH_OBJwhich make PDO to fetch the resulting row as an associative array or as an object.
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.
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.
try .. catcharound PDO connection that is doing nothing but echoing the error out. Although there is a controversy about this, catching an exception right in place is considered a very bad practice, defeating the purpose of this excellent feature. Besides, echoing the error out unconditionally is a very bad practice as well. All error messages should be processed uniformly: on a dev server they could go on screen whereas on a live server all error messages should be logged instead. While neither task should be done by means of catching an error in place, but by means of setting the appropriate PHP configuration options. Long story short: simply leave PDO exceptions alone and never catch them only to report an error.
Accessing the newly created connection
There is one thing that makes PDO more complex than old mysql_connect related stuff. Although one was able to use mysql_query anywhere in the code, without taking care of the connection which was magically supplied by PHP, with PDO one should always make sure that once created PDO instance is available in each part of their script.
There are many different strategies to have this done, but mostly used are:
- global. Although this method is unanimously frowned upon, in case your code is the usual procedural spaghetti so familiar to every PHP user, using
globalto access a PDO instance would be your least problem. So for the simplest method possible just create a PHP file with the code above, and then include in the every PHP script that needs a database connection. Then use $pdo variable everywhere you need. To make it accessible in functions, add
- singleton approach is better but still frowned upon. But at least it can save you a hassle with global variables. An example can be found in the corresponding chapter about a simple PDO wrapper
- constructor parameter is the most robust method in case your code is OOP. For the every class that needs a database connection, make PDO a constructor parameter