5.4. Forms
This heading does say "Forms," which we briefly touched upon in the previous section, even if it was because we needed somewhere to cache information, such as the contents of the shopping cart. Because of this, and the fact that I don't like to code similar functions too often, much of the client-side JavaScript from the tabular web pages is reused hereor, if you're a friend of nature, recycled. See, not only is Ajax the wave of the future, it is also environmentally friendly.
5.4.1. Read Only
In my opinion, the classic read-only form on an e-commerce website has to be the shipping information page. In fact, it is so well known that the page doesn't even have to be coded as a form. It is perfectly acceptable to "fake it" using Cascading Style Sheets, or simply display the information in some kind of orderly fashion. The advantage of this is that we can avoid having to use the disabled and readonly attributes, which, in the case of the disabled attribute, tends to be a little hard on the eyes because the text is grayed out.
The approach that I've decided upon here is to simply display the information directly from the database. Also, because I'm feeling somewhat adventurous, I've used CSS positioning for content layout instead of the method that I normally employ. Just in case you're wondering, using HTML tables is my usual method of content layout, but I'm undergoing therapy to overcome this shortcoming.
Before going into detail about the SQL that defines the tables needed for this example, I want to clarify one thing again. I am by no means a DBA; I am, according to some, a mad scientist (or mad, at the very least). Any of these can be used as an explanation of why I did what I did when designing these tables. In short, I went a little bit overboard when normalizing.
There isn't a single table to contain information pertaining to a customer. There aren't two tables to contain the information pertaining to a customer, such as one for the address and one for everything else. I made three tables: one for the customer name, one for the address, and one for all other customerrelated information. I'm pretty sure that if you look up the word overkill, this is definition number six, but it does have some advantages that we'll get into later when doing updates.
Now that my long-winded excuse is over, let's take a gander at the SQL that defines the tables and the associated stored procedure that retrieves the information. The SQL for this is shown in Listings 5-23 and 5-24, respectively.
Listing 5-23. SQL to Create MySQL Database Tables
CREATE TABLE address (
address_id int(6) auto_increment NOT NULL,
address_company varchar(255) NULL,
address_line1 varchar(255) NOT NULL,
address_line2 varchar(255) NULL,
address_city varchar(255) NOT NULL,
state_abbreviation varchar(2) NOT NULL,
address_postal varchar(10) NOT NULL,
names_id int(6) NULL,
PRIMARY KEY (address_id),
UNIQUE id (address_id)
);
CREATE TABLE country (
country_id int(6) auto_increment NOT NULL,
country_name varchar(255) NOT NULL,
PRIMARY KEY (country_id),
UNIQUE id (country_id)
);
CREATE TABLE customer (
customer_id int(6) auto_increment NOT NULL,
customer_telephone varchar(10) NULL,
customer_email varchar(255) NOT NULL,
customer_credit_card varchar(16) NOT NULL,
customer_credit_pin varchar(6) NULL,
customer_expiration datetime NOT NULL,
names_id int(6) NULL,
address_id int(6) NULL,
PRIMARY KEY (customer_id),
UNIQUE id (customer_id),
KEY names_key (names_id),
KEY address_key (address_id)
);
CREATE TABLE names (
names_id int(6) auto_increment NOT NULL,
names_last varchar(255) NOT NULL,
names_first varchar(255) NOT NULL,
names_mi varchar(1) NULL,
PRIMARY KEY (names_id),
UNIQUE id (names_id)
);
CREATE TABLE state (
state_abbreviation varchar(2) NOT NULL,
state_name varchar(255) NOT NULL,
country_id int(6) NOT NULL,
PRIMARY KEY (state_abbreviation),
UNIQUE id (state_abbreviation),
KEY country_key (country_id)
);
|
Listing 5-24. MySQL Stored Procedure to Select Address Information
DELIMITER $$
DROP PROCEDURE IF EXISTS `ajax`.`addressSelect`$$
CREATE PROCEDURE `ajax`.`addressSelect`(
email VARCHAR(255)
)
BEGIN
SELECT c.customer_id,
n.names_last,
n.names_first,
n.names_mi,
c.customer_telephone,
c.customer_email,
a.address_company,
a.address_line1,
a.address_line2,
a.address_city,
a.state_abbreviation,
s.state_name,
a.address_postal,
y.country_name
FROM customer c
INNER JOIN names n
ON c.names_id = n.names_id
INNER JOIN address a
ON c.address_id = a.address_id
INNER JOIN state s
ON a.state_abbreviation = s.state_abbreviation
INNER JOIN country y
ON s.country_id = y.country_id
WHERE (email IS NULL OR c.customer_email = email);
END$$
DELIMITER ;
|
The thing that I always find amazing about stored procedures is that they have a tendency to reduce the amount of code needed on the web server. Consider the example that we're currently going over; the PHP merely formats the information returned by the stored procedure for the web browser, as Listing 5-25 illustrates.
Listing 5-25. Customer Display
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<?php
include('common.php');
$title="Customer Display";
$email = substr(@$_SERVER['QUERY_STRING'],6);
$query = "CALL addressSelect(" . $email . ")";
$mysqli = new mysqli($server,$user,$password,$database);
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if(!$result = $mysqli->query($query))
{
printf("Error: %s\n", $mysqli->error);
exit();
}
?>
<head>
<link rel="stylesheet" type="text/css" href="common.css"/>
<title><?php echo $title; ?></title>
<script language="javascript" src="library.js"></script>
</head>
<body onload="initialize()">
<form name="hidden_form" id="hidden_form" action="post.aspx">
<?php
pageHeader($system,$title);
$row = $result->fetch_array(MYSQLI_ASSOC);
$rowLabel ="<div class='rowHeader' style='position: absolute; left: 50px;
right: auto%; bottom: auto; width: 200px; top: ";
$rowData = "<div class='rowData' style='position: absolute; left: 255px;
right: auto; bottom: auto; width: 600px; top: ";
echo $rowLabel . "75px'> Name:</div>";
echo $rowLabel . "92px'> Company:</div>";
echo $rowLabel . "110px'> Address Line 1:</div>";
echo $rowLabel . "127px'> Address Line 2:</div>";
echo $rowLabel . "144px'> City:</div>";
echo $rowLabel . "161px'> State:</div>";
echo $rowLabel . "178px'> Zip/Postal Code:</div>";
echo $rowLabel . "195px'> Country:</div>";
echo $rowLabel . "212px'> Telephone Number:</div>";
echo $rowLabel . "229px'> EMail Address:</div>";
echo $rowData . "75px'> " . $row["names_last"] . ', ' .
$row["names_first"] . ' ' . $row["names_mi"] . "</div>";
echo $rowData . "92px'> " . $row["address_company"] . "</div>";
echo $rowData . "110px'> " . $row["address_line1"] . "</div>";
echo $rowData . "127px'> " . $row["address_line2"] . "</div>";
echo $rowData . "144px'> " . $row["address_city"] . "</div>";
echo $rowData . "161px'> " . $row["state_name"] . "</div>";
echo $rowData . "178px'> " . $row["address_postal"] . "</div>";
echo $rowData . "195px'> " . $row["country_name"] . "</div>";
echo $rowData . "212px'> " . $row["customer_telephone"] . "</div>";
echo $rowData . "229px'> " . $row["customer_email"] . "</div>";
echo "<input type='button' value='Continue to items' onclick='itemsList()'
style='position: absolute; top: 250px; left: 50px; right: auto; bottom:
auto; height: 22px; width: 120px'>";
hidden($row,'customer_id');
hidden($row,'names_last');
hidden($row,'names_first');
hidden($row,'names_mi');
hidden($row,'customer_email');
hidden($row,'customer_id');
?>
</form>
</body>
<?php
mysql_close();
?>
|
5.4.2. Updateable
In the previous example, we covered the display of information from multiple tables, which was easy enough because there wasn't much happening on the client side. The server side was also rather easy; yeah, there were some inner joins, but it is hard to get all worked up about something that easy. There is, however, something that you might have missedI know that I did.
Let's review my overzealous database normalization from a different point of view. First, customer information is spread across three tables. Second, the customer table contains the information that specifies how to find the related information in the other two tables. Third, retrieving the information is merely a matter of using inner joins. So we know what the data looks like and how to get it out of the tables, but the big question is, how do I get it in?
On the bright side, I know how the guy who spent years building a sailboat in his basement felt when his wife said, "Nice, but how are you going to get it out of the basement?" Whoops, didn't think that far ahead. What he ended up doing was supporting the floor joists along one outside basement wall, digging a ramp from the outside to that position, and knocking out a boat-sized hole. It worked, but I want a little more elegant solution. In fact, I want one so elegant that you might think that my earlier screw-up was intentional so that I could demonstrate some really cool features of MySQL.
All my current issues arise from the fact that data in three different tables needs to be updated. Seems simple enoughjust use a transaction. Unfortunately, I forgot to mention that during my earlier fit of normalization, I wrote two stored procedures, shown in Listings 5-26 and 5-27, that I want to use. Waste not, want not.
Listing 5-26. Stored Procedure to Insert Names
DELIMITER $$
DROP PROCEDURE IF EXISTS `ajax`.`namesInsert`$$
CREATE PROCEDURE `ajax`.`namesInsert`(
IN nameLast VARCHAR(255),
IN nameFirst VARCHAR(255),
IN nameMI VARCHAR(1),
OUT namesId INTEGER(6)
)
BEGIN
INSERT INTO names
(names_last,
names_first,
names_mi)
VALUES (nameLast,
nameFirst,
nameMI);
SET namesID = LAST_INSERT_ID();
END$$
DELIMITER ;
|
Listing 5-27. MySQL Stored Procedure to Insert Customer Address Information
DELIMITER $$
DROP PROCEDURE IF EXISTS `ajax`.`addressInsert`$$
CREATE PROCEDURE `ajax`.`addressInsert`(
IN addressCompany VARCHAR(255),
IN addressLine1 VARCHAR(255),
IN addressLine2 VARCHAR(255),
IN addressCity VARCHAR(255),
IN stateAbbreviation VARCHAR(255),
IN addressPostal VARCHAR(10),
IN namesId INTEGER(6),
OUT addressId INTEGER(6)
)
BEGIN
INSERT INTO address
(address_company,
address_line1,
address_line2,
address_city,
state_abbreviation,
address_postal,
names_id)
VALUES (addressCompany,
addressLine1,
addressLine2,
addressCity,
stateAbbreviation,
addressPostal,
namesId);
SET addressId = LAST_INSERT_ID();
END$$
DELIMITER ;
|
Alright, if I have it straight and haven't painted myself into another corner, what is needed is a way to tie these stored procedures together. I suppose that I could somehow stick them together using PHP, but that seems too much like making the sailboat out of duct tape, and that solution is a little too Red Green for me. I ended up writing a third stored procedure (see Listing 5-28) that uses transactions and calls the other two stored procedures.
Listing 5-28. MySQL Stored Procedure That Calls Other Stored Procedures
DELIMITER $$
DROP PROCEDURE IF EXISTS `ajax`.`customerInsert`$$
CREATE PROCEDURE `ajax`.`customerInsert`(
IN namesLast VARCHAR(255),
IN namesFirst VARCHAR(255),
IN namesMI VARCHAR(1),
IN customerTelephone VARCHAR(10),
IN customerEmail VARCHAR(255),
IN customerCreditCard VARCHAR(16),
IN customerCreditPin VARCHAR(6),
IN customerExpiration DATETIME,
IN addressCompany VARCHAR(255),
IN addressLine1 VARCHAR(255),
IN addressLine2 VARCHAR(255),
IN addressCity VARCHAR(255),
IN stateAbbreviation VARCHAR(2),
IN addressPostal VARCHAR(10),
OUT customerId INTEGER(6)
)
BEGIN
DECLARE errorInd INTEGER DEFAULT 0;
DECLARE namesId INTEGER(6);
DECLARE addressId INTEGER(6);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errorInd = 1;
START TRANSACTION;
CALL namesInsert(namesLast,
namesFirst,
namesMI,
namesId);
CALL addressInsert(addressCompany,
addressLine1,
addressLine2,
addressCity,
stateAbbreviation,
addressPostal,
namesId,
addressId);
INSERT INTO customer
(customer_telephone,
customer_email,
customer_credit_card,
customer_credit_pin,
customer_expiration,
names_id,
address_id)
VALUES (customerTelephone,
customerEmail,
customerCreditCard,
customerCreditPin,
customerExpiration,
namesId,
addressId);
IF errorInd = 0 THEN
COMMIT;
SET customerId = LAST_INSERT_ID();
ELSE
ROLLBACK;
SET customerId = 0;
END IF;
END$$
DELIMITER ;
|
Now that the sailboat is out of the basement, the remaining task is simply a matter of putting all the pieces together, as shown in Listing 5-29 and Figure 5-4.
Listing 5-29. Customer Display Page
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<?php
include('common.php');
$title="Customer Display";
$email = substr(@$_SERVER['QUERY_STRING'],6);
$query = "CALL addressSelect('" . $email . "')";
$mysqli = new mysqli($server,$user,$password,$database);
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if(!$result = $mysqli->query($query))
{
printf("Error: %s\n", $mysqli->error);
exit();
}
?>
<head>
<link rel="stylesheet" type="text/css" href="common.css"/>
<title><?php echo $title; ?></title>
<script language="javascript" src="library.js"></script>
</head>
<body onload="initialize()">
<form name="hidden_form" id="hidden_form"
action="customerInput.php">
<?php
pageHeader($system,$title);
$row = $result->fetch_array(MYSQLI_ASSOC);
$rowLabel ="<div class='rowHeader' style='valign: center; height: 20px;
width: 200px;'> %s</div>";
$rowData = "<div class='rowData' style='position: absolute; left: 255px;
right: auto; bottom: auto; width: 600px; top: ";
?>
<table border="0" width="980px" id="Table1" border="1" cellpadding="2"
cellspacing="2">
<?php
echo "<tr><th class='rowHeader' width='20%' align='left'> First
Name:</th>";
printf("<td class='rowData'> <input type='text' name='names_first'
id='names_first' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["names_first"]);
echo "<tr><th class='rowHeader' align='left'> Middle Initial:</th>";
printf("<td class='rowData'> <input type='text' name='names_mi'
id='names_mi' size='2' maxlength='1' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["names_mi"]);
echo "<tr><th class='rowHeader' align='left'> Last Name:</th>";
printf("<td class='rowData'> <input type='text' name='names_last'
id='names_last' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["names_last"]);
echo "<tr><th class='rowHeader' align='left'> Address Line 1:</th>";
printf("<td class='rowData'> <input type='text' name='address_line1'
id='address_line1' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["address_line1"]);
echo "<tr><th class='rowHeader' align='left'> Address Line 2:</th>";
printf("<td class='rowData'> <input type='text' name='address_line2'
id='address_line2' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["address_line2"]);
echo "<tr><th class='rowHeader' align='left'> City:</th>";
printf("<td class='rowData'> <input type='text' name='address_city'
id='address_city' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["address_city"]);
echo "<tr><th class='rowHeader' align='left'> State:</th><td
class='rowData'>";
stateSelect($server,$user,$password,$database,$row['state_abbreviation']);
echo "</td></tr><tr><th class='rowHeader' align='left'> Postal
Code:</th>";
printf("<td class='rowData'> <input type='text' name='address_postal'
id='address_postal' size='50' maxlength='10' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["address_postal"]);
echo "<tr><th class='rowHeader' align='left'> Telephone
Number:</th>";
printf("<td class='rowData'> <input type='text'
name='customer_telephone' id='customer_telephone' size='50' maxlength='10'
value='%s'
onchange='changeEvent(this)'></td></tr>",$row["customer_telephone"]);
echo "<tr><th class='rowHeader' align='left'> E-Mail Address:</th>";
printf("<td class='rowData'> <input type='text' name='customer_email'
id='customer_email' size='50' maxlength='255' value='%s'
onchange='changeEvent(this)'></td></tr>",$row["customer_email"]);
?>
</table>
<?php
echo "<input type='button' value='Place Order' onclick='submitForm()'>";
?>
</form>
</body>
<?php
mysqli_close($mysqli);
function stateSelect($server,$user,$password,$database,$value)
{
$query = "CALL stateSelect(null)";
$mysqli = new mysqli($server,$user,$password,$database);
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if(!$result = $mysqli->query($query))
{
printf("Error: %s\n", $mysqli->error);
exit();
}
echo "<select id='state_abbreviation' name='state_abbreviation'
onchange='changeEvent(this)'>";
while($row = $result->fetch_array(MYSQLI_ASSOC))
{
if($row['state_abbreviation'] == $value)
printf("<option value='%s'
selected='true'>%s</option>",$row[state_abbreviation],$row[state_name]);
else
printf("<option
value='%s'>%s</option>",$row[state_abbreviation],$row[state_name]);
}
echo "</select>";
mysqli_close($mysqli);
}
?>
|
|
Free JavaScript Editor
JavaScript Debugger