Shineyrock

like 7 19
Why You Should Be Using PHP’s PDO for Database Access
1. January 2014
2. Daily
3. read 0
Many PHP programmers learned how to access databases by using either the MySQL or MySQLi extensions. As of PHP 5.1, there's a better way. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive!

CRUD Generators and Frameworks

Database code is repetitive, but very important to get right. That's where PHP CRUD generators and frameworks come in—they save you time by automatically generating all this repetitive code so you can focus on other parts of the app.

On CodeCanyon, you will find CRUD generators and frameworks that will help you deliver outstanding quality products on time. (CRUD is an acronym for create, read, update, and delete—the basic manipulations for a database.)
- PHP
  
  9 Useful PHP CRUD Generators and Frameworks Available on CodeCanyon
  
  Franc Lucas
- PHP
  
  Quickly Build a PHP CRUD Interface With the PDO Advanced CRUD Generator Tool
  
  Sajal Soni
Introduction to PDO

PDO—PHP Data Objects—are a database access layer providing a uniform method of access to multiple databases.

It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances.

This tutorial isn't meant to be a complete how-to on SQL. It's written primarily for people currently using the mysql or mysqli extension to help them make the jump to the more portable and powerful PDO.

When it comes to database operations in PHP, PDO provides a lot of advantages over the raw syntax. Let's quickly list a few:
- abstraction layer
- object-oriented syntax
- support for prepared statements
- better exception handling
- secure and reusable APIs
- support for all popular databases
Database Support

The extension can support any database that a PDO driver has been written for. At the time of this writing, the following database drivers are available:
- PDO_DBLIB (FreeTDS/Microsoft SQL Server/Sybase)
- PDO_FIREBIRD (Firebird/Interbase 6)
- PDO_IBM (IBM DB2)
- PDO_INFORMIX (IBM Informix Dynamic Server)
- PDO_MYSQL (MySQL 3.x/4.x/5.x)
- PDO_OCI (Oracle Call Interface)
- PDO_ODBC (ODBC v3 (IBM DB2, unixODBC, and win32 ODBC))
- PDO_PGSQL (PostgreSQL)
- PDO_SQLITE (SQLite 3 and SQLite 2)
- PDO_4D (D)
All of these drivers are not necessarily available on your system; here's a quick way to find out which drivers you have:
```
print_r(PDO::getAvailableDrivers());
```
Connecting

Different databases may have slightly different connection methods. Below, you can see the method to connect to some of the most popular databases. You'll notice that the first three are identical, other than the database type—and then SQLite has its own syntax.
```
try {
  # MS SQL Server and Sybase with PDO_DBLIB
  $DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass);
  $DBH = new PDO("sybase:host=$host;dbname=$dbname", $user, $pass);

  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

  # SQLite Database
  $DBH = new PDO("sqlite:my/database/path/database.db");
}
catch(PDOException $e) {
    echo $e->getMessage();
}
```
Please take note of the try/catch block. You should always wrap your PDO operations in a try/catch and use the exception mechanism—more on this shortly. Typically, you're only going to make a single connection—there are several listed to show you the syntax. $DBH stands for 'database handle' and will be used throughout this tutorial.

You can close any connection by setting the handle to null.
```
# close the connection
$DBH = null;
```
You can get more information on database-specific options and/or connection strings for other databases from PHP.net.

Exceptions and PDO

PDO can use exceptions to handle errors, which means anything you do with PDO should be wrapped in a try/catch block. You can force PDO into one of three error modes by setting the error mode attribute on your newly created database handle. Here's the syntax:
```
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
```
No matter what error mode you set, an error connecting will always produce an exception, and creating a connection should always be contained in a try/catch block.

PDO::ERRMODE_SILENT

This is the default error mode. If you leave it in this mode, you'll have to check for errors in the way you're probably used to if you've used the mysql or mysqli extensions. The other two methods are more suitable for DRY programming.

PDO::ERRMODE_WARNING

This mode will issue a standard PHP warning and allow the program to continue execution. It's useful for debugging.

PDO::ERRMODE_EXCEPTION

This is the mode you want in most situations. It fires an exception, allowing you to handle errors gracefully and hide data that might help someone exploit your system. Here's an example of taking advantage of exceptions:
```
# connect to the database
try {
  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

  # UH-OH! Typed DELECT instead of SELECT!
  $DBH->prepare('DELECT name FROM people');
}
catch(PDOException $e) {
    echo "I'm sorry, Dave. I'm afraid I can't do that.";
    file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
}
```
There's an intentional error in the select statement; this will cause an exception. The exception sends the details of the error to a log file and displays a friendly (or not so friendly) message to the user.

Insert and Update

Inserting new data (or updating existing data) is one of the more common database operations. Using PHP PDO, this is normally a two-step process. Everything covered in this section applies equally to both the UPDATE and INSERT operations.

Here's an example of the most basic type of insert:
```
# STH means "Statement Handle"
$STH = $DBH->prepare("INSERT INTO folks ( first_name ) values ( 'Cathy' )");
$STH->execute();
```
You could also accomplish the same operation by using the exec() method, with one less call. In most situations, you're going to use the longer method so you can take advantage of prepared statements. Even if you're only going to use it once, using prepared statements will help protect you from SQL injection attacks.

