PHP
Article

Re-introducing PDO – the Right Way to Access Databases in PHP

By Parham Doustdar

PDO is the acronym of PHP Data Objects. As the name implies, this extension gives you the ability to interact with your database through objects.

Stock graphic of database icon branching into other icons

Why not mysql and mysqli?

The very valid question people ask when confronted by a new technology is simply, why should they upgrade? What does this new technology give them that is worth the effort of going through their entire application and converting everything to this new library, extension, or whatever?

It’s a very valid concern. We’ve written about this to some degree before, but let’s go through why we think it’s worth it to upgrade.

PDO is object-oriented

Let’s face it: PHP is rapidly growing, and it is moving toward becoming a better programming language. Usually, when this happens in a dynamic language, the language increases its strictness in order to allow programmers to write enterprise applications with peace of mind.

In case of PHP, better PHP means object-oriented PHP. This means the more you get to use objects, the better you can test your code, write reusable components, and, usually, increase your salary.

Using PDO is the first step in making the database layer of your application object-oriented and reusable. As you will see in the rest of this article, writing object-oriented code with PDO is much simpler than you may think.

Abstraction

Imagine that you have written a killer application using MySQL at your current workplace. All of a sudden, someone up the chain decides that you must migrate your application to use Postgres. What are you going to do?

You have to do a lot of messy replaces, like converting mysql_connect or mysqli_connect to pg_connect, not to mention all the other functions you used for running queries and fetching results. If you were using PDO, it would be very simple. Just a few parameters in the main configuration file would need changing, and you’d be done.

It allows parameter binding

Parameter binding is a feature that allows you to replace placeholders in your query with the value of a variable. It means:

  • You don’t have to know, at runtime, how many placeholders you will have.
  • Your application will be much safer against SQL injection.

You can fetch data into objects

People who have used ORMs like Doctrine know the value of being able to represent data in your tables with objects. If you would like to have this feature, but don’t want to learn an ORM, or don’t want to integrate it into an already existing application, PDO will allow you to fetch the data in your table into an object.

The mysql extension is no longer supported!

Yes, the mysql extension is finally removed from PHP 7. That means if you’re going to use PHP 7, you need to change all those functions to mysqli_* instead of mysql_*. This is a great time to just upgrade straight to PDO because of how much it helps you in writing maintainable, portable code with much less effort.

I hope the reasons above have convinced you to start integrating PDO into your application. Don’t worry about setting it up; you may already have it on your system!

Verifying the Existence of PDO

If you are using PHP 5.5.X and above, chances are that your installation already includes PDO. To verify, simply open the terminal on Linux and Mac OS X, or the command prompt on Windows, and type the following command:

php -i | grep 'pdo'

You can also create a php file under your webroot, and insert a phpinfo() statement inside it:

<?php
phpinfo();

Now, open this page in your browser and search for the pdo string.

If you have PDO and MySQL, skip the installation instructions. If you have PDO but don’t have it for MySQL, you merely need to install the mysqlnd extension per the instructions below. However, if you don’t have PDO at all, your path is longer, but not harder! Keep on reading and we’ll tell you how to gear up by installing PDO and mysqlnd!

Installation of PDO

If you have already installed PHP from a repository through a package manager (e.g. apt, yum, pacman, and so on), installing PDO is very simple and straightforward; just run the installation command that is listed under your respective operating system and distribution below. If you haven’t, though, I have also included my recommended methods for starting from scratch.

Fedora, RedHat and CentOS

First, if you don’t have it already, add the Remi repository using the instructions provided on their blog. When that’s done, you can easily install php-pdo using the following command:

sudo yum --enablerepo=remi,remi-php56 install php-pdo

Note: Although having the remi repository enabled is required, you need to replace remi-php56 with your desired repository in the command above.

Of course, if you don’t have it already, you also need to install the mysqlnd extension using the following command:

sudo yum --enablerepo=remi,remi-php56 install php-mysqlnd

Debian and Ubuntu

On Ubuntu, you need to add the Ondrej repository. This link points to the PPA for 5.6, but you can find links to previous versions there as well.

