Transcript Ch. 9 Slides

CHAPTER 9
PHP AND MYSQL
A POSSIBLE SITE
CONFIGURATION
Application Folder
index.php
header.php
includes (folder)
footer.php
model (folder)
style.css
mysqli_connect.php
CONNECTING TO A
DATABASE
PHP offers three different ways to connect to and interact
with a MySQL database:
1. Original MySQL extension – no longer developed, not
recommended
2. MySQL Improved: MySQLi – designed specifically for
MySQL so is not easily portable to other databases
3. PHP Data Objects: PDO – software neutral, preferable of
database flexibility is important.
We will focus on MySQLi
COMMUNICATING TO
MYSQL
All PHP methods follow the same sequence:
1. Connect to the MySQL database using the hostname,
username, password, and database name.
2. Prepare an SQL query.
3. Execute the query and save the result.
4. Extract the data from the result (usually with a loop.)
5. Close the connection to the database.
EXAMPLE
<?php # Script 9.2 - mysqli_connect.php
// This file contains the database access information.
// It establishes a connection to MySQL and selects the database
// Set the database access information as constants:
DEFINE ('DB_USER', 'your user name');
DEFINE ('DB_PASSWORD', 'your sql password');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'your database');
// Make the connection:
$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
echo 'Connection successful!';
CALLING THE
CONNECTION FILE
Use the require() function, which will halt the script
if it is unsuccessful, to connect to the database:
<?php
require ('model/mysqli_connect.php');
?>
IN-CLASS EXERCISE
Write a simple script that will:
1. Try to connect to your database on
webdev,
2. Print a successful connection message,
or error message
3. Then close the connection
EXECUTING SIMPLE
QUERIES
$r = mysqli_query($dbc, $query);
• For simple queries like, INSERT, UPDATE, DELETE, etc.
(which don't return records), the function will return either
TRUE or FALSE.
• For complex queries like SELECT, which return results of
the query as a table, $r will be a pointer to the result or
FALSE if it did not work.
EXECUTING SIMPLE
QUERIES
Write the query and assign it to a variable:
$q = "DELETE from SF_products where id = 3";
Execute the query:
$r = mysqli_query($dbc, $q);
EXECUTING SIMPLE
QUERIES WITH VARIABLES
$category_id = 1;
$code = 'strat';
$name = 'Fender Stratocaster';
$price = 699.99;
Write the query and assign it to a variable:
$query = "INSERT INTO products
(categoryID, productCode, productName, listPrice)
VALUES
($category_id, '$code', '$name', $price)";
Execute the query:
$r = mysqli_query($dbc, $query);
CLOSE THE EXISTING
CONNECTION
mysqli_close($dbc);
• optional because PHP will close the connection at the end
of the script
• makes for good programming form anyway
SENDING FORM DATA
TO THE DATABASE
Use the trim() function to remove white
space from beginning and end of strings.
Example: register.php (download from
calendar)
Check the database to make sure the data
was entered correctly. Don't rely on the
'success' of the script.
EXECUTING 'SELECT'
QUERIES
A MySQL SELECT query typically generates one or more
rows of information that has to be handled by the script.
The same command is used to execute the query:
$r = mysqli_query($dbc, $q);
$r is the query result variable. In the case of a SELECT
query, $r is a pointer to the results table.
The mysqli_fetch_array() function returns one row of the
result at a time, in array format:
while ($row = mysqli_fetch_array($r) )
{ //Do something with $row
}
EXECUTING 'SELECT'
QUERIES
Notes:
The connection file has been moved out of the public_html directory:
The organization for this example is:
EXECUTING 'SELECT'
QUERIES
Notes:
The query must be executed first and then the results are processed.
Remember that SQL variables are case-sensitive – you must reference
them exactly as they are in the database.
Instead of mysqli_fetch_array($r, MYSQLI_ASSOC) , you could use
mysqli_fetch_assoc($r)
mysqli_free_result ($r); is an optional (but good practice) command to
free the memory taken by $r.
EXAMPLE:
header.html
EXAMPLE: RETRIEVE DATA
SQL SECURITY
1. Protect the MySQL access information
•
Keep it outside of the Web directory so that it is never
viewable though a Web browser.
2. Don't reveal too much about the database
•
Don't allow users to the PHP error messages or SQL error
messages.
• They are useful to display during development for
debugging, but remove them when the site goes live.
3. Be especially careful with user-submitted data
•
Never trust it!
CHECKING USERSUBMITTED DATA
1. Validate that a value has been submitted and that is the
proper type (number, string, etc.)
2. Use regular expressions to make sure that submitted
data matches what you expect. (Ch. 14)
3. Typecast variables to guarantee that they are numeric.
(Ch. 13)
4. Use prepared statements (Ch. 13) which is a preferable
alternative to mysqli_real_escape_string() discussed in
this chapter.
COUNTING
RETURNED RECORDS
$num = mysqli_num_rows ($r);
• Takes the results variable as the argument
• Used to:
• Determine how many rows are in a given result. For
example, to check if a userid already exists in a database.
• Paginate query results (Ch. 10)
$num = mysqli_affected_rows ($dbc);
• Takes the database connection as the argument
• Can be used to determine the success of UPDATE,
DELETE, or INSERT queries.