Chapter Seven – Database Processing Applications
E. Code a Web page using PHP to display the data in OWNED_PROPERTY. Add a
hyperlink on the GG Web page to access the page. Using your database, demonstrate
that your page works.
<!DOCTYPE html>
<html>
<head>
</style>
</head>
<body>
<?php
// Get connection
$Conn = odbc_connect(‘GG’, ‘GG-User’,‘GG-User+password’);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: “ . $Conn);
}
Chapter Seven – Database Processing Applications
// Test existence of recordset
if (!$RecordSet)
{
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′>
<tr>
<th>PropertyID</th>
<th>PropertyName</th>
<th>PropertyType</th>
</tr>”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
Chapter Seven – Database Processing Applications
F. Code a Web page using PHP to display the data in PROPERTY_SERVICE. Add a
hyperlink on the GG Web page to access the page. Using your database, demonstrate
that your page works.
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<title>ReadPropertyService PHP Page</title>
<style type=“text/css”>
</style>
</head>
<body>
<?php
// Get connection
$Conn = odbc_connect(‘GG’, ‘GG-User’,‘GG-User+password’);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: “ . $Conn);
}
// Create SQL statement
$SQL = “SELECT * FROM PROPERTY_SERVICE”;
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 64 of 100
// Execute SQL statement
$RecordSet = odbc_exec($Conn,$SQL);
// Test existence of recordset
if (!$RecordSet)
{
exit (“SQL Statement Error: “ . $SQL);
}
?>
<!– Page Headers –>
<h1>
The Garden Glory PROPERTY_SERVICE Table
</h1>
<hr />
<h2>
PROPERTY_SERVICE
</h2>
<?php
// Table headers
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
Chapter Seven – Database Processing Applications
G. Create a view named Property_Service_View that displays PROPERTY.PropertyID,
PropertyName, SERVICE.EmployeeID, ServiceDate, and HoursWorked. Code a Web
page using PHP to display the data in Property_Service_View. Add a hyperlink to the
GG Web page to access the page. Using your database, demonstrate that your page
works.
<!DOCTYPE html>
<html>
<head>
Chapter Seven – Database Processing Applications
<body>
<?php
// Get connection
$Conn = odbc_connect(‘GG’, ‘GG-User’,‘GG-User+password’);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: “ . $Conn);
}
?>
<!– Page Headers –>
<h1>
The Garden Glory Property_Service_View
<br />
(Chapter 03: PropertyServiceView)
</h1>
<hr />
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘PropertyID’] . “</td>”;
echo “<td>” . $RecordSetRow[‘PropertyName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Street’] . “</td>”;
Chapter Seven – Database Processing Applications
// Close connection
odbc_close($Conn);
H. Code two HTML/PHP pages to add a new OWNER to the GG database. Create data for
two new OWNERs and add them to the database to demonstrate that your pages work.
Chapter Seven – Database Processing Applications
HTML CODE FOR FORM TO COLLECT NEW OWNER DATA:
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Strict//EN”
“http://www.w3.org/TR/html4/strict.dtd”>
<html>
<head>
</style>
</head>
<body>
<form action=“InsertNewOwner.php” method=“POST”>
<!– Page Headers –>
<h1>
Garden Glory New Owner Form
</h1>
<hr />
<br />
</td>
</tr>
</table>
<p>
<b>Select Owner Type:</b>
</p>
</select>
<br />
<hr />
</p>
</form>
<br />
<hr />
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 69 of 100
<p class=“footer”>
<a href=“../GG/index.html”>
Return to Garden Glory Home Page
</a>
</p>
<hr />
</body>
</html>
HTML/PHP CODE TO INSERT NEW OWNER DATA:
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Frameset//EN”>
<html>
</style>
</head>
<body>
<?php
// Get connection
$Conn = odbc_connect($DSN, $User, $Password);
// Test connection
if (!$Conn)
{
// Create SQL statement to INSERT new data
$SQLINSERT = “INSERT INTO OWNER “;
$SQLINSERT .= “VALUES(‘$OwnerName‘, “;
$SQLINSERT .= “‘$OwnerEmailAddress‘, ‘$OwnerType‘)”;
Chapter Seven – Database Processing Applications
<table>
<tr>”;
echo “<td>OwnerName:</td>”;
echo “<td>” . $OwnerName . “</td>”;
echo “</tr>”;
echo “<tr>”;
else {
exit (“SQL Statement Error: ” . $SQL);
}
// Create SQL statement to read OWNER table data
$SQL = “SELECT * FROM OWNER”;
// Execute SQL statement
$RecordSet = odbc_exec($Conn,$SQL);
// Test existence of recordset
if (!$RecordSet)
{
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 71 of 100
<p class=“footer”>
<a href=“../GG/index.html”>
Return to Garden Glory Home Page
</a>
</p>
<hr />
</body>
</html>
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
[NOTE: The James River Jewelry Project Questions are available online for Appendix D,
which can be downloaded from the textbook’s Web site:
http://www.pearsonhighered.com/kroenke. The solutions for these questions will be
included in the Instructor’s Manual for each chapter]
If you have not already implemented the James River Jewelry database shown in the
James River Jewelry Project Questions for Chapter 3 that is online in Appendix D in a
DBMS product, create and populate the JAMES_RIVER_JEWELRY database now in
the DBMS of your choice (or as assigned by your instructor). Note that this assignment
uses SQL views, which are discussed in online Appendix E—SQL Views.
A. Create a user named JRJ-User with the password JRJ-User+password. Assign this
user to database roles so that the user can read, insert, delete, and modify data.
Chapter Seven – Database Processing Applications
C. Add a new folder to the DBC Web site named JRJ. Create a Web page for James River
Jewelry in this folder. Use the file name index.html. Link this page to the DBC Web
page.
<!DOCTYPE html>
<html>
Chapter Seven – Database Processing Applications
D. Create an appropriate ODBC data source for your database.
The process is described in the text—see pages 431-436, and be sure to read and understand
the footnote on page 432 (Also see the By The Way in Appendix I on page I-9). The steps are
also illustrated it the solution to the MDC case questions above. The ODBC system data source
name is JRJ.
E. Code a Web page using PHP to display the data in PURCHASE. Add a hyperlink on the
JRJ Web page to access the page. Using your database, demonstrate that your page
works.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
Chapter Seven – Database Processing Applications
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
F. Code a Web page using PHP to display the data in ITEM. Add a hyperlink on the JRJ
Web page to access the page. Using your database, demonstrate that your page works.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<title>ReadItem PHP Page</title>
// Test connection
if (!$Conn)
{
exit (“SQL Statement Error: “ . $SQL);
}
?>
<!– Page Headers –>
<h1>
</h2>
Chapter Seven – Database Processing Applications
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
}
echo “</table>”;
G. Create a view named Purchase_Item_Item_View that displays
PURCHASE.InvoiceNumber, PURCHASE_ITEM.ItemNumber,
PURCHASE.InvoiceDate, ITEM.Description, and PURCHASE_ITEM.RetailPrice. Code
a Web page using PHP to display the data in Purchase_Item_Item_View. Add a
hyperlink to the JRJ Web page to access the page. Using your database, demonstrate
that your page works.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<title>ReadPurchaseItemItemView PHP Page</title>
</style>
</head>
<body>
<?php
Chapter Seven – Database Processing Applications
// Test existence of recordset
if (!$RecordSet)
{
exit (“SQL Statement Error: “ . $SQL);
}
?>
<!– Page Headers –>
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′>
<tr>
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘InvoiceNumber’] . “</td>”;
echo “<td>” . $RecordSetRow[‘ItemNumber’] . “</td>”;
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
Chapter Seven – Database Processing Applications
H. Code two HTML/PHP pages to add a new CUSTOMER to the JRJ database. Create
data for two new CUSTOMERs and add them to the database to demonstrate that your
pages work.
New Customer Data:
Smith David 970-654-9876 David.Smith@somewhere.com
Wu Susan 404-653-3465 Susan.Wu@somewhere.com