Transcript PHP and MySQL

Introduction to
PHP and MySQL
Kirkwood Center for
Continuing Education
By Fred McClurg, [email protected]
Copyright © 2010 All Rights Reserved.
Chapter Ten
Getting PHP to talk to MySQL
http://webcert.kirkwood.edu/~fmccl
urg/courses/php/slides/chapter10.ppt
Performing the Perfect Procedure
Regardless of whether you use the MySQL commandline, the built-in functions, or the PEAR functions,
you will use the same process to communicate to the
database.
Common Process Steps:
0. Initialize variables
1. Connect to the database.
2. Choose which database to use.
3. Build the SQL statement.
4. Execute the SQL statement.
5. Retrieve and display the results.
6. Close database connection (optional)
Step 0: Initialize Variables
File “login.php” page 1:
<?php
// 0. init DB variables
$host = "localhost";
$dbName = "cookbook";
$username = "root";
$password = "";
...
?>
Step 1: Connection to Database
File “login.php” page 2:
<?php
...
// 1. Connect to the database
$conn = mysql_connect( $host,
$username,
$password );
if ( ! $conn ) // not connected
{
die( "Could not connect $host to
database.<br />" .
mysql_error() );
}
...
?>
Making a PHP Connection to MySQL
Description: The Perl motto of “There Is More Than
One Way To Do It” is also true of Perl’s cousin PHP.
The acroynm “TIMTOWTDI” (pronounced “Tim
Toady”), is especially true concerning PHP and
MySQL.
Built-in PHP connect function:
mysql_connect( $host, $username,
$password );
PEAR DB connect function:
$conn =
DB::connect("mysql:://$username
:$password@$host/$database");
Step 2: Select Database
File “login.php” page 3:
<?php
...
// 2. Choose which database to use
$useDb = mysql_select_db($dbName);
if ( ! $useDb ) // not selected
{
die( "Could not select database
$dbName <br />" .
mysql_error() );
}
?>
Step 3: Define SQL Statement
File “fetch_row.php” page 1:
<?php
// 1 & 2. Connect and use db
require_once( "login.php" );
// 3. Build the SQL statement
$sql = "SELECT *
FROM recipe";
...
?>
Step 4: Execute SQL Statement
File “fetch_row.php” page 2:
<?php
...
// 4. Execute the SQL statement
$result = mysql_query( $sql );
if ( ! $result ) // SQL failed
{
die( "Could not execute SQL
'$sql' <br />" .
mysql_error() );
}
...
?>
Step 5: Retrieve Database Results
Description: The function “mysql_fetch_row()” retrieves the results from a
database query via a numeric array. See file “fetch_row.php” page 3
<?php
...
// 5. Display the results
while ( $row = mysql_fetch_row( $result ) )
{
printf( "ID: %s &nbsp;
Name: %s <br />",
$row[0], $row[1] );
}
// 6. Close DB connection (optional)
mysql_close( $conn );
?>
Step 5: Retrieve DB Results (alternate)
Description: The function “mysql_fetch_assoc()” retrieves the results from a
database query via a hash. See file “fetch_assoc.php”
<?php
...
// 5. Display the results
while ( $hash =
mysql_fetch_assoc( $result ) )
{
printf( "ID: %s &nbsp;
Name: %s <br />",
$hash['id'],
$hash['name'] );
}
// 6. Close DB connection (optional)
mysql_close( $conn );
?>
Student Exercise 9.1 Summary
Description: Write a PHP program that
displays the following information from
the database in the browser:
Title
Chocolate Chip
Cookies
...
Mix
Add chocolate
chips ...
...
Type
Dessert
...
Student Exercise 9.1 Details
Requirements:
1. Using MySQL command-line or phpmyadmin, create a
database named “cookbook”.
2. Inside the cookbook DB, import the file
“cookbookDB.sql” to create the tables and data.
http://webcert.kirkwood.edu/~fmcclurg/courses/php/e
xamples/chapter10/cookbookDB.sql
3. Inside the file “list.php”, perform a database query
joining the tables “recipe” and “category”. Display the
columns “recipe.name”, “recipe.content”,
“category.name” and create aliases for them. Sort the
results by “recipe.name”.
4. Retrieve the database rows using
“mysql_fetch_assoc()”.
5. Display the results in an HTML table.
Student Exercise 9.1 Solution (option 1, pg 1)
See file “list.1.php”
<?php
...
// 3. Build the SQL statement
$sql = "
SELECT rec.name AS title,
rec.content AS mix,
cat.name AS type
FROM recipe AS rec,
category AS cat
WHERE rec.category_id = cat.id
ORDER BY title";
...
?>
Student Exercise 9.1 Solution (option 1, pg 2)
Description: ... and the body of the while loop. (see file
“list.1.php”)
<?php
...
printf("<table border=1>");
printf("
<tr>");
printf("
<th>Title</th>");
printf("
<th>Mix</th>");
printf("
<th>Type</th>");
printf("
</tr>");
...
?>
Student Exercise 9.1 Solution (option 1, pg 3)
Description: ... and the body of the while loop. (see file “list.1.php”)
<?php
...
// 5. Display the results
while ( $row = mysql_fetch_assoc( $result ) )
{
/* multiple print statements */
printf("<tr valign='top'>\n" );
printf("
<td> %s </td>\n", $row['title'] );
printf("
<td> %s </td>\n", $row['mix'] );
printf("
<td> %s </td>\n", $row['type'] );
printf("</tr>\n");
}
printf( "</table>\n" );
...
?>
Student Exercise 9.1 Solution (option 2)
Description: ... and the body of the while loop. (see file “list.2.php”)
<?php
...
// 5. Display the results
while ( $row = mysql_fetch_assoc( $result ) )
{
/* single print statement */
printf( "
<tr valign='top'>
<td> %s </td>
<td> %s </td>
<td> %s </td>
</tr>\n", $row['title'],
$row['mix'],
$row['type'] );
}
printf( "</table>\n" );
...
?>
Student Exercise 9.1 Solution (option 3)
Description: ... and the body of the while loop. (see file “list.3.php”)
<?php
...
// 5. Display the results
while ( $row = mysql_fetch_assoc( $result ) )
{
/* escape PHP and begin HTML */
?>
<tr valign='top'>
<td> <?= $row['title'] ?> </td>
<td> <?= $row['mix'] ?> </td>
<td> <?= $row['type'] ?> </td>
</tr>
<?php
/* escape HTML and begin PHP */
}
printf( "</table>\n" );
...
?>
Student Exercise 9.1 Solution (option 4, pg 1)
Description: ... and the body of the while loop. (see file “list.4.php”)
<?php
...
// HTML table creation functions
require_once( "table.inc.php" );
...
// create table head via function
$header = array( "Title", "Mix", "Type" );
PrintTableHeader( $header );
// 5. Display the results
while ( $row = mysql_fetch_assoc( $result ) )
{
// create table rows via function
PrintTableRow( $row );
}
printf( "</table>\n" );
...
?>
Student Exercise 9.1 Solution (option 4, pg 2)
Description: Included HTML table functions. (see file “table.inc.php”)
<?php
function PrintTableHeader( $columns )
{
$bgColor = "#b4cdcd"; // LightCyan3
$fgColor = "#ffffff"; // White
printf( "<table border='1' cellpadding='5'
cellspacing='0'>" );
printf( "
<tr bgcolor='%s' style='color: %s'>",
$bgColor, $fgColor );
foreach ( $columns as $name )
printf( "<th> %s </th>", $name );
}
?>
printf( "</tr>" );
// function PrintTableHeader();
Student Exercise 9.1 Solution (option 4, pg 3)
Description: Included HTML table functions. (see file “table.inc.php”)
<?php
function PrintTableRow( $row )
{
static $rowCount = 0;
$fgColor = "#000000"; // Black
if ( $rowCount % 2 ) // not even
$bgColor = "#d1eeee"; // LtCyan2
else
$bgColor = "#e0ffff"; // LtCyan
printf( "<tr valign='top' bgcolor='%s' style='color: %s'>\n",
$bgColor, $fgColor );
foreach ( $row as $key => $value )
printf( "<td> %s </td>\n", $row[$key] );
printf( "</tr>\n" );
}
?>
$rowCount++;
// function PrintTableRow();
to be continued ...
http://webcert.kirkwood.edu/~fmccl
urg/courses/php/slides/chapter11.ppt