Transcript Chapter 4 How to use PHP with a MySQL database Murach's PHP and MySQL, C4 © 2010, Mike Murach & Associates, Inc. Slide 1

Chapter 4
How to use PHP
with a MySQL database
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 1
Using PHP with a Database Management System
 Similar to using files with PHP
 To access files in PHP we used 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 2
Recall syntax for creating an object 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
The syntax for a DSN (Data Source Name)
for a MySQL database
mysql:host=host_address;dbname=database_name
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 3
How to connect to the my_guitar_shop1 MySQL
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 4
We need two basic methods to work with a
database
A method to read from a database
to process SQL SELECT
A method to write to a database
To process SQL INSERT, UPDATE, and DELETE
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 5
A method of the PDO class
for executing a SELECT statement
query($select_statement)
Recall the syntax for executing a method of a PHP
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 6
The query method of the PDO class returns a
‘table’
A query method with the SELECT statement
in a 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 7
A query method with the SELECT statement
as the argument
$products = $db->query('SELECT * FROM products');
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 8
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 9
A 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 10
How to execute an UPDATE statement
$product_id = 4;
$price = 599.99;
$query = "UPDATE products
SET listPrice = $price
WHERE productID = $product_id";
$update_count = $db->exec($query);
How to execute a DELETE statement
$product_id = 4;
$query = "DELETE FROM products
WHERE productID = $product_id";
$delete_count = $db->exec($query);
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 11
How to display the row counts
<p>Insert count: <?php echo $insert_count; ?></p>
<p>Update count: <?php echo $update_count; ?></p>
<p>Delete count: <?php echo $delete_count; ?></p>
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 12
The syntax for a try/catch statement
try {
// statements that might throw an exception
} catch (ExceptionClass $exception_name) {
// statements that handle the exception
}
How to handle a PDO exception
try {
$db = new PDO($dsn, $username, $password);
echo '<p>You are connected to the database!</p>';
} catch (PDOException $e) {
$error_message = $e->getMessage();
echo "<p>An error occurred while connecting to
the database: $error_message </p>";
}
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 13
How to handle any type of exception
try {
// statements that might throw an exception
} catch (Exception $e) {
$error_message = $e->getMessage();
echo "<p>Error message: $error_message </p>";
}
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 14
Two of the PHP functions for working with arrays
array()
count($array_name)
How to create an array that with no elements
$rates = array();
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 15
How to work with numeric indexes
How to set values
$rates[0] = 5.95;
$rates[1] = 10.95;
$rates[2] = 15.95;
// sets first element
// sets second element
// sets third element
How to get values
$rate = $rates[2];
// gets third element
How to loop through an array with a for loop
for ($i = 0; $i < count($rates); $i++) {
$message .= $rates[$i] . '|';
}
How to loop through an array with a foreach loop
foreach ($rates as $rate) {
$message .= $rate . '|';
}
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 16
How to work with string indexes
How to set values
$rates['Ground'] = 5.95;
$rates['2nd Day'] = 10.95;
$rates['Overnight'] = 15.95;
How to get values
$overnight = $rates['Overnight'];
How to loop through an array with a foreach loop
foreach ($rates as $index=>$rate) {
$message .= $index . '='. $rate . ' | ';
}
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 17
A method of the PDOStatement class
for getting an array for a row
fetch()
Code that gets a result set that contains one row
$query = 'SELECT productCode, productName, listPrice
FROM products
WHERE productID = $productID';
$products = $db->query($query);
// $products is a PDOStatement object
$product = $products->fetch();
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 18
Code that uses a string index to get each column
$product_code = $product['productCode'];
$product_name = $product['productName'];
$product_list_price = $product['listPrice'];
Code that uses a numeric index
to get each column
$product_code = $product[0];
$product_name = $product[1];
$product_list_price = $product[2];
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 19
A query method that returns a result set
of two or more rows
$query = 'SELECT productCode, productName, listPrice
FROM products
WHERE categoryID = 1;'
$products = $db->query($query);
// $products contains the result set
How to use a foreach statement to display the
result set in an HTML table
<?php foreach
<tr>
<td><?php
<td><?php
<td><?php
</tr>
<?php } ?>
Murach's PHP and MySQL, C4
($products as $product) { ?>
echo $product['productCode']; ?></td>
echo $product['productName']; ?></td>
echo $product['listPrice']; ?></td>
© 2010, Mike Murach & Associates, Inc.
Slide 20
Another syntax for the foreach statement
that works better within PHP tags
<?php foreach ($products as $product) : ?>
<tr>
<td><?php echo $product['productCode']; ?></td>
<td><?php echo $product['productName']; ?></td>
<td><?php echo $product['listPrice']; ?></td>
</tr>
<?php endforeach; ?>
Murach's PHP and MySQL, C4
© 2010, Mike Murach & Associates, Inc.
Slide 21