You are here

Home » Blogs » d.cochran's blog

PDO Connection Basics and Examples using PHP

published by d.cochran on Fri, 10/18/2013 - 08:00

Here are some PDO connection method basics with examples.

You will often commonly see the connection method being referred to as $dbh and $sth. These simply stand for "database handler" and "statement handler".

Connecting to MySQL with PDO Object

//inc-db-connect.php
 
define('DATABASE','database_name_here');
define('DATABASE_USER','database_user_here');
define('DATABASE_PASSWORD','database_password_here');
 
try {
  $dbh = new PDO('mysql:host=localhost;dbname='.DATABASE, DATABASE_USER, DATABASE_PASSWORD,array(
    PDO::ATTR_PERSISTENT => true,
  ));
} catch (PDOException $e) {
  print "Error!: " . $e->getMessage() . "<br/>";
  die();
}

Connecting to the Database from within a Function

include('inc-db-connect.php');
 
function someFunction() {
 
  global $dbh;
 
  $sth = $dbh->query('SELECT * FROM table');
 
}

Passing Parameters to the $dbh Object
By default the PDO execute() function treats all PARAMS passed as STRING VALUES unless defined using bindParam().

//PASSING ONLY STRING VALUES:
//(using named parameters)
 
$sth = $dbh->prepare('SELECT * FROM table WHERE name = :username && password = :pass');
$sth->execute(array(':username' => $username, ':pass' => $password));
 
//OR
//(using named placeholders)
//$values must be ordered respectively
 
$values = array();
$values[] = $username;
$values[] = $password;
 
$sth = $dbh->prepare('SELECT * FROM table WHERE name = ? && password = ?');
$sth->execute($values);
 
//PASSING MIXED VALUES:
//(using named parameters)
 
$sth = $dbh->prepare('SELECT * FROM table WHERE name = :username && user_id = :id');
$sth->bindParam(':username', $username, PDO::PARAM_STR);
$sth->bindParam(':id', $user_id, PDO::PARAM_INT);
$sth->execute();
 
//OR
//(using named placeholders)
 
$sth = $dbh->prepare('SELECT * FROM table WHERE name = ? && user_id = ?');
$sth->bindParam(1, $username, PDO::PARAM_STR);
$sth->bindParam(2, $user_id, PDO::PARAM_INT);
$sth->execute();

Returning Values from an executed PDO Query

//FETCHING ONLY ONE RECORD:
 
$sth = $dbh->prepare('SELECT db_field FROM table WHERE user_id = :id');
$sth->bindParam(':id', $user_id, PDO::PARAM_INT);
$sth->execute();
$user = $sth->fetch();
 
//display an error if one occured
$error = $sth->errorInfo();
if ($error[1]) print_r($error);
 
echo $user['db_field'];
 
//FETCHING MANY RECORDS:
 
$sth = $dbh->prepare('SELECT * FROM users_table WHERE company_id = :id');
$sth->bindParam(':id', $company_id, PDO::PARAM_INT);
$sth->execute();
$users = $sth->fetchAll();
 
//display an error if one occured
$error = $sth->errorInfo();
if ($error[1]) print_r($error);
 
foreach($users as $row => $field) :
 
  //$row = the current record
  //$field = an array of DB fields for the current record
 
  echo $field['user_id'];  //the current records user_id
  echo $field['username']; //the current records username, etc...
 
endforeach;

Returning the Total Rows for PDO Query

$sth = $dbh->prepare('SELECT * FROM users_table WHERE company_id = :id');
$sth->bindParam(':id', $company_id, PDO::PARAM_INT);
$sth->execute();
$ttl_Users = $sth->rowCount();

Return Last Inserted ID with PDO

$sth = $dbh->prepare('INSERT INTO table (username) VALUES (:username)');
$sth->execute(array(':username' => $username));
$user_id = $dbh->lastInsertId();

Tags: