PHP PDO - Introduction and Connecting to Databases

Php-mysql Course

PDO (PHP Data Objects) is a PHP extension for accessing databases in PHP.
PDO uses OOP characteristics (Object Oriented Programming) available since PHP 5.1.
Because PDO is working with classes and objects, you must be familiar with the PHP object-oriented programming.
PDO can work with the following databases:

• MySQL
• PostgreSQL
• SQLite 2 & 3
• Firebird
• Informix (IBM Informix Dynamic Server)
• ODBC
• Oracle
• DBLM: FreeTDS / Sybase / MS-SQL
• IBM (IBM DB2)

- One of the PDO advantages is that you can use similar functions for processing queries to databases, regardless of their type (of those mentioned above).
The PHP scripts that use PDO to connect to a database perform in general the following operations:

1. Connect to the database server by calling new PDO(), that creates an object for working with that database.
2. Implementing PDO specific functions to perform queries to the database.
3. Getting and processing the returned data.
4. Disconnect from the database server.

- To see if the PDO is available in your PHP server, for your database, you can check with phpinfo(), where it's a section for PDO, or with this method: PDO::getAvailableDrivers()

foreach(PDO::getAvailableDrivers() as $driver) {
  echo $driver.'<br />';
}

Connecting to database

Any interaction with a database begins by connecting to it. Depending on the type of database, the connection is made by creating an instance object, with: new PDO(). After the connection is created, we apply PDO methods to get and process data. These methods are largely the same, regardless of the connected database. Here is how to connect to some of these database:

Connecting to MySQL

<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- The $conn variable is the PDO object that will contain the data received from MySQL server. The PDO methods for processing data will be aplied to this object.
Notice this statement: try() ... catch() This is used to not expose important connection data that can be displayed in case of errors.
        - For example, using this technique if your database name is incorrect, returns something like this:         - If the name or password is wrong, show something similar to: But if you use directly:

<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

$conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
?>

        - The error message would display more data, including password, as shown below: • So it is recommended to apply try() ... catch().

Connecting to PostgreSQL

<?php
try {
  $conn = new PDO("pgsql:host=localhost port=5432 dbname=pdo", "username", "password");
  echo "PDO connection object created";
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to SQLite

When PDO is used with SQLite, you specify only the path to your file with the database. If that file not exist, it will try to create it.

<?php
try {
  // connect to SQLite database
  $conn = new PDO("sqlite:/path/to/database.sdb");
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Firebird

Firebird is a database used in Windows.

<?php
try {
  $conn = new PDO("firebird:dbname=localhost:C:\Programs\Firebird\DATABASE.FDB", "SYSDBA", "masterkey");
}   
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Informix

<?php
try {
  $conn = new PDO("informix:DSN=InformixDB", "username", "password");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to Oracle

<?php
try {
  $conn = new PDO("OCI:dbname=accounts;charset=UTF-8", "username", "password")
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to ODBC

There are many ODBC connections that can be created. Here's how to connect to a MS Access database called "accounts".

<?php
try {
  $conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to DBLIB

Another database for Windows.

<?php
$hostdb = "localhost";
$port   = 10060;
$namedb   = "my_database";
$userdb = "username";
$passdb = "password";

try {
  $conn = new PDO ("dblib:host=$hostdb:$port;dbname=$namedb","$userdb","$passdb");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Connecting to IBM

The following example shows how to connect to a IBM DB2 database, named "accounts".

<?php
try {
  $conn = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=accounts; HOSTNAME=1.2.3,4;PORT=56789;PROTOCOL=TCPIP;", "username", "password");
}
catch (PDOException $e) {
  echo $e->getMessage();
}
?>

Close the connection to database

Normally, PHP closes the connection to the database after the script was executed. But the disconnection can be done intentionally, attributing the null value to the PDO object, as it's shown in the example below.
The instance of that object will be destroyed, so, this method should be added after all the instructions at the PDO object were written.
    -Disconnecting with this method is useful because it releases the memory used.

<?php
// Connection data (server_address, database, username, password)
$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';

// Display message if successfully connect, otherwise retains and outputs the potential error
try {
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  echo 'Connected to database';
     /* Instructions for working with $conn object */

  $conn = null;         // Close the connection
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

In the next lesson it is presented how to add data in a MySQL database, INSERT, UPDATE, and DELETE, using the PDO exec() method.

<img src="http://coursesweb.net/imgs/webcourses.gif" width="191" height="63" alt="Courses-Web" />

#id {
  font-style: italic;
}

$(document).ready(function() {
  $(".a_class").click(function(){ $(this).hide("slow"); });
});

function fname($a, $b) {
  echo $a * $b;
}