<?php
highlight_file(__FILE__); // This is now a self-documenting file!
echo "<h2>Code At the Top of File Produces Output At the Bottom</h2>";
//****************************************************************//
//**Create a table in the serverside db with the following specs**//
//********* id of type int, auto increment, primary key **********//
//****************** animal_type of type varchar *****************//
//****************** animal_name of type varchar *****************//
//*************** favourite_colour of type varchar ***************//
//****************************************************************//
//
//CONNECTING TO DATABASE
//
require('config.php');
try {
//Creates a new connection to the database
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
//Sets the error handling mode to display exceptions
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch(PDOException $e) {
//echos out the error message
echo $e->getMessage();
}
//empties the table each time the script is ran
require('empty_db.php');
//
//INSERTING INTO DATABASE TABLE
//
//to be more secure from sql injections we create an associative array to
//store the values you wish to add to the table
// **using named placeholders**
$new_row = array('animal_type' => 'Kookaburra', 'animal_name' => 'Gumbo', 'favourite_colour' => 'Powder Blue');
try {
//prepare the INSERT statement before actually executing it
$STH = $DBH->prepare("INSERT INTO animals (animal_type, animal_name, favourite_colour) VALUES (:animal_type, :animal_name, :favourite_colour)");
//after preparing the INSERT statement, execute it to save to db
//passing in your associative array to be parsed and inserted by the method
$STH->execute($new_row);
} catch(PDOException $e) {
//echos out the error message
echo $e->getMessage();
}
//a couple more rows to demonstrate with
$new_row = array('animal_type' => 'Kangaroo', 'animal_name' => 'Jack', 'favourite_colour' => 'Gamboge');
try {
$STH = $DBH->prepare("INSERT INTO animals (animal_type, animal_name, favourite_colour) VALUES (:animal_type, :animal_name, :favourite_colour)");
$STH->execute($new_row);
} catch(PDOException $e) {
echo $e->getMessage();
}
$new_row = array('animal_type' => 'Wallaby', 'animal_name' => 'Wallace', 'favourite_colour' => 'Razzmatazz');
try {
$STH = $DBH->prepare("INSERT INTO animals (animal_type, animal_name, favourite_colour) VALUES (:animal_type, :animal_name, :favourite_colour)");
$STH->execute($new_row);
} catch(PDOException $e) {
echo $e->getMessage();
}
$new_row = array('animal_type' => 'Wombat', 'animal_name' => 'Sally', 'favourite_colour' => 'Xanadu');
try {
$STH = $DBH->prepare("INSERT INTO animals (animal_type, animal_name, favourite_colour) VALUES (:animal_type, :animal_name, :favourite_colour)");
$STH->execute($new_row);
} catch(PDOException $e) {
echo $e->getMessage();
}
$new_row = array('animal_type' => 'Platypus', 'animal_name' => 'Sid', 'favourite_colour' => 'Feldgrau');
try {
$STH = $DBH->prepare("INSERT INTO animals (animal_type, animal_name, favourite_colour) VALUES (:animal_type, :animal_name, :favourite_colour)");
$STH->execute($new_row);
} catch(PDOException $e) {
echo $e->getMessage();
}
//
//SELECTING FROM DATABASE
//
//instead of the prepare method we call the query method
$result = $DBH->query("SELECT * FROM animals");
//iterate through each row in the table
//there are different ways of extracting the information
//FETCH_ASSOC , FETCH_BOTH , FETCH_LAZY , FETCH_OBJ , FETCH_BOUND , FETCH_CLASS , FETCH_INTO , FETCH_NUM
//fetch type can be permanently set with '$STH->setFetchMode(PDO::FETCH_CLASS)' beforehand and just
//looping through '$row = $result->fetch()'
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo $row['id'] . "<br />" . $row['animal_type'] . "<br />" . $row['animal_name'] . "<br />" . $row['favourite_colour'] . "<br /><br />";
}
//*************ALTERNATE*************//
//you can create a class and pass the results into it to create an object
class animal {
public $id;
public $animal_type;
public $animal_name;
public $favourite_colour;
function __construct() {
}
}
$STH = $DBH->query('SELECT * FROM animals');
$STH->setFetchMode(PDO::FETCH_CLASS, 'animal');
while($obj = $STH->fetch()) {
echo $obj->id;
echo $obj->animal_type;
echo $obj->animal_name;
echo $obj->favourite_colour;
echo "<br />";
}
//*********************************//
//
//UPDATING A ROW
//
//similar to the INSERT statement
try {
//prepare the UPDATE statement before executing it
$STH = $DBH->prepare("UPDATE animals SET animal_name = 'Pedro' WHERE animal_name = 'Wallace'");
//after preparing the UPDATE statement, execute it to save to db
//passing in your associative array to be parsed and inserted by the method
$STH->execute();
} catch(PDOException $e) {
//echos out the error message
echo $e->getMessage();
}
echo "<br /><br />";
//demo the changes
$STH = $DBH->query('SELECT * FROM animals');
$STH->setFetchMode(PDO::FETCH_CLASS, 'animal');
while($obj = $STH->fetch()) {
echo $obj->id;
echo $obj->animal_type;
echo $obj->animal_name;
echo $obj->favourite_colour;
echo "<br />";
}
//
//DELETING A ROW
//
//simplest part so far
//instead of preparing a statement to execute we just use the exec method
//this can be used when it returns no real data
try {
$DBH->exec("DELETE FROM animals WHERE animal_name = 'Pedro'");
} catch(PDOException $e) {
//echos out the error message
echo $e->getMessage();
}
echo "<br /><br />";
//demo the changes
$STH = $DBH->query('SELECT * FROM animals');
$STH->setFetchMode(PDO::FETCH_CLASS, 'animal');
while($obj = $STH->fetch()) {
echo $obj->id;
echo $obj->animal_type;
echo $obj->animal_name;
echo $obj->favourite_colour;
echo "<br />";
}
?>
Code At the Top of File Produces Output At the Bottom
5356
Kookaburra
Gumbo
Powder Blue
5357
Kangaroo
Jack
Gamboge
5358
Wallaby
Wallace
Razzmatazz
5359
Wombat
Sally
Xanadu
5360
Platypus
Sid
Feldgrau
5356KookaburraGumboPowder Blue
5357KangarooJackGamboge
5358WallabyWallaceRazzmatazz
5359WombatSallyXanadu
5360PlatypusSidFeldgrau
5356KookaburraGumboPowder Blue
5357KangarooJackGamboge
5358WallabyPedroRazzmatazz
5359WombatSallyXanadu
5360PlatypusSidFeldgrau
5356KookaburraGumboPowder Blue
5357KangarooJackGamboge
5359WombatSallyXanadu
5360PlatypusSidFeldgrau