Practical PHP and MySQL Web Site Databases: A Simplified Approach (2013)

Chapter 7. Migrating to a Host and Backing Up Your Web Site Database

You might think that the database-driven web site created in Chapter 6 is finished and ready for migrating to the host. However, in a real-world situation the client has had time to think about the site and will ask for some last-minute changes. A web designer/developer cannot anticipate everything that a client might expect to achieve with a database driven web site. Also, last-minute changes might be necessary when new laws require the database to exclude certain information or to include additional information.

This chapter contains the following sections:

· Making last-minute changes

· Include members’ titles in the paginated display

· Allow members to update their own details

· Provide for safe e-mailing

· Create a secure feedback form

· Migrating the database to a remote host

· Backing up your database

Making Last-Minute Changes

In our example, we assume that the client asked for some last-minute changes as follows:

· In the database in Chapter 6, the membership secretary noticed that, although the title column was present in the administrator’s individual record display, the title was absent from the paginated table display. We will correct this omission.

· To reduce the workload of the membership secretary, he asked us to allow registered members to update their own details. For instance, telephone numbers and e-mail addresses can change. If a woman is recorded as “Miss,” her title and last name might need changing when she gets married. We will therefore allow members to update their titles, last names, addresses, telephone numbers and e-mail addresses.

· The client asked for a live but secure e-mail address to be displayed on the web site. This will allow users to contact the organization and the webmaster.

· The client asked for an inquiry form so that users can ask for more information about the organization before registering as members.

We will begin by creating a new database and table.

Create a New Database

The last-minute changes were requested for the database that we created in the last chapter, but to avoid confusion we will implement the changes in a new version of the database called lmnmigrate, short for Last Minute and Migrate. (The first letter of the name is a lowercase L, not an uppercase I.) In the htdocs folder within the xampp folder, create a new folder called lmnmigrate.

Download the files for Chapter 7 from the book’s page at www.apress.com, and load them into the new lmnmigrate folder:

1. Use a browser to access phpMyAdmin, click the Databases tab, and create a new database called lmnmigrate.

2. Scroll down and find the newly created database, and select the box next to it.

3. Click Check Privileges and then click Add User. Add a new user and password as follows:

1. User name: trevithick

2. Host: select Local,

3. Password: locomotive

4. Scroll down to the item named Global Privileges, and click Check All.

Scroll to the bottom, and click Add User (or Go in some versions)to save the database user’s details.

Details of the Downloaded File for Connecting to the Database

The file mysqli_connect.php is already created for you in the download files for this chapter.

<?php
// Connect to the lmnmigrate database and set the encoding to utf-8
DEFINE ('DB_USER', 'trevithick');
DEFINE ('DB_PASSWORD', 'locomotive');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'lmnmigrate');
// Make the connection
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die
('Could not connect to MySQL: ' . mysqli_connect_error() );
// Set the encoding
mysqli_set_charset($dbcon, 'utf8');

We now need to create a table named users with 17 columns.

I already compiled the table for you and exported it into a file called users.sql. You will find this among the downloaded files you just installed in the folder lmnmigrate.

In phpMyAdmin, open the database lmnmigrate.

In the home page of phpMyAdmin, click the database lmnmigrate. Then use the import tab to import the file users.sql you downloaded and installed in htdocs.

To locate the users.sql file in the Import interface, click the Browse button shown circled in Figure 7-1.

Figure 7-1. The Import interface in phpMyAdmin

The Browse button will allow you to navigate to the downloaded file users.sql. Open the file and then click the Go button. The file will be imported. If you now open the users table in phpMyAdmin, you will be able to view the table and its data by clicking, in turn, the Structure tab and the Browse tab.

Note The PHP files in this chapter make no attempt to accommodate styling for Internet Explorer 8; for this a ­conditional style sheet would be required. The files work in all the most popular browsers, but make sure your copy of Mozilla FireFox is the latest version; otherwise, you will not be able to access the administration pages with FireFox. In fact, FireFox is rather picky, and unlike the other browsers, it will detect the tiniest error in the PHP code and throw the user back to the login page.

Displaying the Members’ Titles in the Paginated Table

In Chapter 6, the members’ titles were included in some screen displays but not in the paginated table display. The membership secretary has requested that members’ titles also be displayed in the paginated table display.

This is easily achieved by adding one extra column in the file php view-members.php and saving the file as admin_view_users_title.php. The Title column is added in front of the Last name column. The result is shown in Figure 7-2.

Figure 7-2. The members’ titles are now visible in the paginated table

The records are displayed in descending order of the registration date so that the administrator can quickly view the latest registrations. This helps him to update the Paid field when PayPal notifies him that a registrant has paid the membership fee.

To achieve the change, the file view-members.php has been amended and renamed as admin_view_users_title.php. Listing 7-1 gives a snippet of the code.

The file named admin_view_users.php was modified by inserting the items shown in bold type in the following snippet. The file was saved as admin_view_users_title.php.

Partial Listing 7-1. Adding the Title to the Members’ Paginated Table View (admin_view_users_title.php)

