Example 10-6 shows the db.inc file that lists the DBMS credentials for connecting to the winestore database. The settings must be changed for a local installation of the winestore application.
Example 10-6. The db.inc include file
<?php $hostName = "localhost"; $databaseName = "winestore"; $username = "fred"; $password = "shhh"; ?>
The db.inc include file stores the DBMS and database credentials to access the online winestore. The hostName setting is the server name of the DBMS, the databaseName setting is the winestore database name, and the username and password are those used to access the MySQL DBMS. This file is identical to Example 4-7 and is discussed in Chapter 4.
The include.inc file shown in Example 10-7 stores the common function used throughout the winestore application.
Example 10-7. The include.inc file
<?php
// This file contains functions used in more than
// one script in the cart module
include 'db.inc';
include 'error.inc';
// Untaint user data
function clean($input, $maxlength)
{
$input = substr($input, 0, $maxlength);
$input = EscapeShellCmd($input);
return ($input);
}
// Print out the varieties for a wineID
function showVarieties($connection, $wineID)
{
// Find the varieties of the current wine,
// and order them by id
$query = "SELECT gv.variety
FROM grape_variety gv,
wine_variety wv, wine w
WHERE w.wine_id = wv.wine_id
AND wv.variety_id = gv.variety_id
AND w.wine_id = $wineID
ORDER BY wv.id";
// Run the query
if (!($result = @ mysql_query($query, $connection)))
showerror( );
$varieties = "";
// Retrieve and print the varieties
while ($row = @ mysql_fetch_array($result))
$varieties .= " " . $row["variety"];
return $varieties;
}
// Show the user the details of one wine in their
// cart
function showWine($wineId, $connection)
{
global $username;
global $password;
global $databaseName;
$wineQuery = "SELECT year, winery_name, wine_name
FROM winery, wine
WHERE wine.winery_id = winery.winery_id
AND wine.wine_id = $wineId";
$open = false;
// If a connection parameter is not passed, then
// use our own connection to avoid any
// locking problems
if (!isset($connection))
{
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
showerror( );
if (!mysql_select_db($databaseName, $connection))
showerror( );
$open = true;
}
// Run the query created above on the database
// through the connection
if (!($result = @ mysql_query ($wineQuery,
$connection)))
showerror( );
$row = @ mysql_fetch_array($result);
// Print the wine details
$result = $row["year"] . " " .
$row["winery_name"] . " " .
$row["wine_name"];
// Print the varieties for this wine
$result .= showVarieties($connection, $wineId);
if ($open == true)
@ mysql_close($connection);
return $result;
}
// Print out the pricing information for a wineID
function showPricing($connection, $wineID)
{
// Find the price of the cheapest inventory
$query = "SELECT min(cost)
FROM inventory
WHERE wine_id = $wineID";
// Run the query
if (!($result = @ mysql_query($query,
$connection)))
showerror( );
// Retrieve the cheapest price
$row = @ mysql_fetch_array($result);
printf("<b>Our price: </b>$%.2f",
$row["min(cost)"]);
printf(" ($%.2f a dozen)",
($row["min(cost)"] * 12));
}
// Show the total number of items and dollar value of
// the shopping cart, as well as a clickable cart icon
function showCart($connection)
{
global $order_no;
// Initialise an empty cart
$cartAmount = 0;
$cartCount = 0;
// If the user has added items to their cart,
// then the variable order_no will be registered
if (session_is_registered("order_no"))
{
$cartQuery = "SELECT qty, price " .
"FROM items " .
"WHERE cust_id = -1 " .
"AND order_id = " . $order_no;
// Find out the number and the dollar value of
// the items in the cart. To do this, we run
// the cartQuery through the connection on
// the database
if (!($result = @ mysql_query ($cartQuery,
$connection)))
showerror( );
while ($row = @ mysql_fetch_array($result))
{
$cartAmount += $row["price"] * $row["qty"];
$cartCount += $row["qty"];
}
}
// This sets up the cart picture.
// The user can click on it to see the contents of
// their cart. It also contains JavaScript, so that
// the cart highlights
// when the mouse is over it (a "roll-over")
echo "<table>\n<tr>\n\t<td>";
echo "<a href=\"example.cart.2.php\" " .
"onMouseOut=\"cart.src='cart_off.jpg'\" " .
"onMouseOver=\"cart.src='cart_on.jpg'\"> " .
"<img src=\"cart_off.jpg\" vspace=0 border=0 " .
"alt=\"cart picture\" name=\"cart\"></a>\n";
echo "\t</td>\n";
printf("\t<td>Total in cart: $%.2f (%d items)</td>\n",
$cartAmount, $cartCount);
echo "</tr>\n</table>";
}
// Display any messages that are set, and then
// clear the message
function showMessage( )
{
global $message;
// Is there an error message to show the user?
if (session_is_registered("message"))
{
echo "<h3>";
echo "<font color=\"red\">$message</font></h3>";
// Clear the error message
session_unregister("message");
$message = "";
}
}
// Show whether the user is logged in or not
function showLogin( )
{
global $loginUsername;
// Is the user logged in?
if (session_is_registered("loginUsername"))
echo "<p align=\"right\">You are currently " .
"logged in as <b>$loginUsername</b></p>\n";
else
echo "<p align=\"right\">You are currently " .
"not logged in</p>\n";
}
// Show the user a login or logout button.
// Also, show them membership buttons as appropriate.
function loginButtons( )
{
if (session_is_registered("loginUsername"))
{
echo "\n\t<td><input type=\"submit\"" .
" name=\"logout\" value=\"Logout\"></td>\n";
echo "\n\t<td><input type=\"submit\"" .
"name=\"account\" value=\"Change " .
"Details\"></td>\n";
}
else
{
echo "\t<td><input type=\"submit\" " .
"name=\"login\" value=\"Login\"></td>\n";
echo "\n\t<td><input type=\"submit\" " .
"name=\"account\" value=\"Become " .
"a Member\"></td>\n";
}
}
// Get the cust_id using loginUsername
function getCustomerID($loginUsername, $connection)
{
global $databaseName;
global $username;
global $password;
global $hostName;
$open = false;
// If a connection parameter is not passed, then
// use our own connection to avoid any locking
// problems
if (!isset($connection))
{
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
showerror( );
if (!mysql_select_db($databaseName,
$connection))
showerror( );
$open = true;
}
// We find the cust_id through the users table,
// using the session variable holding their
// loginUsername.
$query = "SELECT cust_id
FROM users
WHERE user_name = \"$loginUsername\"";
if (($result = @ mysql_query ($query, $connection)))
$row = mysql_fetch_array($result);
else
showerror( );
if ($open == true)
@ mysql_close($connection);
return($row["cust_id"]);
}
// Produce a <select> list containing database
// elements
function selectDistinct ($connection,
$tableName,
$columnName,
$pulldownName,
$additionalOption,
$defaultValue)
{
$defaultWithinResultSet = FALSE;
// Query to find distinct values of $columnName
// in $tableName
$distinctQuery = "SELECT DISTINCT $columnName
FROM $tableName";
// Run the distinctQuery on the databaseName
if (!($resultId = @ mysql_query ($distinctQuery,
$connection)))
showerror( );
// Retrieve all distinct values
$i = 0;
while ($row = @ mysql_fetch_array($resultId))
$resultBuffer[$i++] = $row[$columnName];
// Start the select widget
echo "\n<select name=\"$pulldownName\">";
// Is there an additional option?
if (isset($additionalOption))
// Yes, but is it the default option?
if ($defaultValue == $additionalOption)
// Show the additional option as selected
echo "\n\t<option selected>$additionalOption";
else
// Just show the additional option
echo "\n\t<option>$additionalOption";
// check for a default value
if (isset($defaultValue))
{
// Yes, there's a default value specified
// Check if the defaultValue is in the
// database values
foreach ($resultBuffer as $result)
if ($result == $defaultValue)
// Yes, show as selected
echo "\n\t<option selected>$result";
else
// No, just show as an option
echo "\n\t<option>$result";
} // end if defaultValue
else
{
// No defaultValue
// Show database values as options
foreach ($resultBuffer as $result)
echo "\n\t<option>$result";
}
echo "\n</select>";
} // end of function
?>
The include.inc file shown in Example 10-7 contains the following functions that are used throughout the winestore application:
- string clean(string
input, integermaxlength) -
Untaints a user-supplied
inputstring by processing it withEscapeShellCmd( )and takes a substring of lengthmaxlength. Returns the untainted string. This function is discussed in Chapter 5. - void showVarieties(resource
connection, intwineID) -
Queries the winestore database through the DBMS
connectionresource. Prints the wine varieties associated with the wine identified by thewine_idwineID. - string showWine(int
wineID, resourceconnection) -
Queries the winestore database through the DBMS
connectionresource. Returns the year, winery name, wine details, and varieties of the wine identified bywineID. The functionshowVarieties( )is called to output the varieties. If theconnectionresource isNULL, a new nonpersistent connection to the DBMS is opened and closed; this can be used to avoid having to lock the tables associated with a wine if the calling function requires locks for other operations. - void showPricing(resource
connection, intwineID) -
Queries the winestore database through the DBMS
connectionresource. Prints the price of the wine identified bywineIDand the cost of a case of that wine where-for simplicity-a case of 12 bottles costs 12 times as much as 1 bottle. - void showCart(resource
connection) -
Produces a shopping cart icon that is an embedded link to the script cart.2. The icon is a rollover, in which JavaScript loads a highlighted cart image when the mouse is over the image. The script also queries the winestore database through the DBMS
connectionresource and sums the total number of items and the dollar value of the items in the user's shopping cart. These total values are reported next to the cart. - void showMessage( )
-
Reports any messages registered in the session variable
message. If a message is displayed, the session variablemessageis unregistered so that a message appears only once. - void showLogin( )
-
Reports whether the user is logged in or not based on whether the
loginUsernamesession variable is registered. If the user is logged in, the message includes the user's login name. - void loginButtons( )
-
Displays
<form>buttons. If the user is logged in, the "logout" and "customer change details" buttons are shown. If the user isn't logged in, the "login" and "become a member" buttons are shown. - string getCustomerID(string
loginUsername, resourceconnection) -
Returns the
cust_idassociated with the user's email address or login nameloginUsername. Queries the winestore database through the DBMSconnectionresource. If theconnectionresource isNULL, a new, nonpersistent connection to the DBMS is opened and closed; this can be used to avoid having to lock the tables associated with a wine if the calling function requires locks for other operations. - void selectDistinct (resource
connection, stringtableName, stringcolumnName, stringpulldownName, stringadditionalOption, stringdefaultValue) -
Produces a drop-down list using the HTML
<select>element. Values from thecolumnNameattribute of the tabletableNameare used to populate the<select>element with the namepulldownName. The<option>defaultValueis shown selected, and an additional nondatabase value-such asAll-can be added with theadditionalOptionparameter. This function is described in detail in Chapter 5.