<?php

$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'connecttutorial';
$charset = 'utf8';

$dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";

$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try{
    $pdo = new PDO($dsn, $user, $pass, $opt);
}
catch(PDOException $e) {
    die('Could not connect to the database using credentials given. Error code: '.$e->getCode());
}

// CREATE -------------------------------------------------------------------------------------------
try {
    $sql = "INSERT INTO books (collection, title, author) VALUES (?,?,?)";
    $pdo->prepare($sql)->execute(['World of Warcraft','War crimes', 'Christie Golden']);

    $sql = "INSERT INTO books (collection, title, author) VALUES (:collection,:title,:author)";
    $pdo->prepare($sql)->execute(['collection' => 'Warcraft','title' => 'The Shattering', 'author' => 'Christie Golden']);
    // get id for the last insterted row
    $id = $pdo->lastInsertId();
}
catch(PDOException $e) {
    die('There was a problem when inserting. Returned message was: '.$e->getMessage());
    var_dump($e);
}

// READ --------------------------------------------------------------------------------------------
try {
    $stmt = $pdo->query("SELECT * FROM books ORDER BY author ASC, title ASC");
    foreach($stmt as $row) {
        echo $row['collection'].': '.$row['title'].' - '.$row['author'].'<br />';
    }
}
catch (PDOException $e) {
    die('There was a problem when trying to read from table. Returned message was: '.$e->getMessage());
}

// READ ONE ROW ------------------------------------------------------------------------------------
try {
    $stmt = $pdo->query("SELECT * FROM books LIMIT 1");
    $row = $stmt->fetch();
    /**
     * we can also fetch() another way:
     * fetch(PDO::FETCH_NUM) - every field has a numeric key
     * fetch(PDO::FETCH_ASSOC) - every field has the column name as key (default, as set in $opt)
     * fetch(PDO::FETCH_BOTH) - both of the above
     * fetch(PDO::FETCH_OBJ) - returns object
     * fetch(PDO::FETCH_LAZY) - all three
     * fetch(PDO::FETCH_CLASS, 'Book') - the returned row is of a certain class (also works on multiple rows)
     */
    var_dump($row);
    echo '<br />';
}
catch (PDOException $e) {
    die('There was a problem when trying to read one row from table. Returned message was: '.$e->getMessage());
}

// READ ONLY ONE COLUMN FROM A RETURNED ROW --------------------------------------------------------
try {
    $stmt = $pdo->prepare("SELECT title FROM books WHERE author LIKE ? LIMIT 1");
    $stmt->execute(['Christie Golden']);
    $bookTitle = $stmt->fetchColumn();
    echo 'Title: '.$bookTitle.'<br />';
}
catch (PDOException $e) {
    die('There was a problem when trying to read one row column from table. Returned message was: '.$e->getMessage());
}

// READ COUNT OF RESULT-----------------------------------------------------------------------------
try {
    $count = $pdo->query("SELECT count(*) FROM books")->fetchColumn();
    echo 'Total rows: '.$count.'<br />';
}
catch (PDOException $e) {
    die('There was a problem when trying to read one row column from table. Returned message was: '.$e->getMessage());
}

// UPDATE ------------------------------------------------------------------------------------------
try {
    $sql = "UPDATE books SET collection = ? WHERE collection = ?";
    $pdo->prepare($sql)->execute(['World of Warcraft', 'Warcraft']);
}
catch (PDOException $e) {
    die('There was a problem when trying to update. Returned message was: '.$e->getMessage());
}

// UPDATE WITH ROWS COUNT --------------------------------------------------------------------------
try {
    $stmt = $pdo->prepare("UPDATE books SET collection = ? WHERE collection = ?");
    $stmt->execute(['Warcraft', 'World of Warcraft']);
    $affectedRows = $stmt->rowCount();
    echo 'Affected rows: '.$affectedRows.'<br />';
}
catch (PDOException $e) {
    die('There was a problem when trying to update. Returned message was: '.$e->getMessage());
}

// DELETE ------------------------------------------------------------------------------------------
try {
    $sql = "DELETE FROM books WHERE title LIKE ?";
    $pdo->prepare($sql)->execute(['%Crimes%']);
}
catch (PDOException $e) {
    die('There was a problem when trying to delete. Returned message was: '.$e->getMessage());
}

// DELETE WITH ROW COUNT --------------------------------------------------------------------------
try {
    $count = $pdo->prepare("DELETE FROM books");
    $count->execute();
    $deletedRows = $count->rowCount();
    echo ('Deleted rows: '.$deletedRows);
}
catch (PDOException $e) {
    die('There was a problem when trying to delete. Returned message was: '.$e->getMessage());
}