On Debian, you should add the Dotdeb repository to your system.

On both of these distributions, once you’ve installed the php5 metapackage, you already have PDO ready and configured. All you need to do is to simply install the mysqlnd extension:

sudo apt-get install php5-mysqlnd

Windows

You should try and use a Linux virtual machine for development on Windows, but in case you’re not up for it, follow the instructions below.

On Windows, you usually get the full lamp stack using Wamp or Xampp. You can also just download PHP straight from windows.php.net. Obviously, if you do the latter, you will only have PHP, and not the whole stack.

In either case, if PDO isn’t already activated, you just need to uncomment it in your php.ini. Use the facilities provided in your lamp stack to edit php.ini, or in case of having downloaded PHP from windows.php.net, just open the folder you chose as your installation directory and edit php.ini. Once you do, uncomment this line:

;extension=php_pdo_mysql.dll

Beginning with PDO: a High-level Overview

When querying your database using PDO, your workflow doesn’t change much. However, there are a few habits you must learn to drop, and others that you have to learn. Below are the steps you need to perform in your application to use PDO. We will explain each one in more detail below.

  • Connecting to your database
  • Optionally, preparing a statement and binding parameters
  • Executing the query

Connecting to your database

To connect to your database, you need to Instantiate a new PDO object and pass it a data source name, also known as a DSN.

In general, a DSN consists of the PDO driver name, followed by a colon, followed by the PDO driver-specific connection syntax. Further information is available from the PDO driver-specific documentation.

For example, here is how you can connect to a MySQL database:

$connection = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', 'root', 'root');

The function above contains the DSN, the username, and the password. As quoted above, the DSN contains the driver name (mysql), and driver-specific options. For mysql, those options are host (in the ip:port format), dbname, and charset.

Queries

Contrary to how mysql_query() and mysqli_query() work, there are two kinds of queries in PDO: ones that return a result (e.g. select and show), and ones that don’t (e.g. insert, delete, and so on). Let’s look at the simpler option first: the ones that don’t.

Executing queries

These queries are very simple to run. Let’s look at an insert.

$connection->exec('INSERT INTO users VALUES (1, "somevalue"');

Technically, I lied when I said that these queries don’t return a result. If you change the code above to the following code, you will see that exec() returns the number of affected rows:

$affectedRows = $connection->exec('INSERT INTO users VALUES (1, "somevalue"');
echo $affectedRows;

As you can probably guess, for insert statements, this value is usually one. For other statements though, this number varies.

Fetching Query Results

With mysql_query() or mysqli_query(), here is how you would run a query:

$result = mysql_query('SELECT * FROM users');

while($row = mysql_fetch_assoc($result)) {
    echo $row['id'] . ' ' . $row['name'];
}

With PDO, however, things are much more intuitive:

foreach($connection->query('SELECT * FROM users') as $row) {
    echo $row['id'] . ' ' . $row['name'];
}

Fetching Modes: Assoc, Num, Obj and class

Just as with the mysql and mysqli extensions, you can fetch the results in different ways in PDO. To do this, you must pass in one of the PDO::fetch_* constants, explained in the help page for the fetch function. If you want to get all of your results at once, you can use the fetchAll function.

Below are a few of what we think are the most useful fetch modes.

  • PDO::FETCH_ASSOC: returns an array indexed by column name. That is, in our previous example, you need to use $row['id'] to get the id.
  • PDO::FETCH_NUM: returns an array indexed by column number. In our previous example, we’d get the id column by using $row[0] because it’s the first column.
  • PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set. For example, $row->id would hold the value of the id column.
  • PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column. If you remember, we noted that PDO, at its simplest form, can map column names into classes that you define. This constant is what you would use to do that.

Note: this list is not complete, and we recommend checking the aforementioned help page to get all of the possible constants and combinations.

As an example, let’s get our rows as associative arrays:

$statement = $connection->query('SELECT * FROM users');

while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['id'] . ' ' . $row['name'];
}

