Monday, 28 December 2015

How to implement PDO in PHP

Introduction PHP PDO 

Connecting to SQL:
1. new PDO("sqlsrv:Server=$servername; Database=$dbname",$username,$password);

Connecting to Oracle:
       1. new PDO("OCI:dbname=accounts;charset=UTF-8","username","password");

Connecting to PgSQL:
1. $db = new PDO("pgsql:dbname=pdo;host=localhost","username", "password");


Connecting to MySQL:
1. $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');


2. $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password',
array(PDO::ATTR_EMULATE_PREPARES => false,
                                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

3. $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Error Handling  
try {
   $stmt = $db->query("SELECT * FROM Employee");
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $ex) {
   echo "An Error occured!".$ex->getMessage();
}

Select Statements
$stmt = $db->query('SELECT * FROM Employee');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['field1'].' '.$row['field2']; //etc...
}

or

$stmt = $db->query('SELECT * FROM Employee');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Note: 
1. Use of PDO::FETCH_ASSOC in the fetch() and fetchAll() return the rows as an associative array with the field names as keys.
2. PDO::FETCH_NUM returns the row as a numerical array.

Getting Row Count
$stmt = $db->query('SELECT * FROM Employee');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Insert Id
$result = $db->exec("INSERT INTO Employee(firstname, lastname)
                                            VALUES('Johny', 'Deep')");
$insertId = $db->lastInsertId();

Update Query
$affected_rows = $db->exec("UPDATE Employee SET firstname='John'");
echo $affected_rows.' were affected'

Statements With Parameters
$stmt = $db->prepare("SELECT * FROM Employee WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Bind parameters
$stmt = $db->prepare("SELECT * FROM Employee WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

bind with named parameters
$stmt = $db->prepare("SELECT * FROM Employee WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

or
$stmt = $db->prepare("SELECT * FROM Employee WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

or
$stmt = $db->prepare("SELECT field FROM Employee WHERE field LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();

Executing prepared statements in a loop
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO Employee(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
  $stmt->execute();
}

2 comments:

  1. Getting the Last Insert Id
    $result = $db->exec("INSERT INTO Employee(firstname, lastname)
    VAULES('Johny', 'Deep')");
    $insertId = $db->lastInsertId();
    Please correct the spelling mistake VAULES Should be Values

    ReplyDelete