Transcript Chapter 19 Professional PHP for working with MySQL Murach's PHP and MySQL, C19 © 2010, Mike Murach & Associates, Inc. Slide 1

Chapter 19
Professional PHP
for working with
MySQL
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 1
But First, A Quick Review of
Chapter 4
How to use PHP
with a MySQL database
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 2
Using PHP with a Database Management System
 Similar to using files with PHP
 To access files in PHP we use file functions
 To access databases in PHP we use classes:
The PDO class
The PDOStatement class
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 3
Recall syntax for creating an object of a PHP class
$anObject = new ClassName(arguments);
The syntax for creating a PDO object for a database
from the PDO class
$aPDObject = new PDO($dsn, $username, $password);
A PDO Object tells us about a Database with a specific
DSN (Data Source Name)
Syntax for a DSN for a MySQL database
mysql:host=host_address;dbname=database_name
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 4
Connect to the my_guitar_shop1 database
$dsn = 'mysql:host=localhost;dbname=my_guitar_shop1';
$username = 'mgs_user';
$password = 'pa55word';
// creates PDO object
$db = new PDO($dsn, $username, $password);
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 5
We need two basic methods to work with a
database
 A method to read from a database, i.e., to process SQL
SELECT statements
 A method to write to a database, i.e., to process SQL