Note: We recommend always choosing a fetch mode, because fetching the results as PDO::FETCH_BOTH (the default) takes twice as much memory, since PHP provides access to different column values both through an associative array and a normal array.

As you might remember, above, when we were listing the advantages of PDO, we mentioned that there’s a way to make PDO store the current row in a class you have previously defined. You have probably also seen the PDO::FETCH_CLASS constant explained above. Now, let’s use it to retrieve the data from our database into instances of a User class. Here is our User class:

class User
{

    protected $id;
    protected $name;

    public function getId()
    {
        return $this->id;
    }

    public function setId($id)
    {
        $this->id = $id;
    }

    public function getName()
    {
        return $this->name;
    }

    public function setName($name)
    {
        $this->name = $name;
    }

}

Now, we can make the same query again, this time using our User class, which is, in these cases, also known as Model, Entity, or a plain old PHP object (taken from Plain Old Java Object in the world of Java).

$statement = $connection->query('SELECT * FROM users');

while($row = $statement->fetch(PDO::FETCH_CLASS, 'User')) {
    echo $row->getId() . ' ' . $row->getName();
}

Prepared Statements and Binding Parameters

To understand parameter binding and its benefits, we must first look more deeply into how PDO works. When we called $statement->query() above, PDO internally prepared a statement, and executed it, returning the resulting statement to us.

When you call $connection->prepare(), you are creating a prepared statement. Prepared statements are a feature of some database management systems that allow them to receive a query like a template, compile it, and execute it when they receive the value of placeholders – think of them as rendering your Blade or Twig templates.

When you later on call $statement->execute(), you are passing in the values for those placeholders, and telling the database management system to actually run the query. It’s like calling the render() function of your templating engine.

To see this in action, let’s create a query that returns the specified id from the database:

$statement = $connection->prepare('Select * From users Where id = :id');

The above PHP code sends the statement, including the :id placeholder, to the database management system. The database management system parses and compiles that query, and based on your configuration, may even cache it for a performance boost in the future. Now, you can pass in the parameter to your database engine and tell it to execute your query:

$id = 5;
$statement->execute([
    ':id' => $id
]);

Then, you can fetch the result from the statement:

$results = $statement->fetchAll(PDO::FETCH_OBJ);

The Benefits of Parameter Binding

Now that you are more familiar with how prepared statements work, you can probably guess at their benefits.

PDO has taken the task of escaping and quoting the input values you receive from the user out of your hands. For example, now you don’t have to write code like this:

$results = mysql_query(sprintf("SELECT * FROM users WHERE name='%s'", 
        mysql_real_escape_string($name)
    )
) or die(mysql_error());

Instead, you can say:

$statement = $connection->prepare('Select * FROM users WHERE name = :name');
$results = $connection->execute([
    ':name' => $name
]);

If that isn’t short enough for you, you can even make it shorter, by providing parameters that are not named – meaning that they are just numbered placeholders, rather than acting like named variables:

$statement = $connection->prepare('SELECT * FROM users WHERE name = ?');
$results = $connection->execute([$name]);

Likewise, having a prepared statement means that you get a performance boost when running a query multiple times. Let’s say that we want to retrieve a list of five random people from our users table:

$numberOfUsers = $connection->query('SELECT COUNT(*) FROM users')->fetchColumn();
$users = [];
$statement = $connection->prepare('SELECT * FROM users WHERE id = ? LIMIT 1');

for ($i = 1; $i <= 5; $i++) {
    $id = rand(1, $numberOfUsers);
    $users[] = $statement->execute([$id])->fetch(PDO::FETCH_OBJ);
}

When we first call the prepare function, we tell our DBMS to parse, compile and cache our query. Later on in our for loop, we only send it the values for the placeholder – nothing more. This allows the query to run and return quicker, effectively decreasing the time our application would need in order to retrieve the results from the database.

You also might have noticed that I have used a new function in the piece of code above: fetchColumn. As you can probably guess, it returns the value of one column only, and is good for getting scalar values from your query result, such as count, sum, min, max, and other functions which return only one column as their result.

