PDO STATMENT

<?php
// Connect With PDO
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'username', 'password');

// 2ND METHOD CONNECT WITH PDO WITH EXTRA PARAMETER
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

// 3RD METHOD CONNECT WITH PDO WITH EXTRA PARAMETER

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








// Query

// By TRY CATCH
try {
    //connect as appropriate as above
    $db->query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
    some_logging_function($ex->getMessage());
}



// QUERY WITH FUNCTION
function getData($db) {
   $stmt = $db->query("SELECT * FROM table");
   return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

//then much later
try {
   getData($db);
} catch(PDOException $ex) {
   //handle me.
}



// Select With PDO
// FIRST METHOD
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

// 2ND METHOD
$stmt = $db->query('SELECT * FROM table');

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

// 3RD METHOD
$stmt = $db->query('SELECT * FROM table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//use $results



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

// Getting the Last Insert Id
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId()


// PDO UPDATE DELETE
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows.' were affected';


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


// Running Statements With Parameters
$stmt = $db->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);

// Named Placeholders
$stmt = $db->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);

// INSERT, DELETE, UPDATE Prepared Queries
$stmt = $db->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 = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();

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

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