Richard H. Nilsson, July 10, 2003
Edit the guestbook
In Part 1, we created a database and built a table in it called "Messages". In Part 2, we constructed an interactive form for making entries in the guestbook. Part 3 provided functions to display a gustbook a some number of entries at a time, and now we will build an editor only you can use to clean up entries maliciously posted.
The best way to start is to re-use as much previously written code as possible, so we'll begin with the entry form. All we have to do is top it with a query to get the existing entry field contents into the form, then the rest of the entry form code can replace it. We may also just want to delete the record completely, so we'll add a "Delete" query.
First, let's start the page with the normal opening. The only thing of note here is that we show how to import an external stylesheet. In fact, since this page will be hidden in a secured subdirectory, the import rule has to point up one level ("../gbk.css"). The form verification is the same as for the entry form:
<?php $page_title = "Edit Guestbook Entry";
?>
<html>
<head>
<title><?php echo $page_title ?></title>
<style type="text/css">
</style>
<script type="text/javascript" language="JavaScript"><!--
function verify(form) {
if (form["fname"].value == '')
{
alert("You forgot to enter your first name.");
return false;
}
if (form["lname"].value == '')
{
alert("You forgot to enter your last name.");
return false;
}
j.compile("[A-Za-z0-9._-]+@[^.]+\..+");
if (!form["email"].value == '')
{
if (!j.test(form["email"].value))
{
alert("You must supply a valid email address.");
return false;
}
}
if (form["comments"].value.length < 10)
{
alert("You forgot to enter any comments.");
return false;
}
return true;
}
//-->
</script>
</head>
Fetch the Entry - We will load this page from a link that is part of its listing in the display page. That link passes the id number of the entry like so:
printf("<div align=\"right\">
<a class=\"edit\" href=\"mgmt/editguest.php?entryid=%s&edit=yes\">--</a>%s %s,",
$myrow["Entryid"], $myrow["Fname"], $myrow["Lname"]);
The key part is the "editguest.php?entryid=%s" and later in the line, "$myrow['Entryid']" is substuted for "%s". This supplies the editguest page with a $entryid variable containing the entry id we can use to fetch entry contents. Now, as before, we get the database ready for querying:
<body>
<div id="maincontent">
<p class="sctn1hd">Edit Logbook:</p>
<?php
$hostName = "localhost";
$databaseName = "guestbook";
$userName = "user";
$passWord = "password";
// function to show any errors//////////////////
function showError()
{
echo("Error " . mysql_errno() . " : " . mysql_error());
} // end error function
if (!($db = @ mysql_connect($hostName, $userName, $passWord)))
showError();
if (!( @ mysql_select_db($databaseName, $db)))
showError();
Let's handle the "delete" function first. This page file should reside in a .htaccess-protected directory, so only you have access to it. Here we check to make sure we have the entryid, and that the form has told us to delete the record. If so, then we submit a MySQL "DELETE" query, specifying this $entryid. Then, we put up a button that uses the JavaScript function "history.go(-2)" function to get us back to the guestbook display, which was 2 pages back.
//==============================================================
// handle "delete" request
// "delete" occurrs immediately upon clicking "Delete" -
// there is no "do you really want to..."
//
if ($entryid && ($delete == "yes"))
{
// delete a record
echo "Deleting entry # $entryid<br />\n";
$sql = "DELETE FROM Messages WHERE Entryid='$entryid'";
if (!($result = @ mysql_query($sql, $db)))
showError();
$delete = "";
echo "Thank you. Entry # <strong>$entryid</strong> was deleted.\n";
// provide 'return' button
echo "<br /><br />\n<center>
<form action='javascript:history.go(-2);'>
<input type='submit' class='nav2' value='Back to Guestbook'></form>
</center><br />\n";
There are only three things this form can do: Delete an entry, change an entry, or "cancel" back to the guestbook. The "Cancel" button does not submit the form, it just links back to the guestbook, and we just handled the "delete" case, so if we submitted, we must be wanting to change the entry.
//==============================================================
// handle "change" request from non-guest users
//
} elseif ($submit) {
// update from form
if ($entryid)
{
$sql = "UPDATE CubGuests
SET Time='$time', Date='$date', Fname='$fname', Lname='$lname',
Email='$email', URL='$url', City='$city', State='$state',
Country='$country', Comments='$comments' WHERE Entryid='$entryid'";
if (!($result = mysql_query($sql)))
{
showerror();
}
echo "Entry # <strong>$entryid</strong> updated.\n";
} else {
echo "<span style='color:red;'>Oops! No entry id given.</span>\n";
}
// provide 'return' button
echo "<br><br>\n<center>
<form action='javascript:history.go(-2);'>
<input type='submit' class='nav2' value='Back to Guestbook'></form>
</center><br>\n";
} else {
MySQL's UPDATE command is the one to use when changing a record that already exits. Note the structure and one-to-one correspondence between the variables and the Message table fields. Note also that we use the time and date the message was originally posted. Again, we do some checking of success/failure status and give the appropriate messages, then a return button.
If we are not submitted for a change, and are not deleting, we use the given $entryid and grab the message from the database. Here's how, using a typical and streamlined method:
// not submitted for changes-
// capture existing data and display it in the form
if ($entryid && $edit="yes") {
$sql = "SELECT * FROM Messages WHERE Entryid='$entryid'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
$time = $myrow["Time"];
$date = $myrow["Date"];
$fname = $myrow["Fname"];
$lname = $myrow["Lname"];
$email = $myrow["Email"];
$url = $myrow["URL"];
$city = $myrow["City"];
$state = $myrow["State"];
$country = $myrow["Country"];
$comments = $myrow["Comments"];
}
?>
We build a query string , asking for all the field contents of the message identfied by $entryid. Then we issue the query and a hash array is built with the keys being the field names, and the values the data from the database. To access the values, just equate a variable with a key, as shown in the stack of equates above. I try to maintain a one-to-one relationship between the table field names and program variables so there's less confusion looking at the code.
Now that we have our variables we can fill in a form by setting the value of a form's input element to one of our captured variable values using the PHP "echo" statement. As before, the form is programed to reload itself by setting the action to $PHP_SELF, after being verifyed by the JavaScript "verify()" function in the head of the page:
<form name="form_one" method="post" action="<?php echo $PHP_SELF?>"
onSubmit="return verify(this);">
<p><strong>Entry Id: <?php echo $entryid ?></p>
<hr>
<table border="0">
<tr>
<td align="right">
<input type="hidden" name="entryid"
value="<?php echo $entryid ?>" />
<strong>Time:</strong></td>
<td>
<input type="text" name="time" size="8" maxlength="8"
value="<?php echo $time ?>" />
</td>
</tr>
<tr>
<td align="right">
<strong>Date:</strong>
</td>
<td>
<input type="text" name="date" size="10" maxlength="10"
value="<?php echo $date ?>" />
</td>
</tr>
<tr>
<td align="right">
<strong>First Name:</strong>
</td>
<td>
<input type="text" name="fname" size="20" maxlength="20"
value="<?php echo $fname ?>" />
</td>
</tr>
<tr>
<td align="right">
<strong>Last Name:</strong>
</td>
<td>
<input type="text" name="lname" size="20" maxlength="20"
value="<?php echo $lname ?>" />
</td>
</tr>
<tr>
<td align="right"><strong>E-Mail:</strong></td>
<td>
<input type="text" name="email" size="40" maxlength="40"
value="<?php echo $email ?>" />
</td>
</tr>
<tr>
<td align="right">URL:</td>
<td>
<input type="text" name="url" size="40" maxlength="80"
value="<?php echo $url ?>" />
</td>
</tr>
<tr>
<td align="right">City:</td>
<td>
<input type="text" name="city" size="30" maxlength="30"
value="<?php echo $city ?>" />,
State: <input type="text" name="state" size="5" maxlength="30"
value="<?php echo $state ?>" />
Country: <input type="text" name="country" size="20" maxlength="20"
value="<?php echo $country ?>" />
</td>
</tr>
<tr>
<td align="right"><strong>Comments:</strong></td>
<td>
<textarea name="comments" cols="50" rows="4"
maxlength="2048"><?php echo $comments ?></textarea>
</td>
</tr>
<tr>
<td align="right">
<hr>
<?php
printf("<a href=\"%s?entryid=%s&delete=yes\">
<strong>Delete </strong><img src=\"images/trshcan.png\"
alt=\"Delete\" width=\"24\" height=\"24\" border=\"0\"
align=\"middle\" /></a> ", $PHP_SELF, $entryid);
?>
</td>
<td align="right"><hr>
<input name="submit" type="submit" value="Change" class="nav2" />
<input type="button" value="Cancel" class="nav2"
onClick='javascript:history.go(-1);' class="nav2" />
<br />
</td>
</tr>
</table>
</form>
<p class="auth">Script and Guestbook Created by:
<a href="http://digital.watersgulch.com">Rik Nilsson</a>.</span>
<?php
} // end display form
?>
</div>
</body>
</html>
I have shown all the code to build a PHP/MySQL-enabled guestbook. Just assemble the snippets in the right order in the right pages and it should work as advertised. The directory structure assumes that all the guestbook pages exist in a subfolder of your site called "guestbook".
If you become a victim of web-bots and see attempts to spam your guests, you may have to protect your "guestbook" directory using tools on your host.
Click to download a working set of all pages/scripts.
go to Page 1 >>.
Copyright 2003 Richard Nilsson. Verbatim copying and redistribution of this entire article are permitted without royalty in any medium provided this notice is preserved.
|