Chapter Seven – Database Processing Applications
NewCustomerForm.html HTML Code:
<!DOCTYPE html>
<html>
h2 {font-family: Ariel, sans-serif; text-align: left; color: blue}
p.footer {text-align: center}
table.output {font-family: Ariel, sans-serif}
</style>
</head>
<body>
<form action=“InsertNewCustomer.php” method=“POST”>
<!– Page Headers –>
<h1>
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 82 of 100
<td>
<input type=“text” name=“LastName” size=“25” />
</td>
</tr>
<tr>
<td>&nbsp;First Name:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“FirstName” size=“25” />
</td>
</tr>
<tr>
<td>&nbsp;Phone Number:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“Phone” size=“12” />
</td>
</tr>
<tr>
<td>&nbsp;Email Address:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“EmailAddress” size=“50” />
</td>
</tr>
</table>
<br />
<hr />
InsertNewCustomer.php HTML/PHP Code:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<title>InsertNewCustomer PHP Page</title>
Chapter Seven – Database Processing Applications
<?php
// Get connection
$DSN = “JRJ”;
// Execute SQL statement
$Result = odbc_exec($Conn, $SQLINSERT);
// Test existence of result
echo “<h1>
The James River Designs CUSTOMER Table
</h1>
<hr />”;
if ($Result){
}
else {
exit (“SQL Statement Error: ” . $SQL);
}
Chapter Seven – Database Processing Applications
// Create SQL statement to read CUSTOMER table data
$SQL = “SELECT * FROM CUSTOMER”;
?>
<!– Page Headers –>
<h2>
CUSTOMER
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′>
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
Chapter Seven – Database Processing Applications
ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
If you have not already implemented the Queen Anne Curiosity Shop database shown in
Chapter 3 in a DBMS product, create and populate the QACS database now in the
DBMS of your choice (or as assigned by your instructor).
A. Create a user named QACS-User with the password QACS-User+password. Assign
this user to database roles so that the user can read, insert, delete, and modify data.
This is described in the text for the HSD database in SQL Server 2016. The same steps can be
used to create the QACS-User in QACS. If you are doing this in Microsoft Access, run without
creating this user as described in The Access Workbench, Section 7.
B. If you haven’t completed Exercise 7.51, do it now.
See Exercise 7.51.
C. Add a new folder to the DBC Web site named QACS. Create a Web page for the Queen
Anne Curiosity Shop in this folder. Use the file name index.html. Link this page to the
DBC Web page.
Chapter Seven – Database Processing Applications
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-1″ />
<title>The Queen Anne Curiosity Shop Demonstration Pages Home Page</title>
</head>
<body>
<h1 style=”text-align: center; color: blue”>
Database Concepts (8th Edition)
Chapter Seven – Database Processing Applications
<p>Project Question F:&nbsp;&nbsp;&nbsp;
<a href=”ReadItem.php”>
</a>
</p>
<p>Project Question H:&nbsp;&nbsp;&nbsp;
<a href=”NewCustomerForm.html”>
Add a New Customer to the CUSTOMER Table
</a>
</p>
<hr />
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 QACS.
Chapter Seven – Database Processing Applications
E. Code a Web Page using PHP to display the data in SALE. Add a hyperlink on the
QACS 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>ReadSale PHP Page</title>
<style type=“text/css”>
</style>
</head>
<body>
<?php
// Get connection
$DSN = “QACS”;
Chapter Seven – Database Processing Applications
// Test connection
if (!$Conn)
}
?>
<!– Page Headers –>
<h1>
The Queen Anne Curiosity Shop SALE Table
</h1>
<hr />
<h2>
</tr>”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘SaleID’] . “</td>”;
echo “<td>” . $RecordSetRow[‘CustomerID’] . “</td>”;
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 90 of 100
<p class=“footer”>
<a href=“../QACS/index.html”>
Return to The Queen Anne Curiosity Shop Home Page
</a>
</p>
<hr />
</body>
</html>
F. Code a Web page using PHP to display the data in ITEM. Add a hyperlink on the QACS
Web page to access the page. Using your database, demonstrate that your page works.
Chapter Seven – Database Processing Applications
<!DOCTYPE html
<html>
</style>
</head>
<body>
<?php
// Get connection
$Conn = odbc_connect(‘QACS’, ‘QACS-User’,‘QACS-User+password’);
// Test connection
if (!$Conn)
{
exit (“SQL Statement Error: “ . $SQL);
}
?>
<!– Page Headers –>
<h1>
The Queen Anne Curiosity Shop ITEM Table
</h1>
<hr />
<h2>
ITEM
</h2>
Chapter Seven – Database Processing Applications
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
G. Create a view named Sale_Item_Item_View that displays SALE.SaleID,
SALE_ITEM.SaleItemID, SALE.SaleDate, ITEM.ItemDescription, and
SALE_ITEM.ItemPrice. Code a Web page using PHP to display the data in
Sale_Item_Item_View. Add a hyperlink to the QACS 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
// Test connection
if (!$Conn)
?>
<!– Page Headers –>
<h1>
The Queen Anne Curiosity Shop Sale_Item_Item_View
<br />
(Chapter 03A: SaleItemItemView)
</h1>
<hr />
<h2>
SaleItemItemView
</h2>
<?php
// Table headers
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
Chapter Seven – Database Processing Applications
<br />
H. Code two HTML/PHP pages to add a new CUSTOMER to the QACS 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
813 Tumbleweed Lane Durango CO 81201
Wu Susan 404-653-3465 Susan.Wu@somewhere.com
105 Locust Ave Atlanta GA 30322
Chapter Seven – Database Processing Applications
NewCustomerForm.html HTML Code:
<!DOCTYPE html>
<html>
<head>
Chapter Seven – Database Processing Applications
<body>
<form action=“InsertNewCustomer.php” method=“POST”>
<!– Page Headers –>
</td>
</tr>
<tr>
<td>&nbsp;First Name:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“FirstName” size=“25” />
</td>
</tr>
<tr>
<td>&nbsp;Phone Number:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“Phone” size=“12” />
</td>
</tr>
<tr>
</p>
<table>
<tr>
<td>&nbsp;Street Address:&nbsp;&nbsp;</td>
Chapter Seven – Database Processing Applications
<tr>
<td>&nbsp;ZIP Code:&nbsp;&nbsp;</td>
</p>
</form>
<br />
<hr />
<p class=“footer”>
InsertNewCustomer.php HTML/PHP Code:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<title>InsertNewCustomer PHP Page</title>
<style type=“text/css”>
</style>
</head>
<body>
<?php
// Get connection
$DSN = “QACS”;
$User = “QACS-User”;
$Password = “QACS-User+password”;
Chapter Seven – Database Processing Applications
$City = $_POST[“City”];
$State = $_POST[“State”];
$ZIP = $_POST[“ZIP”];
// Execute SQL statement
$Result = odbc_exec($Conn, $SQLINSERT);
// Test existence of result
echo “<h1>
The Queen Anne Curiosity Shop CUSTOMER Table
</h1>
<hr />”;
if ($Result){
echo “<h2>
New Customer Added:
</h2>
<table>
echo “<td>Address:</td>”;
echo “<td>” . $Address . “</td>”;
echo “</tr>”;
echo “<tr>”;
echo “<td>City:</td>”;
echo “<td>” . $City . “</td>”;
echo “</tr>”;
}
else {
exit (“SQL Statement Error: ” . $SQL);
}
Chapter Seven – Database Processing Applications
// Create SQL statement to read CUSTOMER table data
$SQL = “SELECT * FROM CUSTOMER”;
{
exit (“SQL Statement Error: ” . $SQL);
}
?>
<!– Page Headers –>
<h2>
CUSTOMER
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′>
</tr>”;
// Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘LastName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘FirstName’] . “</td>”;
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
</a>
</p>
<hr />
</body>
</html>