Chapter Seven – Database Processing Applications
Web Page code:
<!DOCTYPE html>
<html>
<head>
<title>Marcia’s Dry Cleaning Demonstration Pages Home Page</title>
<meta charset=”UTF–8″>
<meta name=”viewport” content=”width=device–width, initial–scale=1.0″>
</head>
</a>
</p>
<p>Question D:&nbsp;&nbsp;&nbsp;
Question D asks for an ODBC data source, not a Web page.
</p>
<p>Question E:&nbsp;&nbsp;&nbsp;
Question E asks for a new column in the <b>ORDER</b> table named <b>Status</b>,
not a Web page.
data.</a>
</p>
<hr />
<p style=”text–align: center“>
<a href=“http://localhost/DBC/index.html”>
Return to the Database Concepts Home Page
</a>
</p>
<hr />
</body>
</html>
Chapter Seven – Database Processing Applications
D. Create an appropriate ODBC data source for your database.
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 43 of 100
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 44 of 100
Chapter Seven – Database Processing Applications
© 2018 Pearson Education, Inc. Page 45 of 100
Chapter Seven – Database Processing Applications
Note the new system DSN name MDC:
E. Add a new column, Status to INVOICE. Assume that Status can have the values
For SQL Server:
ALTER TABLE INVOICE
ADD Status Char(12) NULL;
ALTER TABLE INVOICE
ADD CONSTRAINT StatusValue CHECK
(Status IN (‘Waiting’, ‘In-process’, ‘Finished’, ‘Pending’));
SELECT * FROM INVOICE;
Chapter Seven – Database Processing Applications
F. Create a view called CustomerInvoiceView that has the columns LastName, FirstName,
Phone, InvoiceNumber, Date, Total, and Status.
For SQL Server:
OPTIONAL: We’ll add some data to have some unfinished orders:
/****** Insert New Data **************************************************/
INSERT INTO INVOICE VALUES(
INSERT INTO INVOICE_ITEM VALUES(2017010, 1, 16, 2, 3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2017011, 1, 16, 3, 3.50, 10.50);
SELECT * FROM CustomerInvoiceView;
Chapter Seven – Database Processing Applications
G. Code a PHP page to display CustomerInvoiceView. Using your sample database,
demonstrate that your page works.
!DOCTYPE html>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=UTF-8″>
<title>MDC-Display-CustomerOrder-View</title>
$Conn = odbc_connect($DSN, $User, $Password);
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: ” . $Conn);
}
Chapter Seven – Database Processing Applications
// Create SQL statement
$SQL = “SELECT * FROM CustomerInvoiceView”;
// Execute SQL statement
$RecordSet = odbc_exec($Conn, $SQL);
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′
<tr>
<th>LastName</th>
<th>FirstName</th>
<th>Phone</th>
<th>InvoiceNumber</th>
Chapter Seven – Database Processing Applications
<p class=”footer”>
H. Code two HTML/PHP pages to receive a date value AsOfDate and to display rows of
CustomerInvoiceView for orders having DateIn greater than or equal to AsOfDate. Using
your sample database, demonstrate that your pages work.
Chapter Seven – Database Processing Applications
Here is the code for the form to enter the As Of Date:
<!DOCTYPE html>
<html>
</style>
</head>
<body>
<form action=“MDC-Display-CustomerInvoiceView-By-Date.php” method=“POST”>
<!– Page Headers –>
<h1>
<td>&nbsp;Enter “As Of” Date:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“AsOfDate” size=“25” />
</td>
</p>
</form>
<br />
<hr />
<p class=“footer”>
Chapter Seven – Database Processing Applications
Here is the code to display the CustomerInvoiceView using the As Of Date:
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Frameset//EN”>
<html>
</style>
</head>
<body>
<?php
// Get connection
// Test connection
if (!$Conn)
{
exit (“ODBC Connection Failed: ” . $Conn);
}
// Execute SQL statement
$RecordSet = odbc_exec($Conn, $SQL);
// Test existence of recordset
if (!$RecordSet)
{
Chapter Seven – Database Processing Applications
// Table headers
echo “<table class=’output’ border=’1′
<tr>
<th>LastName</th>
<th>FirstName</th>
<th>Phone</th>
//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 />
Chapter Seven – Database Processing Applications
I. Code two HTML/PHP pages to receive customer Phone, LastName, and FirstName and
to display rows for customers having that Phone, LastName, and FirstName. Using your
sample database, demonstrate that your pages work.
Chapter Seven – Database Processing Applications
Here is the code for the form to enter the customer data:
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Strict//EN”
“http://www.w3.org/TR/html4/strict.dtd”>
<html>
</style>
</head>
<body>
<form action=“MDC-Display-CustomerInvoiceView-By-Customer.php”
method=“POST”>
<!– Page Headers –>
<h1>
Marcia’s Dry Cleaning
</h1>
</td>
</tr>
<tr>
<td>&nbsp;First Name:&nbsp;&nbsp;</td>
<td>
<input type=“text” name=“FirstName” size=“25” />
</td>
</tr>
</table>
<br />
Chapter Seven – Database Processing Applications
<p class=“footer”>
<a href=“../MDC/index.html”>
Return to the Marcia’s Dry Cleaning Home Page
</a>
</p>
<hr />
</body>
</html>
Here is the code to display the CustomerOrder view for the designated customer:
</style>
</head>
<body>
<?php
// Get connection
$DSN = “MDC”;
$User = “MDC-User”;
$Password = “MDC-User+password”;
// Execute SQL statement
$RecordSet = odbc_exec($Conn, $SQL);
// Test existence of recordset
if (!$RecordSet)
{
exit (“SQL Statement Error: ” . $SQL);
}
?>
Chapter Seven – Database Processing Applications
<!– Page Headers –>
<h1>
The Marcia’s Dry Cleaning CustomerInvoiceView
</h1>
<hr />
<h2>
CustomerInvoiceView
</h2>
<?php
// Table headers
echo “<table class=’output’ border=’1′
<tr>
</tr>”;
//Table data
while($RecordSetRow = odbc_fetch_array($RecordSet))
{
echo “<tr>”;
echo “<td>” . $RecordSetRow[‘LastName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘FirstName’] . “</td>”;
echo “<td>” . $RecordSetRow[‘Phone’] . “</td>”;
}
echo “</table>”;
// Close connection
odbc_close($Conn);
?>
<br />
<hr />
<p class=“footer”>
Chapter Seven – Database Processing Applications
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
If you have not already implemented the Garden Glory database shown in Chapter 3 in a
DBMS product, create and populate the GG database now in the DBMS of your choice
(or as assigned by your instructor).
A. Create a user named GG-User with the password GG-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 GG-User in GG. If you are doing this in Microsoft Access 2016, 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.
Chapter Seven – Database Processing Applications
C. Add a new folder to the DBC Web site named GG. Create a Web page for Garden Glory
in this folder—using the file name index.html. Link this page to the DBC Web page.
<!DOCTYPE html>
<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=ISO-8859-1″ />
<title>Garden Glory Demonstration Pages Home Page</title>
</head>
Chapter Seven – Database Processing Applications
<p>Project Question E:&nbsp;&nbsp;&nbsp;
<a href=”ReadOwnedProperty.php”>
Display the OWNED_PROPERTY Table
</a>
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 in the solution to the MDC case questions above. The ODBC system data source
name is GG.