<?php
/* a first program, illustrating how to open, access, and print data
   from a database table, using pdo */

/* establishing a connection to the database */
require "credentials.php"; // for $user and $password
$server   = 'localhost';
$database = 'hwalker';

try {
    $pdo = new PDO("mysql:host=localhost;dbname=$database", $user, $password);

    // set the PDO error mode to throw exception when run-time error encountered
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // provide as much protection as possible to counteract possible injection attacks
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

}
catch (Exception $e) {
    echo "error connecting to $database \n";
    echo "full error message " . $e->getMessage() . "\n";
    echo "exiting\n";
    exit;
}

echo "connected to database '$database'\n";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *  INSERT Queries          INSERT Queries          INSERT Queries     
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

// Approach 1:  using $pdo->query
try {
    $sqlQuery = 'INSERT INTO faculty ';
    $sqlQuery .= '(first, last, dept, email, phone, office) ';
    $sqlQuery .= 'VALUES ("H", "MacKay", "Far Out", ';
    $sqlQuery .=     '"walker@grinnell.edu", "n/a", 199)';
    echo "query:  $sqlQuery \n";
    $result = $pdo->query($sqlQuery);
}
catch (Exception $e) {
    echo "error on query:  $sqlQuery \n";
    echo "error message:  $e->getMessage \n";
    echo "exiting \n";
    exit;
}

// Approach 2:  using $pdo-prepare and $pdo->execute
try {
    $sqlQuery = 'INSERT INTO faculty ';
    $sqlQuery .= 'set first = "Alter", ';
    $sqlQuery .=    'last   = "Ego", ';
    $sqlQuery .=    'dept   = "Consultant", ';
    $sqlQuery .=    'email  = "n/a", ';
    $sqlQuery .=    'phone  =  "n/a", ';
    $sqlQuery .=    'office = 3141592';
    echo "query:  $sqlQuery \n";
    $preparedQuery = $pdo->prepare($sqlQuery);
    $preparedQuery->execute();
}
catch (Exception $e) {
    echo "error on query:  $sqlQuery \n";
    echo "error message:  $e->getMessage \n";
    echo "exiting \n";
    exit;
}


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *  check database          check database          check database            
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
try {
    $sqlQuery = 'SELECT * FROM faculty';
    echo "query:  $sqlQuery \n";
    $result = $pdo->query($sqlQuery);
}
catch (Exception $e) {
    echo "error on query:  $sqlQuery \n";
    echo "error message:  $e->getMessage \n";
    echo "exiting \n";
    exit;
}

/* report results */
while ($row = $result->fetch()) {
   echo $row['first'] . " " . $row['last'] . "\n";
}

/* closing the database connection */
$pdo = null;
echo "database connection closed\n";

?>