Binding Values to an IN Clause

Something that has a lot of people stumped when they first start to learn about PDO is the IN clause. For example, imagine that we allow the user to enter a comma-separated list of names that we store in $names. So far, our code is:

$names = explode(',', $names);

What most people do at this point is the following:

$statement = $connection->prepare('SELECT * FROM users WHERE name IN (:names)');
$statement->execute([':names' => $names]);

This doesn’t work – you can only pass in a scalar value (like integer, string, and so on) to prepared statements! The way to do this is – you guessed it – to construct the string yourself.

$names = explode(',', $names);
$placeholder = implode(',', array_fill(0, count($names), '?'));

$statement = $connection->prepare("SELECT * FROM users WHERE name IN ($placeholder)");
$statement->execute([$names]);

Despite its scary appearance, line 2 is simply creating an array of question marks that has as many elements as our names array. It then concatenates the elements inside that array and places a , between them – effectively creating something like ?,?,?,?. Since our names array is also an array, passing it to execute() works as expected – the first element is bound to the first question mark, the second is bound to the second question mark, and so on.

Providing Datatypes When Binding Parameters

The techniques we showed above for binding values to parameters are good when you are just starting out to learn PDO, but it’s always better to specify the type of every parameter you bind. Why?

  • Readability: for someone reading your code, it’s easy to see what type a variable must be in order to be bound to a parameter
  • Maintainability: knowing that the first placeholder in your query must be an integer allows you to catch any errors that slip out. For example, if someone passes a variable containing test to your function which will then use that value to search for a particular id as an integer, having a datatype allows you to quickly find the error.
  • Speed: when you specify the datatype of the variable, you are telling your database management system that there’s no need to cast the variable and that you’re providing it the correct type. In this way, you don’t have the (small) overhead that comes with casting between datatypes.

To specify the type of each variable, I personally recommend the bindValue function. Let’s alter our code above to specify the type of our placeholder:

$numberOfUsers = $connection->query('SELECT COUNT(*) FROM users')->fetchColumn();
$users = [];
$statement = $connection->prepare('SELECT * FROM users WHERE id = ? LIMIT 1');

for ($i = 1; $i <= 5; $i++) {
    $id = rand(1, $numberOfUsers);
    $statement->bindValue(1, $id, PDO::PARAM_INT);
    $statement->execute();
    $users[] = $statement->fetch(PDO::FETCH_OBJ);
}

As you can see, the only thing that has changed is our call to execute(): instead of passing in the values straight to it, we have bound it first, and have specified that its type is an integer.

Note: You have probably noticed that we have specified the first parameter to bindValue() as 1. If we were using a named parameter (recommended), we would pass in the name of our parameter (e.g. :id). However, in the case of using the ? as a placeholder, the first argument to bindValue() is a number specifying which question mark you are referring to. Be careful – this is a 1-indexed position, meaning that it starts from 1, not 0!

Conclusion

As PHP improves, so do programmers that use it. PDO is the next generation of extensions that allows you to write better code. It’s agile, fast, easy to read, and a delight to work with, so why not implement it in your own project?

Have you implemented PDO into your existing project? What were the problems you faced? Are you glad you migrated over? What features do you miss? Let us know in the comments below!

