Chapter 8: PDO - the Right Way to Access Databases in PHP

by Parham Doustdar

Why not mysql and mysqli?

PDO is object-oriented

Abstraction

It allows parameter binding

  • 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

The mysql extension is no longer supported!

Verifying the Existence of PDO

<php
phpinfo();
php -i | grep 'pdo'

Installation of PDO

Fedora, RedHat and CentOS

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

Make Sure to Refer to the Desired Repository

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

Debian and Ubuntu

sudo apt-get install php5-mysqlnd

Windows

;extension=php_pdo_mysql.dll

Beginning with PDO: a High-level Overview

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

Connecting to your database

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

Queries

Executing queries

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

Fetching Query Results

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

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

Fetching Modes: Assoc, Num, Obj and class

  • 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.

This List Is Not Comprehensive

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

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

Always Choose a Fetch Mode

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;
    }

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

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

Prepared Statements and Binding Parameters

$statement = $connection->prepare('Select * From users Where id = :id');
$statement = $connection->prepare('Select * From users Where id = :id');
$id = 5;
$statement->execute([
    ':id' => $id
]);
$results = $statement->fetchAll(PDO::FETCH_OBJ);

The Benefits of Parameter Binding

$results = mysql_query(sprintf("SELECT * FROM users WHERE name='%s'", 
        mysql_real_escape_string($name)
    )
) or die(mysql_error());
$statement = $connection->prepare('Select * FROM users WHERE name = :name');
$results = $connection->execute([
    ':name' => $name
]);
$statement = $connection->prepare('SELECT * FROM users WHERE name = ?');
$results = $connection->execute([$name]);
$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);
}

Binding Values to an IN Clause

$names = explode(',', $names);
$statement = $connection->prepare('SELECT * FROM users WHERE name IN (:names)');
$statement->execute([':names' => $names]);
$names = explode(',', $names);
$placeholder = implode(',', array_fill(0, count($names), '?'));

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

Providing Datatypes When Binding Parameters

  • 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.
$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);
}

bindValue()'s Paremeter in the Above

Conclusion

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.17.155.54