Accessing and Using Databases using PHP Data Objects - An introduction and tutorial

  • Filter
  • Time
  • Show
Clear All
new posts

    Accessing and Using Databases using PHP Data Objects - An introduction and tutorial

    What is PDO?

    PDO stands for PHP Data Objects and is an easy and convenient way of accessing and manipulating databases in PHP from a variety of database systems, whether MySQL or another database system you use within your PHP applications.

    The difference between using PHP Data Objects and the standard mysql_query(), mysql_fetch_assoc() functions, etc., is that PHP Data Objects are used in the object-oriented programming concept/paradigm. While this really doesn't affect you if you do not use this programming concept in your applications, it can help to understand about object-oriented programming to be able to use PDO proficiently.

    The first thing to note when using PDO is that it completely replaces the need to use any MySQL or other database-related PHP functions, such as mysql_fetch_assoc() or mysql_fetch_object(). All of that functionality is baked into PHP Data Objects. This also goes for the mysql_real_escape_string() function. PHP Data Objects is a completely different database engine being used, so instead of using the mysql_real_escape_string() function to ensure any input sent through a SQL query is escaped with backslashes, use the quote() function/method that is part of PDO. In most cases, this is sufficient to ensure SQL queries are secure when it comes to external input being inserted into an SQL query.

    What is object-oriented programming?

    It is a set of predefined concepts on how programming logic is laid out in your application. Some of the terms when it comes to object-oriented programming concepts are ridiculous, and just plain confusing, but ignore some of the complicated terms like "polymorphism". It's just a fancy Latin-like word to represent certain useful features in object-oriented programming that is available for you to use.

    Usually, object-oriented programming is, at least in PHP, used to wrap certain programming logic and functionality together into kind of "packages" and these "packages" can extend and use other logic from other "packages". It saves having to re-write or re-use code logic that already exists somewhere in your application, and ultimately can make your life easier as a developer.

    So if you do not have experience in object-oriented programming, I'd recommend you read articles and tutorials on it online. We have quite a few tutorials on it on our blog:

    What is CodeIgniter, and how does it work? | Official Web Hosting Blog by eUKhost Ltd.

    This is in regards to the PHP-based framework "CodeIgniter", which is an object-oriented framework. We explain the basics of object-oriented programming in this article before going onto explaining the features of CodeIgniter. It's a pretty nice framework by the way!

    Explanation of object oriented programming logistics | Ben Stones' Personal Tech Blog on Windows, Mac, Software and Hardware

    On my own personal tech blog, I explain the logistics and certain features of object-oriented programming.

    Visual Basic: What are objects? | Ben Stones' Personal Tech Blog on Windows, Mac, Software and Hardware

    This is relevant to Visual Basic, but still is an interesting read nonetheless. Explains about the notion of an "object" in the context of object-oriented programming. do I use PDO?

    Now, in object-oriented programming, there are classes, which are simply like "packages" with functions (or also known as methods) in them, that contain the application/code logic. In order to use the useful methods of PDO, such as quote() and query() and exec(), we have to first declare to PHP we want to use the features of PDO. In other words, we want to create an instance of the PDO class and assign space in memory (to a variable) to use it. And this is it:

    PHP Code:
    $string "mysql:dbname=cpanel-username_db_name;host=localhost";
    $user 'cpanel-username_db_user';
    $password "your password here";
    $db = new PDO($string$user$password);    
    catch (
    PDOException $e) {

    "<p>Connection failed: " $e->getMessage() . "</p>";


    What is the try, catch block? This is a funky new formal (and coherent) way to catch errors that may occur. You can easily use if statements to achieve the same, but it's more coherent to use try, catch blocks. The try block contains code to be executed. If an error occurs, it is catched into the catch block, specifically to the $e variable. You will notice that preceding $e is PDOException, this is the object that returns what the error specifically is. To explain it simply, it is to state to PHP, "this class contains the error I want $e to know about".

    Then $e is of the type of PDOException. PDOException class has a method called getMessage() which returns a string with the error in question, which we simply echo out as usual.

    More information on try, catch ("exceptions") here: PHP: Exceptions - Manual

    So, what is the $string variable containing? It contains the database name, driver type (we're using MySQL here), and the host. You can have localhost or, it represents the local machine (in other words, "where is the database specifically located?" As you may wish to connect to an external database on another server using PHP Data Objects (or even just mysql_query(), obviously)).

    We then create a new instance of PDO. There is no connect() method to connect to the database, when you create an instance of PDO, the actual class itself has parameters which you then give the database connection details. This is instead of having to do something like this:

    PHP Code:
    $instance = new PDO([..]);
    $instance->connect(); // connect to db using supplied details 
    Technically, having parameters in the class itself (for example new PDO([..])) is passing parameter values to the constructor method: PHP: PDO::__construct - Manual

    We then can execute SQL queries, using:

    PHP Code:
    $query $db->query("SELECT * FROM users");
    $query as $row)


    PHP Code:
    $query $db->query("SELECT * FROM users");
    $row $query->fetch(PDO::FETCH_ASSOC); // fetches next row; use foreach above to echo all
    echo $row['column']; 
    To check number of affected rows (such as how many rows DELETE or UPDATE):

    PHP Code:
    $query $db->exec("UPDATE users SET username = '$username' WHERE username = '$username_old'"); // returns number of affected rows - does not work for SELECT
    if($query) {
    "Row updated";

    To check rows of SELECT using a query(), use rowCount():

    PHP Code:
    $query $db->query("SELECT * FROM users");
    Do note if you use quote() to securely escape any malicious input code (SQL injection), do not add quotes around it when executing it in a SQL query, so:

    PHP Code:
    $quote $db->quote($_POST['username']);
    $query $db->query("SELECT * FROM users WHERE username = '$quote'); 
    Will not work, as it will be a double quote, so: WHERE username = ''Ben'', as an example. Correct way:

    PHP Code:
    $quote $db->quote($_POST['username']);
    $query $db->query("SELECT * FROM users WHERE username = $quote); 
    Hope this helps.

    Learn more

    PHP: PDO::exec - Manual

    PHP: PDO::query - Manual

    PHP: PDO - Manual

    An example application using PHP Data Objects is available on our blog for you to download and learn from.

    PHP Data Objects: Accesing and using databases – An illustrative example