// Make the query
$q = "SELECT title, lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y')
AS regdat, class, paid, user_id FROM users ORDER BY registration_date DESC
LIMIT $start, $pagerows";
$result = @mysqli_query ($dbcon, $q); // Run the query
$members = mysqli_num_rows($result);
if ($result) { // If the query ran OK, display the records
// Table headings
echo '<table>
<tr><td><b>Edit</b></td>
<td><b>Delete</b></td>
<td><b>Title</b></td>
<td><b>Last Name</b></td>
<td><b>First Name</b></td>
<td><b>Email</b></td>
<td><b>Date Registered</b></td>
<td><b>Class</b></td>
<td><b>Paid</b></td>
</tr>';
// Fetch and display all the records
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td><a href="edit_record.php?id=' . $row['user_id'] . '">Edit</a></td>
<td><a href="delete_record.php?id=' . $row['user_id'] . '">Delete</a></td>
<td>' . $row['title'] . '</td>
<td>' . $row['lname'] . '</td>
<td>' . $row['fname'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $row['regdat'] . '</td>
<td>' . $row['class'] . '</td>
<td>' . $row['paid'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If the query did not run OK
// Error message

No changes were needed to the rest of the file.

For you to be able to access the amended file, the menu button link in the file header_admin.php has been changed from admin_view_users.php to admin_view_users_title.php.

Caution Remember to log in as administrator to view the table of members.

The login details are e-mail:jsmith@outcook.com and password: dogsbody.

The membership secretary has asked for a change so that members can update their own records to a limited extent. This is a common feature in database-driven web sites. For instance, in PayPal, members can update their profile (their own account details). A provision was already made for members to change their password; therefore, this feature will not be included again in the members’ update interface that follows.

Allow Members to Update Their Own Records

When members have logged in, they can be permitted to update their own accounts. They have no access to any other member’s account; this is controlled by means of a session and a LIMIT that allows only one record to be displayed by the member. The member’s amendments are validated and sanitized. If members want to change a password, they must use the New Password button on the header menu. The items that can be amended are limited for security reasons, and they are as follows: title, first name, last name, e-mail address, postal address, and telephone number.

Tip The membership secretary might ask the member to send an e-mail notifying the secretary that an amendment has been made. If so, this must be stipulated clearly on the edit page. The member should be asked to use the Contact Us button on the menu. This will open the feedback form described later in this chapter.

The first step consists of changing the header for the “members’ only” page so that registered members can view and amend their records. The header shown in Figure 7-3 is modified to allow this.

Figure 7-3. The new “Your Account” button in the header for the members’ page

The code for the revised header is shown in Listing 7-2. This is in the includes folder in the download code.

Listing 7-2. Creating the New Header for the Members’ Page (header_members_account.php)

<div id="header-members">
<h1>This is the header</h1>
<div id="reg-navigation">
<ul>
<li><a href="logout.php">Logout</a></li>
<li><a href="edit_your_account.php">Your Account</a></li>
<li><a href="register-password.php">New Password</a></li>
</ul>
</div>
</div>

When the Your Account button is clicked, a new page is displayed as shown in Figure 7-4.

Figure 7-4. This screen shot shows the data that a registered member is allowed to update

The importance of logging out is stressed on the members’ page and on the update screens. When fields are edited and the Edit button is clicked, the confirmation message appears as shown in Figure 7-4a.

Figure 7-4a. The update is confirmed

The code for the screens shown in Figures 7-4 and 7-4a is given in Listing 7-3 as follows:

Listing 7-3. Creating an Interface for Members to Edit Their Accounts (edit_your_account.php)

The member must log in to access this page; therefore, it begins with session statements.

<?php
session_start(); #1
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 0))
{
header("Location: login.php");
exit();
}
if (isset($_SESSION['user_id'])){
$_POST['id'] = ($_SESSION['user_id']);
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Edit your account</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; }
label {margin-bottom:5px; margin-top:5px; }
input.fl-left { float:left; margin-bottom:5px; margin-top:5px; padding-bottom:0; }
/*#submit { margin-left:-660px; margin-top:260px; margin-bottom:10px; }*/
#submit { position:absolute; top:595px; left:330px; }
.red { color:red; }
footer { position:absolute; top:600px; left:370px;;}
</style>
</head>
<body>
<div id="container">
<?php include("includes/header_members_account.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the "edit your account" content-->
<h2>Edit Your Account</h2>
<h3 class="red">For your own security please remember to logout</h3>
<?php
// When the you're your-Account button is clicked the editing interface appears
// Look for a valid user id, either through GET or POST
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {
$id = $_POST['id'];
} else { // No valid ID, kill the script
echo '<p class="error">This page has been accessed in error.</p>';
include ('includes/footer.php');
exit();
}
require ('mysqli_connect.php');
// Has the form been submitted?
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$errors = array();
// Trim the title #2
$tle = trim($_POST['title']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($tle));
// Get string lengths
$strLen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your title.';
}else{
$title = $stripped;
}
// Trim the first name
$name = trim($_POST['fname']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($name));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strLen < 1 ) {
$errors[] = 'You forgot to enter your first name.';
}else{
$fn = $stripped;
}
// Trim the last name
$lnme = trim($_POST['lname']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($lnme));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your last name.';
}else{
$ln = $stripped;
}
//Set the email variable to FALSE #3
$e = FALSE;
// Check that an email address has been entered
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';
}
//remove spaces from beginning and end of the email address and validate it
if (filter_var((trim($_POST['email'])), FILTER_VALIDATE_EMAIL)) {
//A valid email address is then registered
$e = mysqli_real_escape_string($dbcon, (trim($_POST['email'])));
}else{
$errors[] = 'Your email is not in the correct format.';
}
// Trim the first address
$add1 = trim($_POST['addr1']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($add1));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your address.';
}else{
$addr1 = $stripped;
}
// Trim the second address
$addr2 = trim($_POST['addr2']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($addr2));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strLen < 1 ) {
$ad2=NULL;
}else{
$addr2 = $stripped;
}
// Trim the city
$ct = trim($_POST['city']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($ct));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your city.';
}else{
$city = $stripped;
}
// Trim the county
$conty = trim($_POST['county']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($conty));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your county.';
}else{
$county = $stripped;
}
// Trim the post code
$pcod = trim($_POST['pcode']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($pcod));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your county.';
}else{
$pcode = $stripped;
}
// Has a phone number been entered? #4
if (empty($_POST['phone'])){
$ph=($_POST['phone']);
}
elseif (!empty($_POST['phone'])) {
//Remove spaces, hyphens, letters and brackets
$phone = preg_replace('/\D+/', '', ($_POST['phone']));
$ph=$phone;
}
if (empty($errors)) { // If everything's OK
// make the query
$q = "SELECT user_id FROM users WHERE lname='$ln' AND user_id != $id";
$result = @mysqli_query($dbcon, $q);
if (mysqli_num_rows($result) == 0) {
// Make the update query #5
$q = "UPDATE users SET title='$title', fname='$fn', lname='$ln', email='$e',
addr1='$addr1', addr2='$addr2', city='$city', county='$county', pcode='$pcode',
phone='$phone' WHERE user_id=$id LIMIT 1";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_affected_rows($dbcon) == 1) { // If the query was successful
// Echo a message if the edit was satisfactory
echo '<h3>Your Account has Been Updated.</h3>';
} else { // Echo a message if the query failed
echo '<p class="error">The user could not be edited due to a system error.
We apologize for any inconvenience.</p>'; // Message
echo '<p>' . mysqli_error($dbcon) . '<br />Query: ' . $q . '</p>'; // Debugging message
}
}
} else { // Display the errors
echo '<p class="error">The following error(s) occurred:<br>';
foreach ($errors as $msg) { // Echo each error
echo " - $msg<br/>\n";
}
echo '</p><p>Please try again.</p>';
} // End of the if (empty($errors))section
} // End of the conditionals
// Select the member's information #6
$q = "SELECT title, fname, lname, email, addr1, addr2, city, county, pcode, phone
FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_num_rows($result) == 1) { // If the user id is valid, display the form
// Get the member's information
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Create the form #7
echo '<form action="edit_your_account.php" method="post">
<p><label class="label" for="title">Title:</label>
<input class="fl-left" id="title" type="text" name="title" size="30" maxlength="30"
value="' .$row[0]. '"></p>
<p><label class="label" for="fname">First Name:</label>
<input class="fl-left" id="fname" type="text" name="fname" size="30" maxlength="30"
value="' .$row[1]. '"></p>
<p><label class="label" for="lname">Last Name:</label>
<input class="fl-left" id="lname" type="text" name="lname" size="30" maxlength="40"
value="' .$row[2]. '"></p>
<p><label class="label" for="email">Email:</label>
<input class="fl-left" id="email" type="text" name="email" size="30" maxlength="50"
value="' .$row[3]. '"></p>
<p><label class="label" for="addr1">Address:</label>
<input class="fl-left" id="addr1" type="text" name="addr1" size="30" maxlength="50"
value="' .$row[4]. '"></p>
<p><label class="label" for="addr2">Address:</label>
<input class="fl-left" id="addr2"type="text" name="addr2" size="30" maxlength="50"
value="' .$row[5]. '"></p>
<p><label class="label" for="city">City:</label>
<input class="fl-left" id="city" type="text" name="city" size="30" maxlength="30"
value="' .$row[6]. '"></p>
<p><label class="label" for="county">County:</label>
<input class="fl-left" id="county"type="text" name="county" size="30" maxlength="30"
value="' .$row[7]. '"></p>
<p><label class="label" for="pcode">Post Code:</label>
<input class="fl-left" id="pcode"type="text" name="pcode" size="15" maxlength="15"
value="' .$row[8]. '"></p>
<p><label class="label" for="phone">Phone:</label>
<input class="fl-left" id="phone" type="text" name="phone" size="15" maxlength="15"
value="' .$row[9]. '"></p>
<br><br><p><input id="submit" type="submit" name="submit" value="Edit"></p><br>
<input type="hidden" name="id" value="' . $id . '">
</form>';
} else { // The user could not be validated
...echo '<p class="error">This page has been accessed in error.</p>';
}
mysqli_close($dbcon);
include ('includes/footer.php');
?>
</div>
</div>
</body>
</html>

