CSC 3084: Web Development and Programming

CSC 3084:Web Development and Programming Chapter 4: How to use PHP with a MySQL Database

PHP Objects and Classes • PHP is an object-oriented language • The syntax for creating an object from any class: new ClassName(arguments); • A PDO is a PHP data object, which provides a unified, DBMS-independent syntax for accessing databases • The syntax for creating a database object from the PDO class: new PDO($dsn, $username, $password); • The syntax for a DSN (Data Source Name) for a MySQL database: mysql:host=host_address;dbname=database_name

How to Connect to a MySQL Database $dsn = 'mysql:host=localhost;dbname=my_guitar_shop1'; $username = 'mgs_user'; $password = 'pa55word'; // creates PDO object $db = new PDO($dsn, $username, $password);

Executing Statements • The PHP code that we will look for executing queries is inherently insecure and is susceptible to a security attack known as an SQL injection attack • Basically this means that an attacker sends a specially-formatted string (into a SELECT statement, perhaps) that will cause a second query to execute • Typically these attack queries will reveal the contents of a user database, including names and (maybe) passwords • The easiest way to defend against injection attacks is to use prepared statements, which I hope to cover later in the semester

Executing Statements • A method of the PDO class for executing a SELECT statement: query($select_statement) • The syntax for executing a method of any object: $objectName->methodName(argumentList) • The syntax for executing the query method of the database object: $PDO_object->query($select_statement)

Executing Statements • A query method with the SELECT statement in a variable: $query = 'SELECT * FROM products WHERE categoryID = 1 ORDER BY productID'; $products = $db->query($query); • A query method with the SELECT statement as the argument: $products = $db->query('SELECT * FROM products'); • If the result set is empty, then FALSE is returned by the query method

Modifying a Database • A method of the PDO class for modifying the database: exec($sql_statement) • Returns number of rows affected by the query • 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);

UPDATE Statement Example $product_id = 4; $price = 599.99; $query = "UPDATE products SET listPrice = $price WHERE productID = $product_id"; $update_count = $db->exec($query);

DELETE Statement Example $product_id = 4; $query = "DELETE FROM products WHERE productID = $product_id"; $delete_count = $db->exec($query);

How to Display the Row Counts Insert count: <?php echo $insert_count; ?> Update count: <?php echo $update_count; ?> Delete count: <?php echo $delete_count; ?>

Exceptions • An exception is an object that contains information about an error that has occurred. A statement may throw an exception if an error occurs. • To handle exceptions, you use a try/catch statement. • Then, you code a catch block that catches the exception. This is known as exception handling.

Exceptions • 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 'You are connected to the database!'; } catch (PDOException $e) { $error_message = $e->getMessage(); echo "An error occurred while connecting to the database: $error_message "; }

How to Handle Any Type of Exception try { // statements that might throw an exception } catch (Exception $e) { $error_message = $e->getMessage(); echo "Error message: $error_message "; }

PHP Arrays • An array in PHP is actually an ordered map. • A map is a type that associates values to keys. • If the values are integers, then we have what looks like an array from languages like Java, C++, etc. • Two of the PHP functions for working with arrays: array() count($array_name) • How to create an array that with no elements: $rates = array();

How to Work with Numeric Indexes • How to set values: $rates[0] = 5.95; // sets first element $rates[1] = 10.95; // sets second element $rates[2] = 15.95; // 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 . '|'; }

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 foreachloop: foreach ($rates as $index=>$rate) { $message .= $index . '='. $rate . ' | '; }

Arrays as Result Sets • The fetch() method returns an array for the next row in a result set. • This array is indexed by both a string index for the column name and a numeric address for the column position. • If no array is available, the method returns a FALSE value. • Code that gets a result set that contains only one row: $query = 'SELECT productCode, productName, listPrice FROM products WHERE productID = $productID'; $products = $db->query($query); // $products is a PDOStatementobject $product = $products->fetch();

Arrays as Result Sets • 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];

Result Sets w/ Two or More Rows • 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: <?phpforeach ($products as $product) { ?> <tr> <td><?php echo $product['productCode']; ?></td> <td><?php echo $product['productName']; ?></td> <td><?php echo $product['listPrice']; ?></td> </tr> <?php } ?>

Result Sets w/ Two or More Rows • Another syntax for the foreachstatement that works better within PHP tags: <?phpforeach ($products as $product) : ?> <tr> <td><?php echo $product['productCode']; ?></td> <td><?php echo $product['productName']; ?></td> <td><?php echo $product['listPrice']; ?></td> </tr> <?phpendforeach; ?> • This syntax make it easier to use the foreach statement with other control statements • Can use similar syntax with if-statements, for-loops, etc.

Example: Product Viewer • See PHP Textbook Files\book_apps\ch04_product_viewer\ • Run PHP Textbook Files\book_apps\_create_db\my_guitar_shop1.sql in phpMyAdmin to setup the database • Click on a category to clear the error. I’ll show you in the next example how to fix it. • Inspect database.php to seee the database connection code • See index.php to see the SQL queries and other PHP code • Change the query on line 23 to ORDER BY productName instead of productID

Example: Product Manager • See PHP Textbook Files\book_apps\ch04_product_manager\ • To an the error in index.php, change the code starting on line 5 to this: if(!isset($category_id)) { if (!isset($_GET['category_id'])) $category_id = 1; else $category_id= $_GET['category_id']; if (!isset($category_id)) { $category_id = 1; } }

Example: Product Manager • There is another bug on line 61 of index.php • Change it to this:<a href="index.php?category_id=<?php echo $category['categoryID']; ?>">

Example: Product Manager • Run PHP Textbook Files\book_apps\_create_db\my_guitar_shop2.sql in phpMyAdmin to setup the database • Review the following files: • index.php • add_product_form.php • add_product.php • delete_product.php • Try out the application!

CSC 3084: Web Development and Programming