PDO Extension in PHP

Introduction

This article explains the PHP Data Object (PDO) extension in PHP. The PDO extension defines a light-weight, consistent interface for accessing databases in PHP. Every database driver that implements the PDO interface can expose database-specific options as regular extension functions. Note that you simply cannot perform any database functions that utilize the PDO extension by itself. You need to use a database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which implies that, despite that database you utilize, you employ identical functions to issue queries and fetch information. PDO doesn't provide an information abstraction; it does not rewrite SQL or emulate missing options. You must use a full-blown abstraction layer if you would like that facility. PDO ships with PHP 5.1, and is accessible as a PECL extension for PHP 5.0.

Installation and Configuration

The following is installation and configuration of it:

• Requirements
• Installation
• Runtime Configuration
• Resource Types

Requirements: To build this extension there is no need for external libraries.

Installation: There are the following two types of PDO installation:

• Installation for Linux: PDO and also the PDO_SQLITE driver is enabled by default as of PHP 5.1.0. You'll need to be compelled to enable the PDO driver for your info of choice. Consult the documentation for database-specific PDO drivers to seek out additional this. When putting in PDO as a shared module, the php.ini file must be updated for the PDO extension to be loaded mechanically once PHP runs. You may additionally get to modify any information specific drivers there too. Certify that they're listed when the pdo.so line, since PDO should be initialized before the database-specific extensions are often loaded, such as (extension=pdo.so).
• Installation for Windows: PDO and every one the key drivers ship with PHP as shared extensions, and easily would like to be activated by redaction of the php.ini file, otherwise you will choose the other database-specific DLL files and either use dl() to load them at runtime, or enable them in php.ini below php_pdo.dll.

 

Note: Remember that when creating changes to your php.ini file you may need to restart PHP for your new configuration directives to be applied.

Runtime Configuration: These functions are affected by php.ini configuration settings.

Resource Types: Extension has no resource types defined.

Connection Management

Connections measure established by making instances of the PDO base category. It does not matter what driver you would like to use; you mostly use the PDO category name.

Some PDO Classes

 

Classes	Description
PDO::commit	Commits a transaction
PDO::errorInfo	Creates a PDO instance representing a connection to a database
PDO::lastInsertId	Returns the ID of the last inserted row or sequence value
PDO::prepare	Prepares a statement for execution and returns a statement object
PDO::query	Executes an SQL statement, returning a result set as a PDO Statement object
PDO::rollBack	Rolls back a transaction
PDO::setAttribute	Sets an attribute

PDO Statements

Statements	Description
PDOStatement::fetch	Fetches the next row from a result set
PDOStatement::execute	Executes a prepared statement
PDOStatement::fetchAll	Returns an array containing all of the result set rows
PDOStatement::rowCount	Returns the number of rows affected by the last SQL statement
PDOStatement::setAttribute	Sets a statement attribute
PDOStatement::getAttribute	Retrieves a statement attribute
PDOStatement::bindParam	Binds a parameter to the specified variable name
PDOStatement::bindValue	Binds a value to a parameter

Example

This example shows connecting to the MySQL database.

<?php

$username='root';

$password='';

$database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password);

if($database){

echo 'connection establish';

}else{

echo 'connection not establish try again';

}

?>

 

Output

If there are any connection errors then a PDO exception object is thrown. You'll catch the exception if you wish to handle the error condition, otherwise you could choose to leave it for the application outside the exception handler that you simply originated via "set_exception_handler()".

Example

This example is used for handling connection errors.

 

<?php

$username='root';

$password='';

try {

    $database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password);

    foreach($database->query('SELECT * from student') as $row) {

        echo "<pre>";print_r($row);

    }

    $database = null;

} catch (PDOException $a) {

    print "Error!: " . $a->getMessage() . "<br/>";

    die();

}

?>

 

Output

 

Example

This example shows closing a connection.

<?php

$username='root';

$password='';

$database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password);

$database=null;

?>

 

And this example is used for a persistence connection. Many web applications are like creating persistent connections to database servers. Persistent connections don't seem to be closed at the time of the script, however measure cached and re-used once another script requests a association utilize constant credentials.

<?php

$database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password, array(

    PDO::ATTR_PERSISTENT => true

));

?>

 

Prepare statements and store-procedure

 

First, an example of repeated inserts using prepared statements.

 

Example

 

<?php

$username='root';

$password='';

$database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password);

$query = $database->prepare("INSERT INTO course (id, course, desc) VALUES (:id, :course,:desc)");

$query->bindParam(':id', $id);

$query->bindParam(':course', $course);

$query->bindParam(':desc', $desc);

// insert one row

$id = '7';

$course = 'MCA';

$desc = 'this is good description for entry';

$query->execute();

// insert another row with different values

$id = '8';

$course = 'MCom';

$desc = 'this is good description for entry';

$query->execute();

?>
 

Example

This example shows fetching data from a database used to prepare a query.

<?php

$username='root';

$password='';

$database = new PDO('mysql:hostname=localhost;dbname=cms', $username, $password);

$stmt = $database->prepare("SELECT * FROM student where name = vinod");

if ($stmt->execute(array($_GET['name']))) {

  while ($row = $stmt->fetch()) {

    print_r($row);

  }

}

?>

 

For more examples or details related to PHP Data Object please visit the http://www.php.net/ website.