Transcript PHP_Bible
PHP Bible
Chapter 21: Building Forms from Queries
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
Wiley and the book authors, 2002
Summary
HTML forms for use with databases
Basic form submission to a database
Self-submission
Editing data with an HTML form
Data-driven forms
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
Wiley and the book authors, 2002
HTML forms
Form handling is one of PHP's best features. The combination
of HTML to construct a data-input form, PHP to handle the
data, and a database server to store the data lies at the heart of
all kinds of useful Web tasks
You already know most of what you need to make good forms
to be handled by PHP and a database
Always use a NAME for every data entry element (INPUT,
SELECT, TEXTAREA). These NAME attributes will become
PHP "variable" names
A form field NAME doesn't have to be the same as the database
field name, but it's often a good idea
You should usually specify a VALUE in your form field tags
You can pass hidden variables from form to form using the
HIDDEN INPUT field
You can pass multiple variables in an array
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
Wiley and the book authors, 2002
Basic form submission to a database
Submitting data to a database via an HTML form is straightforward if the
form and form-handler are two separate pages
*Newsletter_signup.html*
<HTML>
<HEAD><TITLE>Newsletter Signup</TITLE></HEAD>
<BODY>
<TABLE BORDER=0 CELLPADDING=10 WIDTH=100%>
<TR>
<TD BGCOLOR="#F0F8FF" ALIGN=CENTER VALIGN=TOP WIDTH=17%></TD>
<TD BGCOLOR="#FFFFFF" ALIGN=LEFT VALIGN=TOP WIDTH=83%>
<H1>Newsletter sign-up form</H1>
<P>Enter your email address and we will send you our weekly
newsletter.</P>
<FORM METHOD="post" ACTION="formhandler.php">
<INPUT TYPE="text" SIZE=25 NAME="email">
<BR><BR>
<INPUT TYPE="submit" NAME="submit" VALUE="Submit">
</FORM>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
Wiley and the book authors, 2002
Basic form submission to a database (cont.)
*formhandler.php*
<HTML>
<HEAD><TITLE>Newsletter Signup Status</HEAD>
<BODY>
<TABLE BORDER=0 CELLPADDING=10 WIDTH=100%>
<TR>
<TD BGCOLOR="#F0F8FF" ALIGN=CENTER VALIGN=TOP WIDTH=17%></TD>
<TD BGCOLOR="#FFFFFF" ALIGN=LEFT VALIGN=TOP WIDTH=83%>
<?php
if (!$_POST['email'] || $_POST['email'] == "" || strlen($_POST['email']) > 30)
echo '<P>There is a problem. Did you enter an email address?</P>';
else
{
mysql_connect("localhost", "phpuser", "sesame") or die("Failure to communicate with
database");
mysql_select_db("test");
// Insert email address
$mod_email = trim(mysql_escape_string($_POST['email']));
$query = "INSERT INTO mailinglist SET Email='$mod_email',Source='newsletter_signup.html'";
$result = mysql_query($query);
if (mysql_affected_rows() == 1)
echo '<P>Your information has been recorded.</P>';
else
{
error_log(mysql_error());
echo '<P>Something went wrong with your signup attempt.</P>';
}
}
?>
</TD></TR></TABLE></BODY></HTML>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
5
Wiley and the book authors, 2002
Basic form submission to a database (cont.)
Having a separate form and form handler is a very clean design which can
potentially be easier to maintain. However there are quite a few things you
might want to do that can't be done easily with this model
If something goes wrong with the submission, it's very difficult to redisplay
the form with the values you just filled in. This is especially important with a
user registration form, where you might want to check for unique e-mail
addresses or matching passwords, and reject the registration with an error
message if it doesn't pass the tests. Users may get annoyed if one type causes
them to lose all the data they filled in
Another situation where self-submission is better is when you need to submit
the same form more than once (e.g. you are applying for auto insurance and
you need to give the particulars of multiple cars)
Separate form and form handlers also make it difficult to pull data from the
database, edit it, and submit it – repeating the process however many times it
takes for the user to be satisfied
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
6
Wiley and the book authors, 2002
Self-submission
Self-submission refers to the process of combining one or more
forms and form-handlers in a single script, using the HTML
FORM standard to submit data to the script one or more times
Self-submission is accomplished by specifying the same script
name as the ACTION target in the form handler
<FORM METHOD="POST" ACTION="<?php echo $_SERVER['PHP_SELF']; ?>">
The single most important thing to remember about selfsubmitting forms is the logic comes before the display.
In order to determine if this is the first time the script has
executed, you can use a "stage" variable. This can be checking
if the SUBMIT variable has a value or creating a hidden field in
the form and checking if it has a value
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
7
Wiley and the book authors, 2002
Self-submission (example)
* newsletter_signup.php *
<?php
if (isset($_POST['submitted']))
{
if (!$_POST['email'] || $_POST['email'] == "" || strlen($_POST['email'] > 30))
$message = '<P>There is a problem. Did you enter an email address?</P>';
else
{
// Open connection to the database
mysql_connect("localhost","phpuser","sesame") or die("Can't communicate with
database");
mysql_select_db("test");
// Insert email address
$mod_email = trim(mysql_escape_string($_POST['email']));
$query = "INSERT INTO mailinglist SET
Email='$mod_email',Source='newsletter_signup.html'";
$result = mysql_query($query);
if (mysql_affected_rows() == 1)
{
$message = '<P>Your information has been recorded.</P>';
$noform_var = 1;
}
else
{
error_log(mysql_error());
$message = '<P>Something went wrong with your signup attempt.</P>';
}
}
_______________________________________________________________________________________________________________
}
PHP Bible, 2nd Edition
8
Wiley and the book authors, 2002
Self-submission (example)
// Show the form in every case except successful submission
if (!$noform_var)
{
$thisfile = $_SERVER['PHP_SELF'];
$message .= '
<P>Enter your email address and we will send you our weekly newsletter.</P>
<FORM METHOD="post" ACTION="'.$thisfile.'">
<INPUT TYPE="text" SIZE=25 NAME="email">
<INPUT TYPE="hidden" NAME="submitted" VALUE="true">
<BR><BR>
<INPUT TYPE="submit" NAME="submit" VALUE="Submit">
</FORM>
';
}
}
?>
<HTML>
<HEAD><TITLE>Newsletter Signup</HEAD>
<BODY>
<TABLE BORDER=0 CELLPADDING=10 WIDTH=100%>
<TR>
<TD BGCOLOR="#F0F8FF" ALIGN=CENTER VALIGN=TOP WIDTH=17%></TD>
<TD BGCOLOR="#FFFFFF" ALIGN=LEFT VALIGN=TOP WIDTH=83%>
<H1>Newsletter sign-up form</H1>
<?php echo $message; ?>
</TD>
</TR>
</TABLE></BODY></HTML>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
9
Wiley and the book authors, 2002
Self-submission (cont.)
The first time you load up this page, you should see a normal HTML form.
If you submit without any data or with a string that's too long, you'll see an error
message and the form again
If something goes wrong with the database INSERT, you'll see an error message and
the form again
Only if the INSERT completes successfully will you not see the form again
We only need to check for two states of the form (unsubmitted or submitted) so we
can either use the Submit button or use a hidden form field or session variable
Another issue with self-submitted forms is navigation so you'll need to decide
Whether the form can be resubmitted multiple times by the user
Whether the user decides when to move on by clicking on a link or the form
moves users along automatically
Whether you need to pass variables on to the next page
Whether you want to control where the user can go next or if you want to give
the users multiple choices
The answers to these questions will determine whether you need a control, another
form, a simple link or button, or multiple links
Make sure you adequately explain what is going to happen at each step
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
10
Wiley and the book authors, 2002
Editing database data with an HTML form
PHP is brilliant at putting variables into a database, but it really
shines when taking data from a database, displaying it in a form
to be edited, and then putting it back in the database
It's HTML-embeddedness, easy variable-passing, and slick
database connectivity are at their best in this kind of job
TEXT and TEXTAREA are the most straightforward types of
HTML input fields because they enjoy an unambiguous one-toone relationship between identifier and content
There is only one possible VALUE per NAME
You just pull the data field from a record in the database and
display it in the form by referencing the appropriate array value
The following example takes a description out of the database
and allows you to edit it
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
11
Wiley and the book authors, 2002
TEXT and TEXTAREA form example
<?php
// Open connection to the database
mysql_connect('sql.gettelco.com','MGMT380b','MGMT380b')
or die('Failure to communicate with database');
mysql_select_db('MGMT380b');
// check to see if this form had been submitted to itself
if (isset($_POST['submit']) and $_POST['submit'] == 'Submit')
{
// Format the data
$ItemID = trim($_POST['ItemID']);
$Title = trim($_POST['Title']);
$Description = trim($_POST['Description']);
$update_query = 'UPDATE catalog SET '.
'Title = "'.mysql_escape_string($Title).'", '.
'Description = "'.mysql_escape_string($Description).'" '.
'WHERE (ItemID = "'.$ItemID.'")';
$result = mysql_query($update_query) or die('Couldn\'t execute query');
if (mysql_affected_rows() == 1)
$success_msg = '<P>Your description has been updated</P>';
else
{
error_log(mysql_error());
$success_msg = '<P><B>Something went wrong</B></P>';
}
}
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
12
Wiley and the book authors, 2002
TEXT and TEXTAREA form example (cont.)
// If form had not been submitted to itself (e.g. first time viewing it)
elseif (isset($_GET['ItemID']))
{
// Get the appropriate comment header and comment
$ItemID = $_GET['ItemID'];
$select_query = 'SELECT Title, Description FROM catalog '.
'WHERE (ItemID = "'.$ItemID.'")';
$result = mysql_query($select_query) or die('Unable to execute query');
$row = mysql_fetch_assoc($result);
$Title = stripslashes($row['Title']);
$Description = stripslashes($row['Description']);
$success_msg = 'Editing Item ID: '.$ItemID;
}
else
die ('Must set ItemID');
$this_page = $_SERVER['PHP_SELF'];
$form_page = '<HTML><HEAD><TITLE>Description Edit</TITLE></HEAD><BODY>
<H1>Description Edit</H1>
'.$success_msg.'
<FORM METHOD="post" ACTION="'.$this_page.'">
<INPUT TYPE="text" SIZE="40" NAME="Title" VALUE="'.$Title.'"> <BR><BR>
<TEXTAREA NAME="Description" ROWS="10" COLS="50">'.$Description.'</TEXTAREA><BR><BR>
<INPUT TYPE="hidden" NAME="ItemID" VALUE="'.$ItemID.'">
<INPUT TYPE="submit" NAME="submit" VALUE="Submit"></FORM></BODY></HTML>';
print ($form_page);
?>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
13
Wiley and the book authors, 2002
TEXT and TEXTAREA form example (cont.)
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
14
Wiley and the book authors, 2002
SELECT form example
The SELECT field type is perhaps the most interesting of all
It can handle the largest number of options (as opposed to the
CHECKBOX which can only have 2 options or the
RADIOBUTTON which should have 5 or fewer options)
It can also allow the user to select multiple options that can be
passed back to the script using arrays
Chapter 28 has even more exciting ways of combining SELECT
statements in PHP with JavaScript
In the following example we will use a SELECT field to pick
which category a product belongs to and bring in the text for the
select options from the related table
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
15
Wiley and the book authors, 2002
SELECT form example (cont.)
<?php
// Open connection to the database
mysql_connect('sql.gettelco.com','MGMT380b','MGMT380b')
or die('Failure to communicate with database');
mysql_select_db('MGMT380b');
// check to see if this form had been submitted to itself
if (isset($_POST['submit']) and $_POST['submit'] == 'Submit')
{
// Format the data
$ItemID = trim($_POST['ItemID']);
$Title = trim($_POST['Title']);
$Description = trim($_POST['Description']);
$Category = $_POST['Category'];
$update_query = 'UPDATE catalog SET '.
'Title = "'.mysql_escape_string($Title).'", '.
'Category = "'.$Category.'", '.
'Description = "'.mysql_escape_string($Description).'" '.
'WHERE (ItemID = "'.$ItemID.'")';
$result = mysql_query($update_query) or die('Couldn\'t execute query');
if (mysql_affected_rows() == 1)
$success_msg = '<P>Your description has been updated</P>';
else
{
error_log(mysql_error());
$success_msg = '<P><B>Something went wrong</B></P>';
}
}
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
16
Wiley and the book authors, 2002
SELECT form example (cont.)
// If form had not been submitted to itself (e.g. first time viewing it)
elseif (isset($_GET['ItemID']))
{
// Get the appropriate comment header and comment
$ItemID = $_GET['ItemID'];
$select_query = 'SELECT Title, Description, Category FROM catalog '.
'WHERE (ItemID = "'.$ItemID.'")';
$result = mysql_query($select_query) or die('Unable to execute query');
$row = mysql_fetch_assoc($result);
$Title = stripslashes($row['Title']);
$Description = stripslashes($row['Description']);
$Category = $row['Category'];
$success_msg = 'Editing Item ID: '.$ItemID;
}
else
die ('Must set ItemID');
$this_page = $_SERVER['PHP_SELF'];
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
17
Wiley and the book authors, 2002
SELECT form example (cont.)
$select_str = '<SELECT NAME="Category" SIZE="1">';
$select_result = mysql_query('SELECT * FROM categories') or die('?');
while ($row = mysql_fetch_assoc($select_result))
{
if ($row['category_id'] == $Category)
$select_str .= '<OPTION VALUE="'.$row['category_id'].'" SELECTED>'.
$row['description'].'</OPTION>';
else
$select_str .= '<OPTION VALUE="'.$row['category_id'].'">'.
$row['description'].'</OPTION>';
}
$select_str .= '</SELECT>';
$form_page = '<HTML><HEAD><TITLE>Description Edit</TITLE></HEAD><BODY>
<H1>Description Edit</H1>
'.$success_msg.'
<FORM METHOD="post" ACTION="'.$this_page.'">
<INPUT TYPE="text" SIZE="40" NAME="Title" VALUE="'.$Title.'"> <BR><BR>
<TEXTAREA NAME="Description" ROWS="10" COLS="50">'.$Description.'</TEXTAREA><BR><BR>
'.$select_str.'<BR><BR>
<INPUT TYPE="hidden" NAME="ItemID" VALUE="'.$ItemID.'">
<INPUT TYPE="submit" NAME="submit" VALUE="Submit"></FORM></BODY></HTML>';
print ($form_page);
?>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
18
Wiley and the book authors, 2002
SELECT form example (cont.)
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
19
Wiley and the book authors, 2002
Multiple TEXT fields stored in an array
In many instances, it's preferable to store multiple input fields
into a single array
If you have a catalog of items to display and you want to have
TEXT fields to allow the Web user to enter the quantity of each
item they want to purchase
This example uses SESSION variables to pass information to
other PHP scripts on the site (SESSION variables will be
discussed more in chapter 27 but behave very similarly to GET
and POST variables)
This example also uses a separate form processing script so that
the user could place orders from multiple pages that all get
processed from the same script
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
20
Wiley and the book authors, 2002
Multiple TEXT fields stored in an array (example)
<HTML>
<HEAD>
<TITLE>Order entry page</TITLE>
</HEAD>
<H1>Order entry page</H1>
<FORM ACTION="ProcessCatalogPage.php" METHOD="POST">
<TABLE border="1">
<TR>
<TH>Picture</TH><TH>Item ID</TH><TH>Title</TH><TH>Description</TH>
<TH># In Inventory</TH><TH>Price</TH><TH># Ordered</TH>
</TR>
<?php
// Allow the use of session variables
session_start();
// Open connection to the database
$db_resource = mysql_connect('sql.gettelco.com','MGMT380b','MGMT380b')
or die('Failure to communicate with database');
mysql_select_db('MGMT380b');
$sql = 'SELECT PictureURL,ItemID,Title,Description,Inventory,Price FROM
catalog';
$result_id = mysql_query($sql) or die ('ERROR executing query');
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
21
Wiley and the book authors, 2002
Multiple TEXT fields stored in an array (example)
while ($row = mysql_fetch_assoc($result_id))
{
print ('<TR>');
print ('<TD><IMG SRC="'.$row['PictureURL'].'"></TD>');
print ('<TD>'.$row['ItemID'].'</TD>');
print ('<TD><B>'.$row['Title'].'</B></TD>');
print ('<TD><FONT SIZE="-1">'.$row['Description'].'</FONT></TD>');
print ('<TD>'.$row['Inventory'].'</TD>');
print ('<TD>$'.$row['Price'].'</TD>');
if (isset($_SESSION['ordered_items']) and
isset($_SESSION['ordered_items'][$row['ItemID']]))
print ('<TD><INPUT TYPE="text" NAME="ordered_items['.$row['ItemID'].
'][quantity]" VALUE="'.
$_SESSION['ordered_items'][$row['ItemID']]['quantity'].
'" SIZE="4"></TD>');
else
print ('<TD><INPUT TYPE="text" NAME="ordered_items['.$row['ItemID'].
'][quantity]" VALUE="0" SIZE="4"></TD>');
print ('</TR>'."\n");
}
?>
</TABLE><BR><BR>
<INPUT TYPE="submit" NAME="submit" Value="Order">
<INPUT TYPE="reset">
</FORM>
</BODY>
</HTML>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
22
Wiley and the book authors, 2002
Multiple TEXT fields stored in an array (example)
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
23
Wiley and the book authors, 2002
Processessing Multiple TEXT fields stored in an array
<HTML>
<HEAD>
<TITLE>Process order</TITLE>
</HEAD>
<BODY>
<H1>Items ordered</H1>
<TABLE BORDER="1">
<?php
session_start();
if (isset($_POST['ordered_items']) and is_array($_POST['ordered_items']))
{
foreach($_POST['ordered_items'] as $ItemID => $value)
$_SESSION['ordered_items'][$ItemID] = $value;
}
else
die ('Did not order any items');
foreach ($_SESSION['ordered_items'] as $ItemID => $value)
{
if ($value['quantity'] > 0)
{
print('<TR>');
print('<TD>'.$ItemID.'</TD>');
print('<TD>'.$value['quantity'].'</TD>');
print('</TR>');
}
}
?>
</TABLE><BR>
<A href="CatalogPage.php">Continue shopping</A>
</BODY>
_______________________________________________________________________________________________________________
</HTML>
PHP Bible, 2nd Edition
24
Wiley and the book authors, 2002