Tag: php pdo

Hey Folks,

The PDO (PHP Data Objects) is an extension which defines a lightweight, consistent interface for accessing databases in PHP. PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new Object Oriented features in the core of PHP 5, and so will not run with earlier versions of PHP.

Note : I assume you already have a PDO configured PHP version. If not, Please click here to see the installation process.

PDO Connections

Connecting to Database (Eg. MySQL) :

<?php $dbconn = new PDO('mysql:host=localhost;dbname=testdb', $username, $password); ?>

//Note : If there are any connection errors, a PDOException object will be thrown.

Handling Errors In Connection :

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

Upon successful connection to the database, an instance of the PDO class is returned in your script. The connection remains active for the lifetime of that PDO object.

Closing a connection :

<?php
$dbconn = new PDO('mysql:host=localhost;dbname=testdb', $username, $password);
// use the connection here


// and now we're done; close it
$dbconn= null;
?>

Persistent connections :

<?php
$dbconn = new PDO('mysql:host=localhost;dbname=testdb', $username, $password, array(
    PDO::ATTR_PERSISTENT => true //If you wish to use persistent connections, you must set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor.
));
?>

Multiple database connections :

try {
  $dbconn1 = new PDO('mysql:host=localhost;dbname=testdb1', $username, $password);
  $dbconn2 = new PDO('mysql:host=localhost;dbname=testdb2', $username, $password);
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

You can execute the query like this :

<?php
$result = $dbconn->query("select * from tablename");
foreach ($result as $row) {
  echo $row['fieldname'] . "\n";
}
?>

Fetching data using prepared statements 1 :

<?php
$query= $dbconn->prepare("SELECT * FROM tablename where fieldname = ?"); // The user input is automatically quoted, so there is no risk of a SQL injection attack. 
if ($query->execute(array($_POST['name']))) {
  while ($row = $query->fetch()) {
    print_r($row);
  }
}
?>

Fetching data using prepared statements 2 :

$query= $dbconn->prepare("select * from tablename where id = :id");
$query->execute(array(':id' => 555));
$row = $query->fetch();

Calling a stored procedure with an output parameter :

<?php
$query = $dbconn->prepare("CALL sp_returns_string(?)");
$query->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

// call the stored procedure
$query->execute();

print "procedure returned $return_value\n";
?>

I have covered only some basics here. You’ll get detailed explanation of various other functions of PDO from the php.net Website.

Cheers,
JENSon.