PDO

• MySQL is a database system used on the web
  
• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL supports standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by Oracle Corporation
• MySQL is named after co-founder Monty Widenius's daughter: My

• Databases are useful when storing information categorically. A company may have a database with the following tables:
• Employees
  
• Products
• Customers
• Orders

• Open /Applications/MAMP/bin/php/php5.5.3/conf/php.ini
• Find "error_reporting"
• Make sure it = E_ALL
• Find "display_errors"
• Make sure it = On
• Find "display_startup_errors"
• Make sure it = On
• Set opcache.revalidate_freq=0
  
• If that doesn't stop caching, comment out all opcache related lines i.e. put a # in front of the line

<?php
  
  $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

?>

• $dbh = "Database handle"
• $sth = "Statement handle"
• $stmt = "Statement"

<?php
  $host  = "localhost";
  $user = "chris";
  $pass = "p4ssw0rd";
  $dbname = "dc2s10";

  try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    echo 'Connected to database';
  } catch(PDOException $e) {
    echo "<p>Oop, something went wrong.</p>";
    die("DB Error: ". $e->getMessage()); 
  }?>

<?php
  $host  = "localhost";
  $user = "chris";
  $pass = "p4ssw0rd";
  $dbname = "dc2s10";

  try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    echo 'Connected to database';
  } catch(PDOException $e) {
    echo "<p>Oop, something went wrong.</p>";
    file_put_contents('PDOErrors.txt', $e->getMessage()."\n", FILE_APPEND);
    die("DB Error: ". $e->getMessage()); 
  }
?>

<?php
  $host  = "localhost";
  $user = "chris";
  $pass = "p4ssw0rd";
  $dbname = "dc2s10";

  try {
    // deliberately trigger an error using "msql" instead of "mysql"
    $dbh = new PDO("msql:host=$host;dbname=$dbname", $user, $pass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    echo 'Connected to database';
  } catch(PDOException $e) {
    echo "<p>Oop, something went wrong.</p>";
    file_put_contents('PDOErrors.txt', $e->getMessage()."\n", FILE_APPEND);
    die("DB Error: ". $e->getMessage()); 
  }
?>

<?php
  // truncated for example purposes
  $stmt = $dbh->query('SELECT * FROM table');
  $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

  foreach($results as $row) {
    echo $row['field1'].' '.$row['field2']; // etc...
    // In a users table these could be 'first_name', 'last_name', 'email' etc
  }
?>

<?php
  $stmt = $dbh->query('SELECT * FROM table');
  $row_count = $stmt->rowCount();
  echo $row_count.' rows selected';
?>

<?php
  $result = $dbh->exec("INSERT INTO table(firstname, lastname) VALUES('John', 'Doe')");
?>

 $insertId = $dbh->lastInsertId();

<?php
  $affected_rows = $dbh->exec("UPDATE table SET field='value'");
  echo $affected_rows.' were affected'
?>

<?php  $affected_rows = $dbh->exec("UPDATE table SET field='value' WHERE id='id'");
  echo $affected_rows.' were affected'
?>

<?php
  /* Delete specific rows from the USERS table */
  $del = $dbh->prepare("DELETE FROM users WHERE first_name = 'Chris'");
  $del->execute();

  /* OR */

  /* Delete all rows from the USERS table */
  $del = $dbh->prepare('DELETE FROM users');
  $del->execute();

  /* Return number of rows that were deleted */
  echo("Return number of rows that were deleted:\n");
  $count = $del->rowCount();
  echo("Deleted $count rows.\n");
?>

$stmt = $dbh->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->execute(array($id, $name));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

• The prepare method sends the query to the server, and it's compiled with the '?' placeholders to be used as expected arguments.
  
• The execute method sends the arguments to the server and runs the compiled statement. Since the query and the dynamic parameters are sent separately, there is no way that any SQL that is in those parameters can be executed... so NO SQL INJECTION can occur! This is a much better and safer solution than concatenating strings together.
  

$stmt = $dbh->prepare("SELECT * FROM table 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);

$stmt = $dbh->prepare("SELECT * FROM table 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);

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

$stmt = $dbh->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));
$affected_rows = $stmt->rowCount();

$stmt = $dbh->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();

$stmt = $dbh->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();

$time = 'NOW()';
$name = 'BOB';
$stmt = $dbh->prepare("INSERT INTO table(`time`, `name`) VALUES(?, ?)");
$stmt->execute(array($time, $name));

$name = 'BOB';
$stmt = $dbh->prepare("INSERT INTO table(`time`, `name`) VALUES(NOW(), ?)");
$stmt->execute(array($name));

$name = 'BOB';
$password = 'password';
$stmt = $dbh->prepare("INSERT INTO table(`hexvalue`, `password`) VALUES(HEX(?), PASSWORD(?))");
$stmt->execute(array($name, $password));

$stmt = $dbh->prepare("SELECT field FROM table WHERE field LIKE %?%");
$stmt->bindParam(1, $search, PDO::PARAM_STR);
$stmt->execute();

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

$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $dbh->prepare("INSERT INTO table(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
  $stmt->execute();
}

• Calling beginTransaction() turns off autocommit mode.
  
• While autocommit mode is turned off, changes made to the database via the PDO object instance are not committed until you end the transaction by calling PDO::commit().
  
• Calling PDO::rollBack() will roll back all changes to the database and return the connection to autocommit mode.

try {
    $dbh->beginTransaction();
 
    $dbh->exec("SOME QUERY");
 
    $stmt = $dbh->prepare("SOME OTHER QUERY?");
    $stmt->execute(array($value));
 
    $stmt = $dbh->prepare("YET ANOTHER QUERY??");
    $stmt->execute(array($value2, $value3));
 
    $dbh->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $dbh->rollBack();
    echo $ex->getMessage();
}

$dbh->exec("CREATE DATABASE IF NOT EXISTS `$db`;
  CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
  GRANT ALL ON `$db`.* TO '$user'@'localhost';
  FLUSH PRIVILEGES;")
or die(print_r($dbh->errorInfo(), true));

$dbh->exec("CREATE TABLE IF NOT EXISTS $table (
  first_name VARCHAR(150),
  last_name  VARCHAR(150),
  created_at VARCHAR(100)")