php database abstraction layer PDO

Here's how the database abstraction layer PDO works:

PDO (PHP Data Objects) is a lightweight PHP extension that provides a data access abstraction layer. Also, PDO can only be used in PHP 5.0 or above.

Here are some common predefined constants used by PDO :

PDO: : PARAM_BOOL   (integer) indicates the Boolean data type

PDO::PARAM_NULL   (integer) represents SQL

with data type NULL

PDO::PARAM_INT   (integer)   represents SQL

of the integer data type

PDO::PARAM_STR   (integer)   represents SQL

of the data type char varchar or other strings

PDO::PARAM_LOB   (integer)   represents SQL

of the object data type

The method specified by PDO::FETCH_LAZY   (integer) should return each row of the result set as the variable name of an object, corresponding to its field name

PDO::FETCH_ORI_NEXT   (integer) takes the next line of the result set,

PDO::FETCH_ORI_PRIOR   (integer)   takes the preceding line of the result set

PDO::FETCH_ORI_FIRST   (integer)   takes the first row of the result set

PDO::FETCH_ORI_LAST   (integer) takes the last line of the result set

PDO::ATTR_PERSISTENT   (integer)   creates a persistent connection instead of creating a new connection

Basic usage of PDO:

Connect to the database using PDO (only MySQL is used here) :

 
<?php 
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass); 
?> 

The following code deals with an MySQL connection error:

 
<?php 
try { 
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass); 
foreach($dbh->query('SELECT * from FOO') as $row) { 
print_r($row); 
} 
$dbh = null; 
} catch (PDOException $e) { 
print "Error!: " . $e->getMessage() . "<br/>"; 
die(); 
} 
?> 

Here are two examples of repeated insert statements:

 
<?php 
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); 
$stmt->bindParam(':name', $name); 
$stmt->bindParam(':value', $value); 

// insert one row 
$name = 'one'; 
$value = 1; 
$stmt->execute(); 

// insert another row with different values 
$name = 'two'; 
$value = 2; 
$stmt->execute(); 
?> 

 
<?php 
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); 
$stmt->bindParam(1, $name); 
$stmt->bindParam(2, $value); 

// insert one row 
$name = 'one'; 
$value = 1; 
$stmt->execute(); 

// insert another row with different values 
$name = 'two'; 
$value = 2; 
$stmt->execute(); 
?> 

Query the database:

 
<?php 
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?"); 
if ($stmt->execute(array($_GET['name']))) { 
while ($row = $stmt->fetch()) { 
print_r($row); 
} 
} 
?>