Prepared Statements

Using prepared statements will help protect you from SQL injection.

A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.

You use a prepared statement by including placeholders in your SQL. Here are three examples: one without placeholders, one with unnamed placeholders, and one with named placeholders.
```
# no placeholders - ripe for SQL Injection!
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)");

# unnamed placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");

# named placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (:name, :addr, :city)");
```
You want to avoid the first method; it's here for comparison. The choice of using named or unnamed placeholders will affect how you set data for those statements.

Unnamed Placeholders
```
# assign variables to each place holder, indexed 1-3
$STH->bindParam(1, $name);
$STH->bindParam(2, $addr);
$STH->bindParam(3, $city);

# insert one row
$name = "Daniel"
$addr = "1 Wicked Way";
$city = "Arlington Heights";
$STH->execute();

# insert another row with different values
$name = "Steve"
$addr = "5 Circle Drive";
$city = "Schaumburg";
$STH->execute();
```
There are two steps here. First, we assign variables to the various placeholders (lines 2–4). Then, we assign values to those placeholders and execute the statement. To send another set of data, just change the values of those variables and execute the statement again.

Does this seem a bit unwieldy for statements with a lot of parameters? It is. However, if your data is stored in an array, there's an easy shortcut:
```
# the data we want to insert
$data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');

$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?, ?, ?)");
$STH->execute($data);
```
That's easy!

The data in the array applies to the placeholders in order. $data[0] goes into the first placeholder, $data[1] the second, etc. However, if your array indexes are not in order, this won't work properly, and you'll need to re-index the array.

Named Placeholders

You could probably guess the syntax, but here's an example:
```
# the first argument is the named placeholder name - notice named
# placeholders always start with a colon.
$STH->bindParam(':name', $name);
```
You can use a shortcut here as well, but it works with associative arrays. Here's an example:
```
# the data we want to insert
$data = array( 'name' => 'Cathy', 'addr' => '9 Dark and Twisty', 'city' => 'Cardiff' );

# the shortcut!
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
$STH->execute($data);
```
The keys of your array do not need to start with a colon, but otherwise need to match the named placeholders. If you have an array of arrays, you can iterate over them and simply call the execute with each array of data.

Another nice feature of named placeholders is the ability to insert objects directly into your database, assuming the properties match the named fields. Here's an example object, and how you'd perform your insert:
```
# a simple object
class person {
    public $name;
    public $addr;
    public $city;

    function __construct($n,$a,$c) {
        $this->name = $n;
        $this->addr = $a;
        $this->city = $c;
    }
    # etc ...
}

$cathy = new person('Cathy','9 Dark and Twisty','Cardiff');

# here's the fun part:
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");
$STH->execute((array)$cathy);
```
Casting the object to an array in the execute means that the properties are treated as array keys.

Selecting Data

Data is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, it's best to tell PDO how you'd like the data to be fetched. You have the following options:
- PDO::FETCH_ASSOC: returns an array indexed by column name.
- PDO::FETCH_BOTH (default): returns an array indexed by both column name and number.
- PDO::FETCH_BOUND: assigns the values of your columns to the variables set with the ->bindColumn() method.
- PDO::FETCH_CLASS: assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist.
- PDO::FETCH_INTO: updates an existing instance of the named class.
- PDO::FETCH_LAZY: combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating the object variable names as they are used.
- PDO::FETCH_NUM: returns an array indexed by column number.
- PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names.
In reality, there are three which will cover most situations: FETCH_ASSOC, FETCH_CLASS, and FETCH_OBJ. In order to set the fetch method, the following syntax is used:
```
$STH->setFetchMode(PDO::FETCH_ASSOC);
```
You can also set the fetch type directly within the ->fetch() method call.

FETCH_ASSOC

This fetch type creates an associative array, indexed by column name. This should be quite familiar to anyone who has used the mysql/mysqli extensions. Here's an example of selecting data with this method:
```
# using the shortcut ->query() method here since there are no variable
# values in the select statement.
$STH = $DBH->query('SELECT name, addr, city from folks');

# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_ASSOC);

while($row = $STH->fetch()) {
    echo $row['name'] . "\n";
    echo $row['addr'] . "\n";
    echo $row['city'] . "\n";
}
```
The while loop will continue to go through the result set one row at a time until complete.

FETCH_OBJ

This fetch type creates an object of std class for each row of fetched data. Here's an example:
```
# creating the statement
$STH = $DBH->query('SELECT name, addr, city from folks');

# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_OBJ);

# showing the results
while($row = $STH->fetch()) {
    echo $row->name . "\n";
    echo $row->addr . "\n";
    echo $row->city . "\n";
}
```
FETCH_CLASS

The properties of your object are set BEFORE the constructor is called. This is important.

This fetch method allows you to fetch data directly into a class of your choosing. When you use FETCH_CLASS, the properties of your object are set BEFORE the constructor is called. Read that again—it's important. If properties matching the column names don't exist, those properties will be created (as public) for you.

