What is pdo?

PDO ( PHP Data Objects ) is a php extension, lightweight interface that is used to interect with database. Say you have a relational database like mysql that you use to store your website data.

PDO is a data access layer that talks with any database using the same functions provided by pdo extension. Your php application will fetch the data from the database using pdo extension and display it on your web page.

How to enable pdo?

When you install php language it comes with different types of extensions however not all of them are enabled by default. You have to enable the extensions as you need them.

php.ini file can be used to set different types of php extensions and run time configurations. Open your php.ini file and uncomment following line:

# linux/mac users
extension=pdo.so

# windows users
extension=php_pdo.dll

How to use pdo to fetch database records?

Once you enable the php pdo extension reload your web server.

Now, we will use following sample code to connect with a relational database like mysql.

<?php

    try {

        // storing database configs
        // in php arrays to use them
        // in php script anywhere
        $database_configs = [
            'database'  => 'schema',
            'user_name' => 'root',
            'user_pass' => 'root'
        ];

        // using defined database config 
        // array we will connect with pdo
        $dbh = new PDO(
            'mysql:host=localhost;dbname=' .$database_configs['database'],
            $database_configs['user_name'],
            $database_configs['user_pass']
        );

        // running a mysql query and fetching data 
        // from categories table
        $records = $dbh->query('SELECT * FROM categories');
    }
    catch (PDOException $e) {
        exit("Error connecting to database.");
    }
?>

Try/cache are great way of caching different types of errors. Good developer always wraps his code around try and cache when it is required to cache some kind of error without breaking an application.

In above code if we get any error with database connection it simply exit and show an error on a web page. Let's place this code in html page and display data from categories table.

Assuming that you have a categories table with following columns:

  • id
  • title
  • description

Demo Application

We will use bootstrap cdn to format our html table:

<?php

    try {

        $database_configs = [
            'database'  => 'schema',            'user_name' => 'root',
            'user_pass' => 'root'
        ];

        $dbh = new PDO(
            'mysql:host=localhost;dbname=' .$database_configs['database'],
            $database_configs['user_name'],
            $database_configs['user_pass']
        );


        $records = $dbh->query('SELECT * FROM categories');
    }
    catch (PDOException $e) {
        exit("Error connecting to database.");
    }
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>PHP CRUD Example</title>

    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxdata.bootstrapdata.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://maxdata.bootstrapdata.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">

    <h2>Category Table</h2>
    <p>Example to list all the data from category table</p>

    <table class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Description</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($records as $record) : ?>
                <tr>
                    <td><?=$record['id']?></td>
                    <td><?=$record['title']?></td>
                    <td><?=$record['description']?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

</div>

</body>
</html>

In above code, we fetched all the records from categories table and stored in $records variable. Then using php foreach loop we went through each database table row and display record one by one.

Following will be the output of our demo app your application might give you different results depending on your database table data.