Meet the author
Parham Doustdar is an Iranian, completely blind developer who was born in February of 1990. He specializes in writing clean, maintainable back-end code with a high test coverage, and believes that code must be readable with the smallest amount of effort. He has been a fan of writing for a long time, but has found himself worthy of sharing knowledge only recently. He enjoys being part of a larger community, and urges everyone to message or follow him on any social network, or just simply to email him!
  • http://www.galvao.eti.br/ Er Galvão Abbott

    What’s the reason for adding that repository in Fedora? I’m using Fedora 22 and didn’t needed to add any repositories to install PDO and I’m running the latest PHP stable version without any repos as well.

    • parhamdoustdar

      Hi,
      It was probably that the way I had explained it was confusing. Here I said:
      “If you have already installed PHP from a repository through a package manager (e.g. apt, yum, pacman, and so on), installing PDO is very simple and straightforward; just run the installation command that is listed under your respective operating system and distribution below.”

      This means if you already have PHP, all you need to do is to run ‘yum install php-pdo’.
      The Remi repository provides the most up-to-date repositories when the official repositories don’t. I’m not sure how fast it is compared to Fedora repository maintainers, but for other distros like CentOS and RedHat, you have to have this repository, or the Webtatic repository.

      • http://www.galvao.eti.br/ Er Galvão Abbott

        Ah, makes sense now. Thank you! =)

        • Mohammad Taheri

          salam parham farsi ham baladi? :)
          hi parham do you think the guys on zend studio have any updates in php 7

          about ways to connecting to an database with php?

          • parhamdoustdar

            I know that Zend Studio 13.X supports PHP 7, but not sure what you mean by “connecting to a database” – it’s not the responsibility of an IDE to give you ways to connect to the database…

            I do know Farsi. If you’re more comfortable communicating in Farsi, connect to me on Twitter, Facebook, LinkedIn or email and we can continue the discussion there.

  • Traverse

    Would love to see a follow up about how to properly use/set up PDO transactions!

    • parhamdoustdar

      Great point! I’ll see what I can do ;)

  • Tony Marston

    I disagree that better PHP is object oriented PHP. PHP is a multi-paradigm language which means that it supports both OO and procedural style, and it is perfectly acceptable to mix both paradigms in a single script. Remember that you *MUST* start with procedural code in order to instantiate the first object. A procedural function does not become “better” just because it is put in a class, and unrelated functions should never be put in the same class as this lowers cohesion.

    I disagree that the only way to access your database in an object oriented way is to use PDO. It is possible to put all the procedural calls to MySQL inside your own object, and for your code to call this object whenever it wants to access the database. This is known as a Data Access Object (DAO) as it exists in the Data Access layer of the 3-Tier Architecture.
    I disagree that the “proper” way is to return database data in objects, with a separate object for each row. It is easier and more efficient to return the data as a single multi-dimensional array and step through it using FOREACH.

    I disagree that using PDO is the only way to enable you to switch from one DBMS to another. Years before PDO existed I needed a way to easily switch between the “mysql_*” and “mysqli_*” extensions, and because I had already created a DAO for the “mysql_*” extension it was easy to create another class for the “mysqli_*” extension. All I have to do at runtime is identify which class to load, and because each class has exactly the same methods with exactly the same parameters (a perfect example of polymorphism) the business layer does not know or care what the underlying database is. I have subsequently extended this feature to include DAOs for PostgreSQL, Oracle and SQL Server.

    I disagree that using PDO makes it easy to switch from one DBMS to another. The problem is that you have to first construct a complete SQL query and then send it to PDO, but although IN THEORY every DBMS should be able to execute the same query and return the same result this does not work in practice. For example, while both MySQL and PostgreSQL will accept the LIMIT and OFFSET clauses these will be rejected by both Oracle and SQL Server. The entire query must be re-engineered before it will work. I get around this with my own DAOs because my business layer does not construct and send a complete query to the DAO, it constructs the different fragments in a query, and it is up to the DAO to string those fragments together in the appropriate manner for that particular DBMS. If you have actual experience of switching between MySQL and Oracle or SQL Server you will realise that there are other differences in SQL syntax which need to be dealt with and PDO cannot handle any of these.

    I write enterprise applications which are used by large organisations, and most of these organisations use a proprietary database such as Oracle or SQL Server, and I could not use PDO for Oracle simply because it is still full of bugs. These bugs do not exist in the non-PDO extension.

    I disagree that using PDO with prepared statements is the only way to secure against SQL injection. It is *A* way but not the *ONLY* way. Properly escaping every string is the original way, and because all my SQL queries are constructed and executed in a single class it is very easy to put the code to escape each string in that class.

    Saying that using PDO is “the right way” is misleading. It is *A* way, and certainly not the *BEST* way, not by a long chalk.

    • parhamdoustdar

      Thanks a lot for the comment!

      You make my points seem much more argumentative than they really are. If you are fine escaping everything yourself, go ahead and do so – I even have an example for that approach. If you think that having “efficient” code means to use associative arrays, and if you feel better replacing functions in your custom DAO, no one is keeping you. :-)

      In most cases, I don’t even have an “only” in my article – I never said the only object-oriented way is PDO, I didn’t say that the only “proper” way is using fetch_class, and I didn’t say that “PDO” was the best way, or the only way, for switching between different databases. I also never said that using PDO takes care of syntactical differences in the SQL queries understood by each RDBMS.

      The “right way”s are mostly useful when you are a beginner in a language, framework, or stack. If you are good enough to understand the concept of a data access object and you are able to write one, then I’m certain you can make your own choice on what best fits your needs. I’m sure we both find PDO a step in the right direction compared to having a piece of code written by PHP beginners, which is peppered with calls to mysqli_* or mysql_* functions.

      • Tony Marston

        Your whole article is geared to promoting PDO above all other techniques. The title itself says “PDO – the Right Way to Access databases in PHP” which implies that every other way is wrong. You also say “better PHP is object-oriented PHP” which implies that if you are not using objects at every possible opportunity then your code is the opposite of “better”. The fact that PDO is OO is irrelevant as the “mysqli_” extension has an object oriented interface as well as a procedural one. It is even possible to do what I did in 2003 (using PHP4 years before PDO was created) which was to put all the procedural calls to “mysql_” in its own class, thus making it OO. I then created different classes for different database extensions which enabled me to switch databases simply by changing a single line of code which identifies which class to load.

        You wrote that PDO enables you to switch from one DBMS to another without changing any code, but you failed to point out that PDO cannot deal with any syntactic differences which exist between different databases. I discovered years ago that there are differences in query syntax between MySQL and Oracle and SQL Server which I was able to solve simply because I have the code for each database in its own class. PDO cannot deal with these differences, so not pointing this out is a serious omission.

        You are now qualifying your article by saying “The “right way”s are mostly useful when you are a beginner” which I find to be completely misleading. You don’t have one “right way” for beginners and a different “right way” for the more experienced. You are supposed to be teaching beginners to write code similar to what more experienced developers would write, and I’m afraid that in this you have failed. As an experienced developer I will never touch PDO simply because it was too little, too late, and as I have already written my own database classes which can do things which PDO cannot then as far as I am concerned PDO is a complete waste of time.
        To create a proper database abstraction layer requires much more effort than simply changing your calls from “mysqli_*” functions to “pdo_*” functions. By not mentioning that your article has no justification for saying that PDO is the “right way”. As an experienced developer I have created a better way which does not have any of PDO’s limitations, so I’m afraid that your article does nothing but point beginners down the wrong path.

        • parhamdoustdar

          Thanks a lot for taking the time to point out the above disagreements. I’m sorry that you didn’t find what you expected in this article.

        • http://hassankhan.me/ Hassan Khan

          Though his article may not have been spot-on with the title, I’d rather use PDO than any one of your classes that has never seen the outside of your production environment.

        • Vishal Kumar

          Ha ha… argument on title… actually he meant … The best Way with pdo. Nice article.

    • Pavel Bína

      Amen…

  • Brent Roose

    I don’t agree on this statement:

    “PDO is the next generation of extensions that allows you to write better code.”

    PDO is shipped with PHP 5.1, which was release in 2006, almost 10 years ago! PDO is useful, but definitely not the “next generation”.

    • parhamdoustdar

      Very true. I was merely comparing it to using mysql_* and mysqli_* functions everywhere.

  • Tomas

    What about debugging prepared statement? Print query string with params?

  • http://www.galvao.eti.br/ Er Galvão Abbott

    It’s probably worth to mention on the “Verifying the existence of PDO” that you can also do it programatically:

    if (extension_loaded(‘PDO’)) {
    // Some code
    }

  • mtjo62

    With Firebird when using a general query or prepared statements within a transaction block, you must include the transaction instance as an argument. Using PDO, none of this is required.

    With Oracle when using transactions, you must set the execute mode argument to OCI_NO_AUTO_COMMIT. This is not required using PDO. Prepared statement binding markers use “:1,:2,etc”. With PDO you can simply use a question mark.

    With SQL Server when using prepared statements, you must declare binding parameters in an array (ie: array( &$var1, &$var2 )) before the parsing the statement as well referencing the variable in the bind function (ie: $var1=”Sales”). Not required with PDO.

    Similar to Oracle, Postgres using “$1,$2, etc” as binding markers. With PDO you can use a question mark as a binding marker for all DB types.

    MySQL requires a string type argument in its binding function for prepared statements. Again, not required with PDO.

    I can go on but my point is that if you plan to support multiple DB types, PDO is most definitely more portable.

  • Adil

    Do we need to close the DB connection explicitly after the execution of a query? Or PDO can handle it?

    Thanks,

    • parhamdoustdar

      It’s better to share the connection through your application. Instead of constantly opening and closing the connection, keep it open, run your queries, and close it afterwards. Most frameworks and ORMs take care of this for you, so you don’t have to worry about this if you are already using one.

      • Adil Shahzad

        But I’m not using any framework as it’s my custom coding. So shall I close the connection after executing the query and then re-open if needed?

        • http://www.bitfalls.com/ Bruno Skvorc

          No, make one instance of the connection and reuse it throughout the app. There’s no need to close after use, the connection is automatically closed when the PDO object is destroyed, which happens automatically when the PHP script ends.

          • Adil Shahzad

            Yeah thats great @Bruno. This is what I wanted to hear. Thanks.

  • parhamdoustdar

    Very true! I included the phpinfo() step because it is the most familiar to beginners, and because people using this way of doing things are much more comfortable getting a whole dump instead of creating a programmatic way of doing it.

    I agree with the fact that if you’re writing a package, or if you want something like Composer to check your dependencies at the time of deployment, using something like phpinfo() is nowhere near the ideal solution.

    Thanks for pointing it out.

  • Mike Zoel

    It would be nice if PDO had a better way to insert or update objects with a lot of properties.

    • parhamdoustdar

      You would need an ORM for that. PDO’s purpose is only to simplify access to a database. There are a lot of simple ORMs if you’re not comfortable pulling in a large third-party package though.

  • parhamdoustdar

    Very true!

  • Stelian

    First of all, iff someone decides to switch from mysql to postgress, the changes are significantly bigger then doing a replace all.

    Secondly and more important, if you are talking about replacing all occurrences, you clearly do not understand abstraction. Even if you use mysqli as an extension (which actually has its merits since PDO::mysql does not have parity on functionality), you can nicely encapsulate that. Thus, you would refactor one class, and not an application.

    Please get your facts straight before publishing misleading information.

  • Janusz Szcząchor

    Very good introduction to PDO. Some time ago I found it in a file from a CMS and have wondered what is this. Thanks to a Kevin Yank’s book I use mysqli.

  • Cosmologist

    For check if pdo exist:
    php –ri pdo
    instead
    php -i | grep pdo

  • ischenkodv

    Small mistake
    $statement->execute([$names]);

    It should be
    $statement->execute($names);

    Because $names is array.

    • parhamdoustdar

      Thanks for pointing that out!

  • parhamdoustdar

    Thanks a lot for introducing this library, lucanos. You’ve got me curious enough to look at it in the weekend.

    • lucanos

      It is very useful, and I understand it was adopted some time ago as the Database Abstraction Layer for WordPress, so it has had alot of attention and development.

  • Bob Anderson

    awesome thank you a lots

  • wilsonfpz

    Hi, I am using laravel 5. I know for a single htpp request, the framework would share the database connection.But how about 10 http requests or 100 http requests, does php handle the database connection pooling management?Thanks

  • https://jonaazizaj.wordpress.com/ Jona Azizaj

    Very useful article!

  • vortex100

    I am on a shared server and there is no PDO or mysqli. I get errors when trying either one. Oh, well.

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Get the latest in PHP, once a week, for free.