This means that if your data needs any transformation after it comes out of the database, it can be done automatically by your object as each object is created.

As an example, imagine a situation where the address needs to be partially obscured for each record. We could do this by operating on that property in the constructor. Here's an example:
```
class secret_person {
    public $name;
    public $addr;
    public $city;
    public $other_data;

    function __construct($other = '') {
        $this->address = preg_replace('/[a-z]/', 'x', $this->address);
        $this->other_data = $other;
    }
}
```
As data is fetched into this class, the address has all its lowercase a-z letters replaced by the letter x. Now, using the class and having that data transform occur is completely transparent:
```
$STH = $DBH->query('SELECT name, addr, city from folks');
$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person');

while($obj = $STH->fetch()) {
    echo $obj->addr;
}
```
If the address was '5 Rosebud,' you'd see '5 Rxxxxxx' as your output. Of course, there may be situations where you want the constructor called before the data is assigned. PDO has you covered for this, too.
```
$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'secret_person');
```
Now, when you repeat the previous example with this fetch mode (PDO::FETCH_PROPS_LATE), the address will not be obscured, since the constructor was called and the properties were assigned.

Finally, if you really need to, you can pass arguments to the constructor when fetching data into objects with PDO:
```
$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person', array('stuff'));
```
If you need to pass different data to the constructor for each object, you can set the fetch mode inside the fetch method:
```
$i = 0;
while($rowObj =  $STH->fetch(PDO::FETCH_CLASS, 'secret_person', array($i))) {
    // do stuff
    $i++
}
```
Some Other Helpful Methods

While this isn't meant to cover everything in PDO (it's a huge extension!), there are a few more methods you'll want to know in order to do basic things with PDO.
```
$DBH->lastInsertId();
```
The ->lastInsertId() method is always called on the database handle, not the statement handle, and will return the auto-incremented id of the last inserted row by that connection.
```
$DBH->exec('DELETE FROM folks WHERE 1');
$DBH->exec("SET time_zone = '-8:00'");
```
The ->exec() method is used for operations that cannot return data other than the affected rows. The above are two examples of using the exec method.
```
$safe = $DBH->quote($unsafe);
```
The ->quote() method quotes strings so they are safe to use in queries. This is your fallback if you're not using prepared statements.
```
$rows_affected = $STH->rowCount();
```
The ->rowCount() method returns an integer indicating the number of rows affected by an operation. In at least one known version of PDO, the method was not working with select statements. However, it does work properly in version PHP 5.1.6 and above.

If you're having this problem and can't upgrade PHP, you could get the number of rows with the following:
```
$sql = "SELECT COUNT(*) FROM folks";
if ($STH = $DBH->query($sql)) {
    # check the row count
    if ($STH->fetchColumn() > 0) {

    # issue a real select here, because there's data!
    }
    else {
        echo "No rows matched the query.";
    }
}
```
PHP CRUD Generators From CodeCanyon

You can save yourself hours of time by finding a PHP CRUD generator from CodeCanyon and using it in your projects. Here are five of the most popular downloads you can start using right now.

1. Laravel Multi-Purpose Application: Sximo 6

The Sximo 6 builder is based on the most popular frameworks around. It's also received a fresh update for 2021, making it as easy to use and feature-rich as possible. Some of those features include:
- database table management
- front-end and back-end templates
- module MySQL editor
- multiple images and file upload support
Try it if you're looking to save time with a CRUD PHP template.

2. PDO Crud: Form Builder & Database Management

Here's another powerful CRUD PHP generator. This PHP PDO code template does database management well. But that's not all it does. You can also use PDO CRUD to build helpful forms directly from your database tables. It's a useful feature that not many other options have.

3. Cicool: Page, Form, Rest API and CRUD Generator

Cicool is another multipurpose builder worth looking into. Not only does it offer a CRUD builder, but it also has a:
- page builder
- form builder
- rest API builder
On top of these features, you can also add extensions to Cicool and easily customize its theme.

4. PHP CRUD Generator

Easy admin panel builder? Check. Easy-to-navigate interface? Check. In-depth database analysis? Another check. This PHP CRUD generator has everything you need to build great dashboards and store your data. With different user authentication and rights management features, this PDO PHP template is worth checking out.

Conclusion

I hope this helps you migrate away from the mysql and mysqli extensions. What do you think? Are there any of you out there who might make the switch?

If you would like to build a quick CRUD interface with PHP and PDO, take a look at the following posts!
- PHP
  
  Quickly Build a PHP CRUD Interface With the PDO Advanced CRUD Generator Tool
  
  Sajal Soni
- PHP
  
  Comparing PHP Database Abstraction Layers and CRUD Plugins
  
  Sajal Soni
martijn broeders

founder/ strategic creative at shineyrock web design & consultancy
e-mail: .(JavaScript must be enabled to view this email address)
phone: 434 210 0245

Like

disLike

7

Add a comment...

Comments

Name*

Email*

location

URL

Comment {comment}

Remember my personal information Notify me of follow-up comments?