<?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
4741
Kookaburra
Gumbo
Powder Blue
4742
Kangaroo
Jack
Gamboge
4743
Wallaby
Wallace
Razzmatazz
4744
Wombat
Sally
Xanadu
4745
Platypus
Sid
Feldgrau
4741KookaburraGumboPowder Blue
4742KangarooJackGamboge
4743WallabyWallaceRazzmatazz
4744WombatSallyXanadu
4745PlatypusSidFeldgrau
4741KookaburraGumboPowder Blue
4742KangarooJackGamboge
4743WallabyPedroRazzmatazz
4744WombatSallyXanadu
4745PlatypusSidFeldgrau
4741KookaburraGumboPowder Blue
4742KangarooJackGamboge
4744WombatSallyXanadu
4745PlatypusSidFeldgrau