Explanation of the code

session_start(); #1
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 0))
{
header("Location: login.php");
exit();
}
if (isset($_SESSION['user_id'])){
$_POST['id'] = ($_SESSION['user_id']);
}

To ensure that no ordinary registered member can view and edit the member’s account, a session is started that contains the logged-in member’s details.

// Trim the title #2
$tle = trim($_POST['title']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($tle));
// Get string lengths
$strLen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your title.';
}else{
$title = $stripped;
}

We saw this code before in the safer registration page in Chapter 6. It prevents HTML tags and JavaScript from being inserted into the database table. This sanitizing format is applied many times in this listing. This test applies only if the user has altered the title in an unacceptable manner, or the user has cleared the field and then forgotten to enter a new title.

//Set the email variable to FALSE #3
$e = FALSE;
// Check that an email address has been entered
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';
}
//remove spaces from beginning and end of the email address and validate it
if (filter_var((trim($_POST['email'])), FILTER_VALIDATE_EMAIL)) {
//A valid email address is then registered
$e = mysqli_real_escape_string($dbcon, (trim($_POST['email'])));
}else{
$errors[] = 'Your email is not in the correct format.';
}

This method of validating an email address was demonstrated in Chapter 6 and is repeated here.

// Has a phone number been entered? #4
if (empty($_POST['phone'])){
$ph=($_POST['phone']);
}
elseif (!empty($_POST['phone'])) {
//Remove spaces, hyphens, letters and brackets
$phone = preg_replace('/\D+/', '', ($_POST['phone']));
$ph=$phone;
}

This method of sanitizing and formatting a phone number was explained in Chapter 6 and is repeated here.

// Make the update query: #5
$q = "UPDATE users SET title='$title', fname='$fn', lname='$ln', email='$e',
addr1='$addr1', addr2='$addr2', city='$city', county='$county', pcode='$pcode',
phone='$phone' WHERE user_id=$id LIMIT 1";

There are 10 fields in the form; therefore, there must be 10 items in the update query.