INSERT, UPDATE, and DELETE
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 6
A method of the PDO class for executing a
SELECT statement
query($select_statement)
Recall the PHP syntax for executing a method of
an object
$objectName->methodName(argumentList)
The syntax for executing the query method
of the database object
$PDO_object->query($select_statement)
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 7
The PDO query method returns a ‘table’
A query method with the SELECT statement
in a string variable
$query = 'SELECT * FROM products
WHERE categoryID = 1
ORDER BY productID';
$products = $db->query($query);
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 8
Using the query method with a SELECT
statement as the argument
$products = $db->query('SELECT * FROM products');
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 9
A method of the PDO class for modifying the database
exec($sql_statement)
How to execute an INSERT statement
$category_id = 1;
$code = 'strat';
$name = 'Fender Stratocaster';
$price = 699.99;
$query = "INSERT INTO products
(categoryID,productCode,productName,listPrice)
VALUES
($category_id, '$code', '$name', $price)";
$insert_count = $db->exec($query);
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 10
The exec method of the PDO class returns the
number of rows processed
$insert_count = $db->exec($query);
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 11
In Chapter 19 a new way of using PDO is
described: Using Prepared Statements
An SQL statement is ‘prepared’ for execution
When a prepared SELECT statement is executed, it
returns a PDOStatement object
You can then use PDOStatement methods to process
that object
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 12
Some methods of the PDO class
prepare($sql_statement)
lastInsertId()
Some methods of the PDOStatement class
bindValue($param, $value)
execute()
fetchAll()
fetch()
rowCount()
closeCursor()
The textbook describes these methods in more
detail
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 13
The PDO prepare method
Prepares the specified SQL statement for execution and
returns a PDOStatement object
The PDOStatement execute method
Executes the prepared statement and returns TRUE or
FALSE depending on whether it was successful or not
The PDOStatement fetchAll method
Returns an array for all of the rows in the result set
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 14
How to use the fetchAll method
to return a result set
$query = 'SELECT * FROM products';
$statement = $db->prepare($query);
$statement->execute();
$products = $statement->fetchAll();
$statement->closeCursor();
foreach ($products as $product) {
echo $product['productName'] . '<br />';
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 15
How to use the fetch method
to loop through a result set
$query = 'SELECT * FROM products';
$statement = $db->prepare($query);
$statement->execute();
$product = $statement->fetch();//get first row
while ($product != null) {
echo $product['productName'] . '<br />';
$product = $statement->fetch();//get next row
}
$statement->closeCursor();
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 16
How to use named parameters
$category_id = 1;
$price = 400;
$query = 'SELECT * FROM products
WHERE categoryID = :category_id
AND listPrice > :price';
$statement = $db->prepare($query);
$statement->bindValue(':category_id',
$category_id);
$statement->bindValue(':price', $price);
$statement->execute();
$products = $statement->fetchAll();
$statement->closeCursor();
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 17
How to use question mark parameters
$query = 'SELECT * FROM products
WHERE categoryID = ?
AND listPrice > ?';
$statement = $db->prepare($query);
$statement->bindValue(1, $category_id);
$statement->bindValue(2, $price);
$statement->execute();
$products = $statement->fetchAll();
$statement->closeCursor();
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 18
How to modify data
$category_id = 2;
$code = 'hofner';
$name = 'Hofner Icon';
$price = '499.99';
// Prepare and execute the statement
$query = 'INSERT INTO products
(categoryID, productCode, productName,listPrice)
VALUES
(:category_id, :code, :name, :price)';
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->bindValue(':code', $code);
$statement->bindValue(':name', $name);
$statement->bindValue(':price', $price);
$success = $statement->execute();
$row_count = $statement->rowCount();
$statement->closeCursor();
// Get the last product ID that was generated
$product_id = $db->lastInsertId();
// Display a message to the user
if ($success) {
echo "<p>$row_count row(s)inserted with ID: $product_id</p>";
} else {
echo "<p>No rows were inserted.</p>";
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 19
The three error modes for PDO
ERRMODE_SILENT
ERRMODE_WARNING
ERRMODE_EXCEPTION
Setting the error mode with the PDO constructor
$dsn = 'mysql:host=localhost;dbname=my_guitar_shop1';
$username = 'mgs_user';
$password = 'pa55word';
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
try {
$db = new PDO($dsn, $username, $password,
$options);
} catch (PDOException $e) {
$error_message = $e->getMessage();
echo "<p>Error connecting to database:
$error_message </p>";
exit();
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 20
Setting the mode with the setAttribute method
$db->setAttribute(
PDO::ATTR_ERRMODE,DO::ERRMODE_EXCEPTION);
How to catch PDOException objects
try {
$query = 'SELECT * FROM product';
$statement = $db->prepare($query);
$statement->execute();
$products = $statement->fetchAll();
$statement->closeCursor();
} catch (PDOException $e) {
$error_message = $e->getMessage();
echo "<p>Database error: $error_message </p>";
exit();
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 21
The model/database.php file
<?php
$dsn = 'mysql:host=localhost;dbname=my_guitar_shop2';
$username = 'mgs_user';
$password = 'pa55word';
$options =
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
try {
$db = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
$error_message = $e->getMessage();
include 'errors/db_error_connect.php';
exit;
}
function display_db_error($error_message) {
global $app_path;
include 'errors/db_error.php';
exit;
}
?>
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 22
The model/category_db.php file
<?php
function get_categories() {
global $db;
$query = 'SELECT * FROM categories
ORDER BY categoryID';
try {
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_error($e->getMessage());
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 23
The model/category_db.php file (continued)
function get_category($category_id) {
global $db;
$query = 'SELECT * FROM categories
WHERE categoryID = :category_id';
try {
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_error($e->getMessage());
}
}
?>
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 24
The model/product_db.php file
<?php
function get_products_by_category($category_id) {
global $db;
$query = 'SELECT * FROM products
WHERE categoryID = :category_id
ORDER BY productID';
try {
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
$error_message = $e->getMessage();
display_db_error($error_message);
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 25
The model/product_db.php file (continued)
function get_product($product_id) {
global $db;
$query = 'SELECT *
FROM products
WHERE productID = :product_id';
try {
$statement = $db->prepare($query);
$statement->bindValue(':product_id', $product_id);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
$error_message = $e->getMessage();
display_db_error($error_message);
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 26
The model/product_db.php file (continued)
function add_product($category_id, $code, $name,
$description, $price, $discount_percent) {
global $db;
$query = 'INSERT INTO products
(categoryID, productCode, productName, description,
listPrice, discountPercent, dateAdded)
VALUES
(:category_id, :code, :name, :description, :price,
:discount_percent, NOW())';
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 27
The model/product_db.php file (continued)
try {
$statement = $db->prepare($query);
$statement->bindValue(':category_id', $category_id);
$statement->bindValue(':code', $code);
$statement->bindValue(':name', $name);
$statement->bindValue(':description', $description);
$statement->bindValue(':price', $price);
$statement->bindValue(':discount_percent',
$discount_percent);
$statement->execute();
$statement->closeCursor();
// Get the last product ID that was generated
$product_id = $db->lastInsertId();
return $product_id;
} catch (PDOException $e) {
$error_message = $e->getMessage();
display_db_error($error_message);
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 28
The model/product_db.php file (continued)
function update_product($product_id, $code, $name,
$description,$price, $discount_percent, $category_id) {
global $db;
$query = 'UPDATE Products
SET productName = :name,
productCode = :code,
description = :description,
listPrice = :price,
discountPercent = :discount_percent,
categoryID = :category_id
WHERE productID = :product_id';
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 29
The model/product_db.php file (continued)
try {
$statement = $db->prepare($query);
$statement->bindValue(':name', $name);
$statement->bindValue(':code', $code);
$statement->bindValue(':description', $description);
$statement->bindValue(':price', $price);
$statement->bindValue(':discount_percent',
$discount_percent);
$statement->bindValue(':category_id', $category_id);
$statement->bindValue(':product_id', $product_id);
$row_count = $statement->execute();
$statement->closeCursor();
return $row_count;
} catch (PDOException $e) {
$error_message = $e->getMessage();
display_db_error($error_message);
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 30
The model/product_db.php file (continued)
function delete_product($product_id) {
global $db;
$query = 'DELETE FROM products
WHERE productID = :product_id';
try {
$statement = $db->prepare($query);
$statement->bindValue(':product_id', $product_id);
$row_count = $statement->execute();
$statement->closeCursor();
return $row_count;
} catch (PDOException $e) {
$error_message = $e->getMessage();
display_db_error($error_message);
}
}
?>
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 31
The model/product_db.php file (continued)
if ($success) {
$count = $db->affected_rows;
$statement->close();
return $count;
} else {
display_db_error($db->error);
}
}
Murach's PHP and MySQL, C19
© 2010, Mike Murach & Associates, Inc.
Slide 32