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.