// Select the member's information #6
$q = "SELECT title, fname, lname, email, addr1, addr2, city, county, pcode, phone
FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_num_rows($result) == 1) { // If the user id is valid, display the form
// Get the member's information
$row = mysqli_fetch_array ($result, MYSQLI_NUM);

The 10 column names in the member’s selected information must exactly match the 10 names in the code block beginning at line #4.

// Create the form #7
echo '<form action="edit_your_account.php" method="post">
<p><label class="label" for="title">Title:</label>
<input class="fl-left" id="title" type="text" name="title" size="30" maxlength="30"
value="' .$row[0]. '"></p>
<p><label class="label" for="fname">First Name:</label>
<input class="fl-left" id="fname" type="text" name="fname" size="30" maxlength="30"
value="' .$row[1]. '"></p>
<p><label class="label" for="lname">Last Name:</label>
<input class="fl-left" id="lname" type="text" name="lname" size="30" maxlength="40"
value="' .$row[2]. '"></p>

This shows part of the sticky form for editing the member’s account.

Caution Carefully check the array items shown bold in the block of code beginning with reference line #7 in the ­preceding code. They must be in numerical order. In this table display, they are numbered from 0 to 9 because there are 10 fields. When inserting an extra field or deleting a field, it is only too easy to forget to renumber the array items in square brackets. This would result in either the same data appearing in two adjacent fields or inappropriate data ­appearing in some fields.

That concludes the tutorial on the database lmnmigrate. Some useful additions to a database-driven web site (or any web site) will now be described.

To enable users to contact the web site’s owner, the client has asked for a live e-mail address to be included in one of the web site pages. This could be a magnet for spammers. The webmaster should encode the live e-mail address so that spam spiders cannot harvest it.

Safe E-mailing

This useful item is for information only. A live but secure e-mail link is not included in any page of this chapter’s example web site. An influx of spam will cause distress to the site owner, and this breach of privacy can sow seeds of doubt about the security of the rest of the web site, especially the security of the database. This also damages the reputation of the web designer. This section describes a method for preventing spam.

Escrambler is a free, anti-spam device. It was originally produced as a plain, clickable e-mail link, and it was later enhanced by the addition of an image of the e-mail address by InnerPeace.org. Their web page is at http://innerpeace.org/escrambler.shtml. The web site will quickly generate the code for you, or you can adapt the JavaScript snippet that follows.

The JavaScript code allows for two conditions:

· The full e-mail address appears on the screen in the form of an image that is clickable but invisible to spam spiders.

· If no image is available, a link having the words E-mail Me appears on the web page.

Clicking either the image or the words E-mail Me causes the user’s default e-mail program to open with the e-mail address already filled in. Users might need to be told that the e-mail might go into their Outbox and, therefore, the user must send it by clicking the Send/Receive button.

I usually create the image of the e-mail address in MS Paint, and then I import it into my image-manipulation program, crop it, and export it as a transparent .gif or .png file.

Insert the following JavaScript snippet into the <body> section where you want the e-mail address to appear. The JavaScript code also places a picture of the e-mail address on the page so that the user can click it or make note of it for future use. For your own e-mail scrambler, just change the items in bold type in the following example, which hides the address fredbloggs@aol.com:

<p>
<script type="text/javascript">
<!--
function escramble(){
var a,b,c,d,e,f,g,h,i
a='<a href=\"mai'
b='fredbloggs'
c='\">'
a+='lto:'
b+='@'
b+='aol.com'
e='</a>'
f=''
g='<img src=\"'
h='images/bloggs-email.jpg'
i='\" alt="Email Me" border="0">'
if(f) d=f
else if(h) d=g+h+i
else d=b
document.write(a+b+c+d+e)
}
escramble()
//-->
</script></p>

The JavaScript breaks down the traditional email address into little bits and then reassembles it. Spam spiders have great difficulty reading JavaScript and images; even if they could read JavaScript, the email address is so fragmented that spam spiders would not make sense of it.

Caution The variable f is followed by two single quotes, not a double quote. The line g=‘<img src=\”’ ends in a double quote followed by a single quote.

A Minor Problem

Your client might decide not to put her e-mail address on the web site. She might prefer to use a feedback form because it is a more secure approach. However, there is nothing to prevent someone from ignoring the form and contacting her directly by guessing her e-mail address. This very rarely happens, and the sender is not necessarily a mass spammer. This is known as link exchange spam, so tell your client not to be alarmed. I have personally received only a handful of these e-mails in a decade. This is a minor problem and can be ignored.

So how does the sender know your client’s e-mail address? He knows because he found her web site. Why does he try to contact her? He wants to establish a reciprocal link from her web site to his web site. Because the secure feedback form (described in the next section) is designed to prevent the sender from entering URLs, he must use a normal e-mail; therefore, he makes an intelligent guess.

He tries info@clientswebsite.co.uk or sales@clientswebsite.co.uk, and so on. Link exchange spam can be the result of viewing the web site’s e-mail address posted in Whois, a directory of web-site owners. If your client has a catchall e-mail address, the person could also type something like postmaster@clientswebsite.co.uk and it might reach your client. His e-mail would then include his URL. He normally won’t include a dodgy URL because he could be traced through his e-mail. By requesting and establishing a reciprocal link, he is hoping to improve his search engine ranking by having as many external links as possible that link to his site. Your client should not click a link in the e-mail to look at the person’s web site(s); if she did take the risk, she would usually find that his content is in no way related to her own web site, so she should not agree to a reciprocal link. Also, she will most likely find that his link page has between 50 and 150 links on it. If she agrees to a reciprocal link, search engines will assume she is using a link farm and, as a consequence, they might penalize her web site.

If your client is receiving this type of e-mail, she can avoid it by using a different e-mail address in the feedback form handler. She could use her home address, clientsemailaddress@herisp.co.uk. No one will be able to guess her home e-mail address by looking at her web site, but the downside is that her business replies won’t be distinguished from her personal e-mails.

A Secure Feedback Form

A Contact Us form is not strictly related to databases; however, it is an interactive element and provides an excellent application of PHP code.

E-mails and feedback forms are the most popular contact methods for allowing users to communicate easily with web-site owners. Unfortunately, both contact methods can be abused. E-mail addresses are harvested and sold to spammers. Both actions cause distress and an influx of spam to the site owner, and they don’t enhance the reputation of the web designer. Hackers can hijack a form and use it to send frequent nonsense messages to the web-site owner. These usually contain malware that can be activated via a link in the text area. This section describes some options to prevent these risks.

When designing feedback forms, we need to consider the following three points:

· Because layout tables are deprecated, CSS must be used to align the input fields neatly.

· Blind and severely visually-impaired users can use screen readers to read and reply to forms. Accessibility rules must be observed

· Filters must be built into the form handler to prevent the form from being hijacked for nefarious purposes.

Bogus Replies

Concerning the third point, when I first began to design web sites, I added feedback forms to many of my clients’ web sites and all of them were plagued by people using robots to send bogus replies to the site owners. The replies contained gobbledygook and dodgy URLs. These e-mails were often sent once or twice a week. The site owners were naturally disappointed and puzzled because they thought they had received an order. Fortunately, they did not click a link on these bogus replies, but they contacted me immediately for a solution. What do bogus replies look like?

A typical bogus reply is shown next. For your security, I altered the URLs and the e-mail address. The replies contain gobbledygook as well as live URL links that lead to dodgy web sites. A web-site owner unfamiliar with the weird behavior of hackers might be tempted to click a link, which could lead to all sorts of mayhem. Note the large number of live URLs:

Content-type: text/html;charset=“iso-8859-1”

From: ezrxsk@xyzvjox.com

X-Antivirus: AVG for Email 8.0.100 [270.4.1/1510]

From: Damon Rosario

Telephone No: 24315931045

Mobile phone: 25803805787

Address of sender: pkPb80 <a href=\"http://lpwqwncrqwel.com/\">lpwqwncrqwel</a>,

superobligation hobbledehoyish foreread minaway wips taenioid chancellorism unsocket

3Sy6Rl <a href=\"http://qmuxlytwkukt.com/\">qmuclytwkuxt</a>,

[url=http://iqdqouydsqzn.com/]iqdqouydsqzn[/url],

[link=http://lvcukrrfrlpj.com/]lvkuckrfrlsj[/link],http://uiaopyzucuiyba.com/

<a href=http://www.axtemplate.com/>Axtemplate.com</a>

http://www.moley.co.jp/

The robot is designed to send URLs; therefore, the way to kill the replies—and also cause the robot to have hysterics and crash—is to put a URL filter in the form handler.

What Does a Genuine Reply Look Like?

The following is a genuine e-mail received from a secure form and its handler:

This message was sent from:

http://www.my-website.co.uk/feedbackform.html

------------------------------------------------------------

Name of sender: Andrew Eastman

Email of sender: aeastman@myisp.co.uk

Telephone No: 01111 222333

XP?: No

Vista?: No

Windows7?: No

Windows 8: Yes

Laptop?: Yes

Desktop?; No

------------------------- MESSAGE ----------------------------

How can I change back to Windows 7?

-------------------------------------------------------------------

The Feedback Form

Figure 7-5 shows a typical feedback form.

Figure 7-5. The feedback form. In this screenshot, the header and footer are cropped away to save space

The code for the feedback page concentrates on preventing URLs from being entered because these are the main concern with forms filled in by robots.

The formatting of a form can be rather time consuming, and you will need a conditional style sheet for Internet Explorer 8. The Listing 7-4a that follows contains an additional link to a style sheet for the feedback form and also a link to a conditional style sheet for Internet Explorer 8. Conditional style sheets must always be listed after the other style sheets. The code [if lt IE 9] means “if less than Internet Explorer 9.” Note the space between IE and 9.

Listing 7-4a. Creating the “Contact Us” Form (feedback-form.php)

<!doctype html>
<html lang=en>
<head>
<title>The feedback form</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<link rel="stylesheet" type="text/css" href="feedback_form.css">
<!--[if lt IE 9]>
<link rel="stylesheet" type="text/css" href="feedback-ie8.css">
<![endif]-->
</head>
<body>
<div id='container'>
<div id='content'><!--Start of feedback form content.-->
<div id="form">
<form action="feedback_handler.php" method="post" ><h2>Contact Us</h2>
<div class="cntr">
<strong>Address:</strong> 1 The Street, Townsville, AA6 8PF, <b>Tel:</b> 01111 800777</div>
<p class="cntr"><strong>To email us:</strong> please use this form and click the Send button at the bottom.</p>
<p class="cntr"><span class="small">Make a note of our email address:</span>
<img alt="Email Address" title="Email Address" src="images/fredbloggs.gif"></p>
<h3 >Essential items are marked with an asterisk</h3>
<!--START OF TEXT FIELDS-->
<label for="username" class="label"><span class="red">*</span>Your Name: </label>
<input id="username" name="username" size="30">
<label for="useremail" class="label"><span class="red">*</span>Your Email:</label>
<input id="useremail" name="useremail" size="30">
<label for="phone" class="label"><span class="red">*</span>Telephone:</label>
<input id="phone" name="phone" size="30">
<div class="chk1">
<input id="chkbox1" name="brochure" value="Yes" type="checkbox">
<label for =”chkbox1> Please send me a brochure (Tick box)</label>
</div>
<h3>Please enter your address if you ticked the box<span class="star">*</span></h3>
<div>
<label for="addrs1" class="label"> Address: </label>
<input id="addrs1" name="addrs1" size="30">
<label for="addrs2" class="label"> Address: </label>
<input id="addrs2" name="addrs2" size="30">
<label for="city" class="label"> Town/city: </label>
<input id="city" name="city" size="30">
<label for="postcode" class="label"> Post code:</label>
<input id="postcode" name="postcode" size="30"></div>
<h3>Would you like to receive emailed newsletters?</h3>
<div id="rad">
<input checked="checked" id="radio1" name="letter" type="radio" value="yes">
<label for="radio1">Yes</label>
<input id="radio2" name="letter" value="no" type="radio">
<label for="radio2">No </label>
</div><br><br>
<h3><span class="red">*</span>Please enter your message below</h3>
<textarea id="comment" name="comment" rows="12" cols="40"></textarea><br>
<!--THE SUBMIT BUTTON GOES HERE-->
<input id="sb" value="Send" title="Send" alt="Send" type="submit"><br>
</form>
</div><!--End of the feedback form content.-->
<div id="footer">
<?php include('includes/footer.php'); ?>
</div></div></div><br>
</body>
</html>

Bogus feedback from the feedback form can be stopped by means of a PHP handler file. If a URL has been entered into any text field, the form handler causes an error message to appear when the submit button is clicked. This stops bogus replies completely. Hackers do not hijack a form to send advertisements for Viagra; they are hoping the site owner will click the URL link and fire up the sender’s dodgy web site(s). The web sites usually contain malware such as a Trojan horse that would take control of the site owner’s computer. The PHP code for the handler is shown inListing 7-4b.

Listing 7-4b. Creating the Feedback Form Handler (feedback_handler.php)

Note that dummy e-mail addresses and URLs have been used. Replace them with your client’s details.

<?php
/* Feedback form handler*/
// set to the email address of the recipient
$mailto = "clientsemailaddress@clients-isp.com" ; #1
//$mailto = "webmaster@someisp.com" ;
$subject = "Message from my website" ;
// list the pages to be displayed
$formurl = "http://www.clients-isp.com/feedback_form.html" ;
$errorurl = "http://www.clients-isp.com/error.html" ;
$thankyouurl = "http://www.clients-isp.com/thankyou.html" ;
$emailerrurl = "http://www.clients-isp.com/emailerr.html" ;
$errorcommentturl = "http://www.clients-isp.com/commenterror.html" ;
$uself = 0;
// Set the information received from the form as short variables #2
$headersep = (!isset( $uself ) || ($uself == 0)) ? "\r\n" : "\n" ;
$username = $_POST['username'] ;
$useremail = $_POST['useremail'] ;
$phone = $_POST['phone'];
$brochure = $_POST['brochure'];
$addrs1 = $_POST['addrs1'];
$addrs2=$_POST['addrs2'];
$city=$_POST['city'];
$postcode = $_POST['postcode'] ;
$letter=$_POST['letter'];
$comment = $_POST['comment'] ;
$http_referrer = getenv( "HTTP_REFERER" );
//Check that all four essential fields are filled out
if (empty($username) || empty($useremail) || empty($phone)|| empty($comment)) {
header( "Location: $errorurl" );
exit ; }
//check that no URLs have been inserted in the username text field #3
if (strpos ($username, '://')||strpos($username, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }

if (preg_match( "[\r\n]", $username ) || preg_match( "[\r\n]", $useremail )) {
header( "Location: $errorurl" );
exit ; }
#remove any spaces from beginning and end of email address #4
$useremail = trim($useremail);
#Check for permitted email address patterns
$_name = "/^[-!#$%&\'*+\\.\/0-9=?A-Z^_`{|}∼]+";
$_host = "([-0-9A-Z]+\.)+";
$_tlds = "([0-9A-Z]){2,4}$/i";
if(!preg_match($_name."@".$_host.$_tlds,$useremail)) {
header( "Location: $emailerrurl" );
exit ; }
// Has a phone number been entered?
if (!empty($_POST['phone'])) {
//Remove spaces, hyphens, letters and brackets.
$phone = preg_replace('/\D+/', '', ($_POST['phone']));
}
//Has the brochure box been checked? #5
if(!$brochure) {$brochure = "No";}
//check that no URLs have been inserted in the addrs1 text field
if (strpos ($addrs1, '://')||strpos($addrs1, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }
//Check whether URLs have been inserted in the addrs2 text field
if (strpos ($addrs2, '://')||strpos($addrs2, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }
//Check whether URLs have been inserted in the city text field
if (strpos ($city, '://')||strpos($city, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }
//Check whether URLs have been inserted in the pcode text field
if (strpos ($pcode, '://')||strpos($pcode, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }
//Check whether URLs have been inserted in the comment text area
if (strpos ($comment, '://')||strpos($comment, 'www') !==false){
header( "Location: $errorcommenturl" );
exit ; }
if($letter !=null) {$letter = $letter;}
$messageproper = #6
"This message was sent from:\n" .
"$http_referrer\n" .
"------------------------------------------------------------\n" .
"Name of sender: $username\n" .
"Email of sender: $useremail\n" .
"Telephone: $phone\n" .
"brochure?: $brochure\n" .
"Address: $addrs1\n" .
"Address: $addrs2\n" .
"City: $city\n" .
"Postcode: $postcode\n" .
"Newsletter:$letter\n" .
"------------------------- MESSAGE -------------------------\n\n" .
$comment .
"\n\n------------------------------------------------------------\n" ;
mail($mailto, $subject, $messageproper, "From: \"$username\" <$useremail>" );
header( "Location: $thankyouurl" );
exit ;
?>

Caution Because the XAMPP package may not contain an e-mail client such as Mercury, to test the form and its handler, the form and its associated files MUST be uploaded and tested on a remote host.

Explanation of the Code

Note that URLs cannot be inserted into the check box or the radio button fields. URLs cannot be inserted into the phone field because it only allows numbers.

$mailto = "clientsemailaddress@clients-isp.com" ; #1
//$mailto = "webmaster@someisp.com" ;
$subject = "Message from my website" ;
// list the pages to be displayed
$formurl = "http://www.clients-isp.com/feedback_form.html" ;
$errorurl = "http://www.clients-isp.com/error.html" ;
$thankyouurl = "http://www.clients-isp.com/thankyou.html" ;
$emailerrurl = "http://www.clients-isp.com/emailerr.html" ;
$errorphoneurl = "http://www.clients-isp.com/phonerror.html" ;
$errorcommentturl = "http://www.clients-isp.com/commenterror.html" ;

The dummy email addresses and URLs will need to be replaced by real-world addresses and URLs. To test the feedback form, the webmaster will comment-out the client’s email address and remove the comment symbols from his own email address. When he is satisfied that the form works, he will reverse this process so that the client receives the feedback. In the last five lines, five files are assigned to short form variables. The last four lines refer to pages containing error messages.

// Set the information received from the form as short variables #2
$headersep = (!isset( $uself ) || ($uself == 0)) ? "\r\n" : "\n" ;
$username = $_POST['username'] ;
$useremail = $_POST['useremail'] ;
$phone = $_POST['phone'];
$brochure = $_POST['brochure'];
$addrs1 = $_POST['addrs1'];
$addrs2=$_POST['addrs2'];
$city=$_POST['city'];
$postcode = $_POST['postcode'] ;
$letter=$_POST['letter'];
$comment = $_POST['comment'] ;

The long variables are assigned to short variables to make the form easier to read and debug.

//check that no URLs have been inserted in the username text field #3
if (strpos ($username, '://')||strpos($username, 'www') !==false){
header( "Location: $errorsuggesturl" );
exit ; }

The PHP function strpos() checks whether the URL symbols // and www are present within the string input by the user. The function actually finds the position of the first occurrence of a string within a string. If URL symbols are present, a page containing an error message is displayed. The function is used to check most of the fields in the form.

#remove any spaces from beginning and end of email address #4
$useremail = trim($useremail);
#Check for permitted email address patterns
$_name = "/^[-!#$%&\'*+\\.\/0-9=?A-Z^_`{|}∼]+";
$_host = "([-0-9A-Z]+\.)+";
$_tlds = "([0-9A-Z]){2,4}$/i";
if(!preg_match($_name."@".$_host.$_tlds,$useremail)) {
header( "Location: $emailerrurl" );
exit ; }

This code uses a regular expression to validate the e-mail address, if the e-mail address does not validate, a page containing an error message is displayed.

//Has the brochure box been checked? #5
if(!$brochure) {$brochure = "No";}

If the brochure check box has not been selected, the word No is assigned to the variable $brochure; otherwise, the word Yes will be assigned by default.

$messageproper = #6
"This message was sent from:\n" .
"$http_referrer\n" .
"------------------------------------------------------------\n" .
"Name of sender: $username\n" .
"Email of sender: $useremail\n" .
"Telephone: $phone\n" .
"brochure?: $brochure\n" .
"Address: $addrs1\n" .
"Address: $addrs2\n" .
"City: $city\n" .
"Postcode: $postcode\n" .
"Newsletter:$letter\n" .
"------------------------- MESSAGE -------------------------\n\n" .
$comment .
"\n\n------------------------------------------------------------\n" ;
mail($mailto, $subject, $messageproper, "From: \"$username\" <$useremail>" );
header( "Location: $thankyouurl" );

This block of code constructs the e-mail using the variables provided in the feedback form by the user. Note the full stops after each item; these are very important. The code \n inserts a line break (or Enter) between each item. The last two lines send the e-mail using the PHP functionmail().

The Style Sheets for the Feedback Form

Two style sheets are provided, one is a conditional to ensure that the fields format properly with Internet Explorer 8.

Listing 7-4c. Creating the Style Sheet for the Feedback Form (feedback_form.css)

body {margin:0; padding:0; }
form {position:relative; margin-left:90px; margin-bottom:0; width:590px; }
.cntr { text-align:center; }
#rad { width:140px; position:absolute; left:210px; }
.chk1 { margin-left:150px; }
textarea { margin-left:120px; }
label, input { margin-bottom:3px; }
#sb { width:50px; margin-left:250px; margin-top:10px;}
img { vertical-align: top; }
h3 { width:590px; text-align:center; }
.red { color:red; }

Listing 7-4d. Creating the Conditional Style Sheet for Use With IE8 (feedback-ie8.css)

body {margin:0; padding:0; }
form {position:relative; margin-left:90px; margin-bottom:0; width:590px; }
.cntr { text-align:center; }
#rad { width:140px; position:absolute; left:210px; }
.chk1 { margin-left:150px; }
textarea { position:absolute; left:15px; margin-bottom:45px;}
label, input { margin-bottom:3px; }
#sb { position:absolute; top:770px; width:50px; margin-left:250px; margin-top:50px; }
img { vertical-align: top; }
h3 { width:590px; text-align:center; }
.red { color:red; }
#footer {margin-top:190px; }

The Thank You Page and the Error Messages

The thank you page confirms that the e-mail was sent successfully. It would be a pity to lose the visitor, so add a Return to Home Page button. The thank you page is shown in Figure 7-6, and the code is Listing 7-5a. The page has a Go Back button with the text Return to Home Page, but you can replace this button with your main navigation menu. The styling for the button is incorporated in the style sheet feedback.css.

Figure 7-6. The “thank you” page message

Listing 7-5a. Creating the “Thank You” Page (thankyou.html)

<!doctype html>
<html lang=en>
<head>
<title>Thank you for your enquiry</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="feedback.css">
</head>
<body>
<p> </p>
<div id="back-button"><a title="Return to the Home page" href="index.php">Return to
Home Page</a>
</div>
<div><br>
<h2>Thank you for your enquiry</h2>
<h3>We will email an answer to you shortly.</h3>
</div>
</body>
</html>

If the e-mail message was not sent successfully, an explanatory error message will appear.

Why use error pages instead of echoing a piece of text to the screen? I found that many of my clients prefer the distinct message, and the help that a page provides, rather than the usual small error messages in red that can be overlooked or that are so often too brief. If you prefer to echo messages to the page, create an $error() array to hold the messages as we did in the registration page.

Listing 7-5b. The Style Sheet for the “Thank You” Page (feedback.css)

/*FEEDBACK.CSS*/
/*reset browsers for cross-client consistency*/
body,h2,h3,p {margin:0; padding:0 }
body {text-align:center; background-color:#D7FFEB; color:black;
font-family: "times new roman"; max-width:100%; min-width:960px;
font-size: medium; color: #000000; margin: auto; width:95%;}
#back-button { margin:20px auto 0 auto; text-align:center; width:200px; height:25px;
padding:5px; background-color:brown; color:white; font-size:110%; font-weight:bold; }
#back-button a { text-decoration:none; color:white; }
#back-button a:hover { color:red; }
h2 { margin-top:15px; margin-bottom:10px; font-size:130%; font-weight:bold;}
h3 { font-size:110%; font-weight:bold; text-align:center;}

The style sheet in Listing 7-5c is used for all three error messages (Listings 7-5c through 7-5e).

Listing 7-5c. The Style Sheet for the Error Messages (error-style.css)

body { text-align:center; font-size: large; font-weight:bold;
}
span.red {color:red; font-size:xlarge; font-weight:bold;
}

Listing 7-5d provides the code for the error message for empty fields.

Listing 7-5d. Creating the Page for Empty Field Errors (error.html)

<!doctype html>
<html lang=en>
<head>
<title>Error message. Missing essentials</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="error-style.css">
</head>
<body>
<p> </p>
<p> </p>
<p>One or more of the essential items in the form has not been filled in.</p>
<p>Essential items have a red asterisk like this <span>*</span></p>
<p>Please return to the form<br> and then fill in the missing items</p>
<form method="post" action=" ">
<input type="button" value="Return to form" onclick="history.go(-1)">
</form>
</body>
</html>

Listing 7-5e shows the code for the e-mail error message.

Listing 7-5e. Error Message for an Invalid E-mail Address (emailerr.html)

<!doctype html>
<html lang=en>
<head>
<title>Email error message</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="error-style.css">
</head>
<body>
<p> </p>
<p>Your email address has an incorrect format.</p>
<p>Please return to the form<br>and then correct your email address.</p>
<form method="post" action=" ">
<input type="button" value="Return to form" onclick="history.go(-1)">
</form>
</body>
</html>

Listing 7-5f provides the code for the message forbidding the entry of URLs.

Listing 7-5f. Error Message for Unacceptable Comment Content (commenterror.html)

<!doctype html>
<html lang=en>
<head>
<title>Error message. Do not enter URLs</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="error-style.css">
</head>
<body>
Sorry, but website addresses are not allowed<br><br>
<p>Please click the Back button on your internet browser<br>and then remove any
website addresses from the form.</p>
</body>
</html>

Note You will need to add one more button to the main menu (includes/nav.php). Label the button “Contact Us”, and link it to the file feedback_form.php.

Migrating the Database and Tables to a Remote Host

Having created a database and table(s) using XAMPP, how can we transfer them to a hosting company? The process for migrating a database from XAMPP (or any other WAMP) to a remote host worries beginners more than any other aspect of database development. This is because MySQL manuals and Internet tutorials rarely provide proper explanations for the procedure; in fact, the many manuals I possess (or have borrowed) either omit or hardly mention this important topic.

For this tutorial, I chose to use the admintable database from Chapter 4 simply because it is shorter than the databases in Chapters 5 and 6 and will take up less space in this chapter.

Caution You will need to determine which operating system is used in your chosen hosting package. Linux and Windows require a slightly different migration procedure.

A Puzzling Error Message

I experienced an occasional problem when trying to transfer a database to another computer. I use phpMyAdmin to create a new empty database in the destination computer, and then I try to import the SQL dump for the table(s). An error message sometimes appears saying that the table already exists. This is weird because there is definitely not a table in that empty database. The solution is to access the XAMPP folder on the first computer and drill down to the MySQL folder and then the Data folder. I then save a copy of the somefilename.frm file on a pen drive (memory stick). I copy that file into the same Data folder on the destination computer and the problem is solved. Being rather paranoid about backups, I regularly save copies of the *.frm files as well as the SQL dumps.

Creating and Exporting the SQL File

The first stage for migrating a database consists of creating an SQL file (the dump file). You will be able to export two types of dumps: (i) for the table(s) only or (ii) f or the database and table(s). We will be using the table(s) only file because dumps of whole databases can be fraught with snags when you import them. They can also be huge and therefore unacceptable to many hosts. Use phpMyAdmin to export a file. The exported SQL file for the table(s) is a simple text file of the type filename.sql, although other file types can be chosen for the exported file. The content of the file and the process will be explained next.

To Create a Dump File of a Table or Tables

Open a browser, and in the address bar, type http://localhost/phpmyadmin/.

In the left panel of phpMyAdmin, click the database containing the admintable tables to be exported. Then click the Export tab as shown in Figure 7-7.

Figure 7-7. The screen for dumping the database table

When the Export tab is selected, the next screen appears as shown in Figure 7-8.

Figure 7-8. Export the SQL file

Select the Quick option, and ensure that the format is SQL. Then click the Go button shown circled. Depending on your browser and settings, you will be asked whether to open or save the file as shown in Figure 7-9.

Figure 7-9. Choose to save the SQL file

Choose to save the file, and click OK. You will be taken to a screen where you can decide where to save it. Save the file in a folder where you can access it for uploading it to the host later with your FTP client. The default is the Downloads folder.

What Does an SQL Dump Look Like?

We will now examine the admintable.sql file in WordPad (or better still, use Notepad++).

You will see many commented-out lines. SQL supports three comment styles as follows:

· A line beginning with a hash symbol like this: #some text

· A line beginning with a double dash followed by a space like this: -- some text

· A block of text between tags like this:

/*some text
some text
some text*/

The dump for the table in the admintable database (from Chapter 4) is shown next. Note the commented-out code. The remaining code, shown in bold type, will re-create the table in the destination server (the host or another computer with XAMPP installed).

-- phpMyAdmin SQL Dump
-- version 3.5.2
--http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 27, 2012 at 06:51 PM
-- Server version: 5.5.25a
-- PHP Version: 5.4.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `logindb`
--
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
`fname` varchar(30) NOT NULL DEFAULT 'NOT NULL',
`lname` varchar(40) NOT NULL DEFAULT 'NOT NULL',
`email` varchar(50) NOT NULL DEFAULT 'NOT NULL',
`psword` varchar(40) NOT NULL DEFAULT 'NOT NULL',
`registration_date` datetime NOT NULL,
`user_level` tinyint(1) unsigned NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `fname` (`fname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `users`
--
INSERT INTO 'users' ('user_id', 'fname', 'lname', 'email', 'psword',
'registration_date', 'user_level') VALUES
(1, 'Rose', 'Bush', 'rbush@myisp.co.uk', '0f2ded3794a9f1ae11c2aba8ff6dff00dd1e4ac6',
'2012-09-20 12:31:49', 0),
(3, 'Mike', 'Rosoft', 'miker@myisp.co.uk', '315806a3a2ae3ae81d1294746df09ac6ceaa587c',
'2012-09-29 20:08:29', 0),
(4, 'Olive', 'Branch', 'obranch@myisp.com', '0d05e04b8b1cf237dcdaab55468c54733c7fdeb1',
'2012-09-29 20:09:15', 0),
(5, 'Frank', 'Incense', 'fincense@myisp.net', '71ea58aa789b63d377fa73c441348da5840bd0dc',
'2012-09-29 20:11:34', 0),
(6, 'Annie', 'Versary', 'aversary@myisp.co.uk', '2f635f6d20e3fde0c53075a84b68fb07dcec9b03',
'2012-09-29 20:14:09', 0),
(7, 'Terry', 'Fide', 'tfide@myisp.de', '55deee02330052a7bb715168f9405b33ef752662',
'2012-09-29 20:15:41', 0),
(11, 'James', 'Smith', 'jsmith@myisp.co.uk', '34ae707a963ad8a1fb248f8c1f50a4d3d5dd2e64',
'2012-09-29 20:28:53', 0),
(16, 'Jack', 'Smith', 'jsmith@outlook.com', 'bda7aeb2f7a4cf6f6f26b7c9e96e009913b2594b',
'2012-10-14 19:17:32', 1),
(18, 'Helen', 'Back', 'hback@myisp.net', 'f16879eb900436b07ee6f31a46da0819ffba6a94',
'2012-12-07 19:09:13', 0),
(19, 'Patrick', 'O''Hara', 'pohara@myisp.org.uk', '3d8385096ef3b5712bdee38e4e385cf626d5de9c',
'2012-12-09 16:12:07', 0),
(20, 'Lynn', 'Seed', 'lseed@myisp.com', 'a2614195adf5952916965acba1b4111058453ba4',
'2012-12-09 16:18:14', 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

The next steps are to do the following:

· Examine the destination server (the host), and prepare it to receive the SQL file and the contents of the htdocs folder.

· Upload the SQL file and web-site files to the host.

· Import the SQL file using phpMyAdmin in the host.

Investigate the Remote Host’s Server

We will assume that you have reserved a space for the web site and registered the domain name with your remote host. Also, you should have determined whether your chosen hosting package uses Linux or Windows.

A remote host must have PHP and MySQL installed. If you are not sure whether PHP and MySQL are installed at the host, type the following in a text editor and save it as phpinfo.php:

<?php
phpinfo();
?>

Use your FTP client to upload the file to the host and open it in a browser. You will see a table like the one in Figure 7-10. Scroll down the table, and you should see details of the MySQL and MySQLi installation on the host’s server.

Figure 7-10. The result of opening phpinfo.php in a server

Caution Some basic hosting packages do not accept databases. In this case, you will have to upgrade to a more expensive package. Check the product range on your intended host before proceeding further.

Using the GUIs on a Remote Host’s Server

Access the control panel of the host, and ensure that it allows you to upload a database. Scroll down the control panel until you see the section shown in Figure 7-11.

Figure 7-11. The database section of the control panel

If a database section is not shown in the control panel, it means that the hosting packet needs to be upgraded. Assuming that the host will accept databases, follow these steps:

· Click the phpMyAdmin icon, and create an empty database. In this example, we will pretend to create the admintable database.

When creating the admintable database, the resulting database will either have the name admintable or the host will add a prefix to the database name. Most hosts add a prefix, and it is nearly always the web-site owner’s username for accessing the control panel. Let’s assume that the owner’s username is mywebsite; the host will create the database name as mywebsite_admintable. In this example, we will assume that the database name has the prefix myusername.

· Now you must immediately protect the newly created database by giving it a user and a password. Do this by using phpMyAdmin as described in Chapter 1. (Some hosts will automatically provide the user name—if that’s the case, use that one.)

Create a user with the following details:

User: webmaster (or a name provided by the host)

Password: coffeepot

Host: localhost

The password in a real-world database must be much more complex than coffeepot. Make a careful note of the database name, password, and user.

Connecting to the Database on the Remote Host

Now that we know the name of the empty database (in this example, it is myusername_admintable), we can amend the connection file to suit the remote host. Do not create this in XAMPP’s htdocs folder on your computer. If you do, you will no longer be able to run the admintable databaseon your computer’s server. Use a text editor such as notepad++ to create the file and store the new mysqli_connect.php file anywhere except in the htdocs folder. The amended file is not included in the downloadable files because it would be unique for your database. The amendment might be as shown in bold type in the next snippet of code.

The Snippet of Code for Connecting to the New Database (msqli_connect.inc.php).

<?php
// Create a connection to the admintable database and set the encoding
// The user name might be provided by the host. If not, use the name from your XAMPP version
DEFINE ('DB_USER', 'webmaster');
DEFINE ('DB_PASSWORD', 'coffeepot');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'myusername_admintable');
// Make the connection
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die
('Could not connect to MySQL: ' . mysqli_connect_error() );
// Set the encoding
mysqli_set_charset($dbcon, 'utf8');

You now have all the items necessary for migrating the database-driven web site to the remote host as follows:

· An account and a domain name registered with the remote host.

· A newly created empty database located on the host’s server.

· The SQL dump files for the tables.

· The folder containing the includes, and the folder containing the images.

· The modified mysqli_connect.php file.

The main PHP and HTML files need an amendment before they can be uploaded, and this amendment is described in the next section.

Securely Upload the mysqli_connect.php File

For maximum security, the database connection file should be located outside the root folder. The file is then not directly accessible via the web site. We will also give it a name that would confuse a hacker who is searching for the file. Figure 7-12 shows an FTP window for viewing the folder structure on a remote host.

Figure 7-12. The remote host’s files and folders shown in an FTP program’s window

In Figure 7-12, the database connection file (circled) has been renamed as greenh.php and uploaded outside the public_html root folder (circled).

Having uploaded the connection file outside the root folder on the host, none of your interactive pages will be able to find it. To solve this, use your code editor to open every file that contains a mysqli_connect.php link and run a Find-and-Replace-All to amend its location as follows.

Find mysqli_connect.php and replace it with ../greenh.php. The symbols ../ tell the pages that the file is located one level above the current folder. For connecting to the database on your own computer/server, you have always used require ('mysqli_connect.php');

Now that the connection file is safely hidden from hackers and the interactive pages have been modified to match, you can upload the HTML and PHP pages to the remote host.

Uploading the Interactive Pages to the Host

The final steps are as follows:

· Most hosts will be using Linux. In this case, there will not be an htdocs folder in the public_html folder. Therefore, you need to use your FTP client to upload the HTML and PHP pages from your admintable’s htdocs folder into the public_html folder in the host’s server. Because you already uploaded an amended and renamed mysqli_connect.php file, take great care not to upload the unmodified version into the public_html folder.

· If your host uses Windows, use your FTP client to upload the content of your admintable’s htdocs folder into the host’s htdocs folder.

· Use your FTP client to upload the SQL files for the tables into the public_html folder.

· Log in to the control panel at the host, and open phpMyAdmin.

· In the left panel, click the name of the newly created empty database, and then click the Import tab.

· You will be asked to name the SQL file to be imported. Browse to the public_html folder, and find each uploaded SQL file. Open them one at a time, and then click Go.

· At some earlier date, you might have uploaded a file named index.html. Your new index.php file must replace the html file; otherwise, index.html will load instead of your index.php file. Rename the index.html file at the host as old-index.html, or delete it.

Note Beginners are often puzzled by the lack of an htdocs folder on the host server because MySQL and Apache normally look for files in the htdocs folder. To explain the mystery, if the host is using Linux, then the host will have configured Apache and MySQL so that it looks in thepublic_html folder.

View your uploaded interactive web site online by typing your web site’s URL into the address field of a browser. You should see your home page, and from there you can test the database features. Be aware that you might have to wait a little while because some hosts might take time to update their server.

As with any computer work, backups are an essential precaution against hardware or software failure. Also, you might want to roll back to an earlier version when experimenting with files.

Back Up Your Database

While you are learning to create and use databases, four steps are essential:

1. Continue writing in your notebook.

2. Be sure to update your flow chart.

3. Use the Export tab in phpMyAdmin to back up your tables.

4. Back up your htdocs folder.

Recording important steps and details such as usernames and password has saved me a lot of grief. During my learning period, when I created many experimental databases and variations of those databases, I could not possibly remember the details of each one. Also, I recorded details of the stage that I had reached and what I intended to do next. This was especially important if I had to stop for two or three days to update some of my clients web sites. Clients never seem to request updates singly; they come in bunches, with four or five clients clamoring for updates at the same time.

I also recorded useful web-site URLs. If I came across a helpful tip in a manual, I made a note of the page number. This saved me from having to trawl though the manual to find the item again. I recorded the URLs, usernames, and password for the many forums that I contacted for help. A ring-back notebook is ideal because you can insert dividers to help you find where you wrote that vital bit of information.

For backups, I recommend using phpMyAdmin to export SQL dumps for the tables from each database. Then copy them to a CD or USB pen drive so that you can create a copy in another computer.

Make a copy of the htdocs folder on a CD or pen drive so that it can be copied to another computer. Having suffered the occasional hard disk or OS failure, I also make a complete copy of each database-driven web site on one or two other computers. The other computers each have XAMPP and an FTP program installed. Extra work is entailed in keeping each web site up to date, but if a computer dies, I can continue working on another computer. Peace of mind is a wonderful thing.

Summary

In this chapter, we added some last-minute improvements suggested by the client and the membership secretary. These included adding a title column to the paginated table display, allowing registered members to update their own details, and adding a live e-mail address to be displayed on the web site. The client also asked for an inquiry form for the web site. All these additions to the web site were described and listed. The chapter then gave a full description of the procedures for migrating a database to a host and backing up database tables.

In the next chapter, we will learn how to create a product catalog for a real estate agent.