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

Chapter 6. The Finishing Touches

In the previous chapter, we created the beginnings of a useful database and several interactive web-site pages. However, we were not able to edit the addresses and telephone numbers. We will now remedy this. The folder and filing system for the PHP pages will be tidied up to reduce the clutter. The previous interactive pages had a minimum of user input filtering; therefore, this chapter will introduce more security filtering and validation. The steps that are needed to finish the web site and database are described in the following sections:

· Create a copy of the database from Chapter 5 using a new name and password.

· Create a copy of the table from Chapter 5 by importing an SQL dump file.

· Tidy up the folder and filing system.

· Providing more information on security.

· Validating and sanitizing user input.

· Creating a more secure registration page.

· Searching for addresses and telephone numbers.

· Viewing and editing the retrieved addresses and phone numbers.

Create the Database

First we will create a new version of the postaldb database named finalpost:

1. Open the XAMPP htdocs folder, and create a new folder named finalpost.

2. Download the files for Chapter 6 from the book’s web site at www.apress.com, and place the files in the new folder called finalpost.

3. Then use a browser to access phpMyAdmin, click the Databases tab, and create a new database called finalpost.

4. Scroll down and find the newly created database. Then select the check box alongside it.

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

1. User name: cabbage

2. Password: in4aPin4aL

3. Host: local host

6. Scroll down; next to Global Privileges, click Check All.

7. Accept the default for the Resource limits.

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

Create the File for Connecting to the Database

Create a new connection file named mysqli_connect.php. (If you don’t need to practice creating the file from scratch, it is already included in the downloaded files for Chapter 6.)

The Database Connection File (mysqli_connect.php)

<?php
// Create a connection to the postaldb database
// Set the database access details as constants and set the encoding to utf-8
DEFINE ('DB_USER', 'cabbage');
DEFINE ('DB_PASSWORD', 'in4aPin4aL');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'finalpost');
// 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');

Tip When composing a password for use in the real-world, you should make it as complex as possible. Ensure that it is not related in any way to the name of the database. A complex password can be easily remembered by means of a mnemonic. The mnemonic can then be written down and placed under lock and key. The mnemonic chosen for the password (in4aPin4aL) in this tutorial is the old British saying, “In for a penny, in for a pound.” One of the meanings of this maxim is, “If we start this, we must make sure we finish it; there must be no drawing back.” In the password itself, the uppercase P stands for Penny, and the uppercase L is the archaic symbol for a British Pound (100 pennies).

Create the Table by Importing an SQL Dump File

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

I think I can hear you groaning, “Oh, no! Not another table!” Don’t worry, I am about to show you a shortcut using the Import/Export facility in phpMyAdmin.

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

To import the .sql file: in the home page of phpMyAdmin, click the database finalpost and then create a table called users with 17 columns, but STOP at that point. Don’t insert any attributes.

When the message appears confirming that your table was created, you can click the Import tab.

Click the Browse button (shown circled) on the Import screen in Figure 6-1.

Figure 6-1. The Import screen in phpMyAdmin

The browse button allows you to navigate to the file users.sql. Open the users.sql file, and then click Go and 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.

If you wish to Create the Table Manually

If you need the practice, you can create the table from scratch as follows.

Use phpMyAdmin to create the membership table named users. Name the table users, and create 17 columns. Give the columns the titles and attributes shown in Table 6-1.

Table 6-1. The Titles and Attributes for the users Table

Note that the columns for the second address and the phone number have their NULL boxes checked. This is because they will be optional fields that can be ignored by the user. Many users may not have an additional address, such as the district of a very large town. Some users are ex-directory and do not wish to disclose their telephone numbers in order to reduce the cold-call nuisance.

In the UK, the county column can be omitted because the Royal Mail relies entirely on the Post Code for mail delivery. In the USA, county would be replaced by state and abbreviations would be acceptable, such as CA for California. To cover both territories, the column title might becounty_state.

Registering Some Members Manually

If you decide to enter the members manually, create the empty table, run XAMPP, and then use a browser to enter this URL:

http://localhost/finalpost/safer-register-page.php

The file safer-register-page.php is a more secure version of the register-page.php file from Chapter 5. The file is included in the downloadable files for Chapter 6. The security enhancements for the safer file are described later in this chapter. First, register these two members: Mr James Smith and the administrator Mr Jack Smith, as you did in Chapter 5. The procedure is repeated here.

Register the following user as a member:

Title: Mr

First name: James

Last name: Smith

E-mail: jsmith@myisp.co.uk

Password: blacksmith

Username: muscleman

James Smith has been appointed as the membership secretary. We can safely assume that James Smith would like to view the members page occasionally as well as viewing the administration page. He will use his membership password to view the members page. He needs a different password and e-mail address to access the administration section; therefore, he needs a new registration identity as follows:

Title: Mr

First name: Jack

Last name: Smith

E-mail:jsmith@outcook.com

Password: dogsbody

Username: wagglytail

In a real-world situation, an administrator would need a much more complex password and a less obvious pseudonym, but this is not important for this tutorial.

Use phpMyAdmin to change the administrator’s user_level to 1.

Register several more members so that you have something to play with. Table 6-2 suggests some users’ details.

Table 6-2. Suggested Details for Registering Members Manually

Tip For your convenience, I repeat the tip given in the previous chapter as follows: Entering the data can be tedious using the manual method. To reduce the tedium in this tutorial, you can enter identical addresses and telephone numbers for each user. A set of suitable data is suggested next.

When experimenting, I use the following data in every record. This is acceptable because no searches will be made in this tutorial using addresses and telephone numbers. Instead, searches are made using the members’ first and last names.

Now we need to do some more housekeeping because the ever-increasing number of PHP and HTML files needs to be organized into folders.

Tidy the Folders and Filing System

The number of files in the postal folder in the previous chapter increased as each chapter added more features. The clutter can be confusing. To tidy the new finalpost folder, we need to place all the included files in their own folder. I already did this in the downloadable files for this chapter. Note the new includes folder and the fact that, in the main files, all the PHP includes now have the prefix includes/, as shown in the following snippet:

<?php include("includes/header-admin.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>

The info column for the registration page safer-register-page.php contains a credit/debit card logo, which is provided by PayPal. The code for this is in the included file info-col-cards.php and is as follows:

<?php include("includes/info-col-cards.php"); ?>

The Style Sheet

If you examine the downloaded includes files (contained in the includes folder) you will see that their internal styles have been removed. This ensures that the web site pages will validate. The styles for the included files have been placed in the main style sheet as follows:

The New Main Style Sheet (includes.css)

body {text-align:center; background-color:#D7FFEB; color:navy; font-family:
"times new roman"; font-size: 100%; color: navy; margin: auto; }
h2 { font-size:150%; color:navy; text-align:center; }
h3 { font-size:110%; color:navy; text-align:center; }
#container {position:relative; min-width:960px; max-width:1200px; margin:auto;
text-align:left; }
#header, #header-members, #header-admin { margin:10px auto 0 auto; min-width:960px;
max-width:1200px; height:175px; background-image: url('images/tile-pale.jpg');
background-repeat: repeat; padding:0; color:white; }
h1 {position:relative; top:40px; font-size:350%; color:white; margin:auto 0 auto 20px;
width: 600px; }
ul { position:absolute; top:180px; left:-40px; color:navy; width:135px; text-align:center;
margin:0; }
#nav ul { position:absolute; top:190px; left:-10px; color:navy; width:135px;
text-align:center; margin:0; }
#info-col { position:absolute; top:190px; right:10px; color:navy; width:135px;
text-align:center; margin:5px 5px 0 0; }
/* set general side button styles */
li { width:115px; list-style-type :none; margin-bottom: 3px; text-align: center; }
/* set general anchor styles */
li a { display: block; width:115px; color: white; font-weight: bold; text-decoration: none }
/* specify state styles. */
/* mouseout (default) */
li a { background: #5B78BE; border: 4px outset #aabaff; }
/* mouseover */
li a:hover { display:block; background: #0a4adf; border: 4px outset #8abaff; width:115px; }
/* onmousedown */
li a:active { background:#aecbff; border: 4px inset #aecbff; }
#reg-navigation ul { float:right; font-size:medium; width:160px; margin:-150px 15px 0 88%; }
#midcol {width:90%; margin:auto; }
#mid-left-col { width:48%; float:left; text-align:left; }
#mid-right-col {width:48%; float:right; text-align:left; }
#content { margin-left:150px; margin-right:150px; }
table { width:800px; border:1px navy solid; border-collapse:collapse; margin:auto; }
td { border:1px navy solid; padding:1px 0 1px 4px; text-align:left; }
form { margin-left:180px; }
#footer { margin:auto; text-align:center; }
p.error { color:red; font-size:105%; font-weight:bold; text-align:center; }
.label { float:left; width:210px; text-align:right; clear:left; margin-right:5px; }
#submit { margin-left:215px; text-align:center; }
span.left { text-align:left; }

Tip If you have more than one style sheet in a web site, it is good practice to create a folder for them named styles. The links for the styles would then have the following format:
<link rel=“stylesheet” type=“text/css” href=“styles/somestylesheet.css”>.

Ensuring the security of a database will be discussed next. Security is the most important aspect of the development process and perhaps the most tedious. Inappropriate input from a user can insert faulty data into a database table. More seriously, it can cause a leakage of private information or allow nefarious people to trash a table. Unacceptable input can be the work of criminals, or it could simply be users’ mistakes. Therefore, all user input must be filtered to ensure that it is fit for your database and for the server.

Degrees of Security

A database can be insecure or partially secure; it can never be 100% secure. However, this statement must be qualified because the risk depends on several other factors besides the built-in security of the database. Criminals will not bother targeting small concerns; they will go for big firms that host many users’ details, especially if money is handled and credit-card details are included in the data. Postal addresses, e-mail addresses, and users’ preferences can be harvested from an insecure database and sold for substantial amounts of money.

Databases for small firms, clubs, or societies must have at least a minimum layer of security built in.

The Minimum Layer of Security

The database must have a unique name (up to 16 characters long), a password (as complex as possible), a host name (usually localhost), a designated user, and a set of permissions.

Multiple users are permissible and their permissions should differ, but the number of users must be severely restricted for maximum security. Additional users should not have permissions such as GRANT, SHUTDOWN, or DROP.

MySQL encrypts the password before storing it on the server. The webmaster should choose a password that is difficult to deduce. Never use a word that can be found in a dictionary or a word that is an anagram of the webmaster’s name. You could take a popular saying such as “Raining again, good weather for ducks” and create a password something like RaGw4dux.

Always assume that everything a user enters will be a danger to the database. Also, the data should be as accurate as possible. For instance, e-mail addresses with incorrect formats are of no use to the user or to the owner of the database.

Trim all string entries, and run them through the function mysqli_real_escape_string() as follows:

$q = mysqli_real_escape_string($dbcon, trim($_POST['first_name']));

mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prefixes backslashes to the following characters: \x00, \n, \r, \, ’, ” and \x1a.

Validate e-mail addresses, phone numbers, and all numerical items, but clean everything else. The two techniques validation and sanitization will be explained later in this chapter.

Filter all user input so that HTML tags and scripts cannot be inserted into a database. Scripts can infiltrate the database using HTML tags, such as <script>, in conjunction with JavaScript.

Where possible, store the file that accesses the database in a folder located one level above the root folder. (The root folder is htdocs or www.) Contact your remote host to see if this is possible. The connection code we have been using is

require ('mysqli_connect.php');

When using a file in a folder one level above the root level, the connection code is

require ('../mysqli_connect.php');

The downloadable PHP files for this tutorial will retain the code require (mysqli_connect.php'); because the database will be located on your computer and not on a remote host.

You could rename the connection file so that a hacker would dismiss it as not what he is searching for. If the web site is selling original paintings, you might change the name of the connection file to canvas.php. The new name fits the theme of the web site but is not conspicuous. However, if you chose the name monalisa.php, a hacker would be suspicious because there is no way that you would have the Mona Lisa for sale. The hacker would guess you are hiding something important.

Always insert and retrieve passwords using SH1 or SH2 encoding like this SH1($password).

Where possible, prevent browsers from displaying error messages that reveal the structure of the database. These messages can help a hacker find his way around your database.

Always use sessions to restrict access to vulnerable pages, such as an administration page and members’ special pages.

You can safely accept payments through a web site by using PayPal, Stripe, or a similar secure payment system. But NEVER store members’ bank/debit/credit card details.

An Increased Layer of Security

If your clients wish to use an internal payment system rather than using PayPal, they will need a very expensive security budget. For sites where national security is at risk, or when users’ financial details are stored, the site will require specialist staff or an expensive agency to supervise and continually monitor the web site.

A secure server is the main requirement for such vulnerable databases. The client would have to pay a substantial annual fee for a server using Secure Sockets Layer (SSL).

Validation and Sanitization

Here, we will demonstrate the difference between validation and sanitization.

· Validating:The validator checks the format of the users’ input. If the format is incorrect, the form page halts and an error message asks the user to correct the input. Validation ensures that e-mail addresses and phone numbers have the correct characters. This prevents incorrect data from being entered into a database table. It also protects against the injection of dangerous code.

· Sanitizing: This can be achieved by two methods: (i) using standard PHP cleaners or (ii) using the new filter_var() function with the SANITIZE attribute. Sanitizing removes undesirable characters automatically before the data is sent to the database table. When you are using the new SANITIZE attribute, no error message is displayed. Users are not alerted to the fact that they have made a mistake or that their input contains dangerous characters or scripts. If an error message would be useful, the web designer must add his or her own error message. Sanitization can remove HTML tags and JavaScript commands that could harm a database.

Caution The filter_var( ) with sanitization should not be used to clean e-mail addresses. Sanitizing does not check that e-mails are in the correct format. An e-mail address must have the correct format; otherwise, it is useless. Therefore, VALIDATE is the appropriate attribute for filtering e-mail addresses.

Validation and sanitization can be applied by means of the function filter_var().

The filter_var() Function

The function filter_var() is included with PHP version 5.2 and later. Its purpose is to simplify the validation and sanitization of user input. The function is used in conjunction with either the FILTER_VALIDATE or FILTER_SANITIZE attribute. For more detailed information on this function, see http://php.net/manual/en/function.filter-var.php.

Validation

In Chapter 5, the registration page had no built-in validation or sanitization. A user could enter data that would either damage the web page or interfere with the database. The user could enter items that were in the wrong format. As an example of a wrong format, a user could make a mistake when entering an e-mail address. The incorrect address could contain spaces or unacceptable characters as follows:

rbush 'myisp.co m

That faulty e-mail address contains no @ symbol, and it has two spaces and an apostrophe. These are unacceptable e-mail characters. The VALIDATION attribute will recognize the problems and provide an error message asking the user to correct the e-mail address.

In Chapter 5, in the file register-page.php, the code for the e-mail was as follows:

// Check that an email address has been entered
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';
} else {
$e = mysqli_real_escape_string($dbcon, (trim($_POST['email'])));
}

The code merely cleaned the input and checked that the field contained some characters. However, the e-mail address format might be complete nonsense, and that nonsense would have been entered into the database table. In the past, I would have used regex (Regex is an abbreviation ofRegular Expressions). to validate the format of the e-mail address, but with the advent of PHP 5.2 and later versions, the filter_var() function became available and this was a great improvement. Validation by means of regex is rather complicated and prone to typing errors, as you will see from the following example shown in bold type:

$email = "name@usersisp.com";
if (preg_match('/^[^0-9][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[@][a-zA-Z0-9_]+([.]
[a-zA-Z0-9_]+)*[.][a-zA-Z]{2,4}$/',$email)){
echo "Your email is in the acceptable format";
} else {
echo "Your email address does not have an acceptable format";
}

Validating by means of the filter_var() function is a great improvement, as you will see in line #4 in the next snippet of code for validating an e-mail address. This is used in the registration form safer_register_page.php.

// Set the email alias as FALSE and check for an email address
$e = FALSE; #1
// Check that an email address has been entered #2
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 #3
if (filter_var((trim($_POST['email'])), FILTER_VALIDATE_EMAIL)) { #4
//A valid email address is then registered
$e = mysqli_real_escape_string($dbcon, (trim($_POST['email'])));
}else{ #5
$errors[] = 'Your email address is invalid or you forgot to enter your
email address.';
}

Explanation of the Code

// Set the email alias as FALSE and check for an email address
$e = FALSE; #1

Assigning the keyword FALSE to the e-mail constant $e may seem an odd thing to do. However, it is a useful trick because it reduces the amount of code required in the conditionals that follows that statement.

// Check that an email address has been entered #2
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';

This first conditional is the same as the one in Chapter 5.

}else{ #3
//remove any spaces from beginning and end of the email address and validate it

Any unwanted spaces at the beginning or end of the e-mail address will be removed by means of the PHP trim() function. This filter is then embedded in the next statements:

if (filter_var((trim($_POST['email'])), FILTER_VALIDATE_EMAIL)) { #4
$e = mysqli_real_escape_string($dbcon, (trim($_POST['email'])));

The PHP function filter_var() is introduced into the code here. The pattern for using it is as follows:

If (filter_var(a variable, the type of filter)) {

If the function determines that the e-mail address has the correct format, the e-mail address is assigned to the variable $e. The variable $e is no longer FALSE. In other words, the e-mail address is accepted and entered into the database table.

}else{ #5
$errors[] = 'Your email is not in the correct format.';
}

When an e-mail address is in an incorrect format, the variable $e is unchanged. In the else clause, the variable $e remains FALSE because it was set to FALSE in line #1. An error message is then inserted into the errors array.

Tip A negative form of a function is available by simply placing an exclamation mark in front of the function as follows: if (!filter_var( variable, FILTER_VALIDATE_EMAIL)) { This means, “if the email does not have the correct format. . .”.

Some text input might not have a valid format because it does not conform to a set pattern—for instance, a text area for comments or a person’s title. In the UK, we have titles that are a holdover from medieval feudalism, quaint things like Baroness, Lord, Lady, or Sir. Some retired military members will use their armed forces titles like Major or Colonel. Text that does not conform to a set pattern can be cleaned by means of either a regex function or possibly with filter_var( ) and the SANITIZE attribute.

Sanitization

The filter_var() function used with the SANITIZE attribute will not be used in this book because it is tricky to implement and this book presents a simplified approach to database design. Also, many PHP gurus seem to discourage its use. This is not a problem because another solution is available that does not use SANITIZE. Instead, it uses two other PHP functions: strip_tags() and mb_strlen(). The first function removes HTML tags, and the second checks the length of the string input by the user. If the string length is less than one (i.e., it is zero), the user has failed to enter a string. The following snippet of code illustrates the use of these two functions to sanitize a string:

// Trim the user’s input
$name = trim($_POST['fname']);
// Strip HTML tags 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.';
}

This code will produce an error message if the field has not been filled in. If a user injects some HTML or JavaScript into a field, the function strip_tags() not only strips out tags such as <p> but it removes the characters between the opening and closing HTML tags. That does not matter because anyone entering HTML tags or JavaScript is usually attacking the database.

Text Areas and Sanitization

Obviously, a text area (a field accepting many lines of text) cannot conform to a format for validation. Sanitization by means of regex must be used for a text area. This is covered in Chapter 10.

Validating Telephone Numbers

At the time of this writing, PHP does not have a built-in validation attribute for telephone numbers. Telephone numbers do not conform to a common format. Users will enter any combination of numbers, spaces, hyphens, and brackets. Also there are international differences. To complicate matters, some users will write the numbers in groups as follows:

0111 222 333 or (0111) 222 333 or 0111-222-333

We must therefore resort to a regex method of validation. Phone numbers have national differences. To help you cope with this, many regex expressions are available. This book offers a simplified approach. The neatest solution is to strip out every character that is not a number. Brackets, letters, spaces, and hyphens can be removed by using a regex function. This function is used in the revised registration page that is described next.

A More Secure Registration Page

To make the registration page more secure, several techniques were employed. The revised registration page has exactly the same appearance as the registration page in the Chapter 5, as shown in Figure 6-2.

Figure 6-2. The registration page

Additional security and cleaning has been included, and this will be explained at the end of Listing 6-2. The modified items are shown in bold type.

Listing 6-2. Creating a More Secure Registration Page (safer-register-page.php).

//The form contains an internal style sheet because the styling of the form elements is unique to this page.

<!doctype html>
<html lang=en>
<head>
<title>Safer register pagepage</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
#midcol { width:98%; margin:auto; }
input, select { margin-bottom:5px; }
h2 { margin-bottom:0; margin-top:5px; }
h3.content { margin-top:0; }
.cntr { text-align:center; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/register-header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col-cards.php");?>
<div id="content"><!--Start of the registration page content-->
<?php
require ('mysqli_connect.php'); // Connect to the database
// This code inserts a record into the users table
// Has the form been submitted?
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$errors = array(); // Start an array named errors
// Trim the title #1
$tle = trim($_POST['title']);
// Strip out HTML code 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 out HTML code 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 #2
$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.';
}
// Check that a password has been entered, if so, does it match the confirmed password
if (empty($_POST['psword1'])){ #3
$errors[] ='Please enter a valid password';
}
if(!preg_match('/^\w{8,12}$/', $_POST['psword1'])) { #4
$errors[] = 'Invalid password, use 8 to 12 characters and no spaces.';
} else{
$psword1 = $_POST['psword1'];
} #4
if($_POST['psword1'] == $_POST['psword2']) { #5
$p = mysqli_real_escape_string($dbcon, trim($psword1));
}else{
$errors[] = 'Your two password do not match.';
}
// Trim the username
$unme = trim($_POST['uname']);
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($unme));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$errors[] = 'You forgot to enter your secret username.';
}else{
$uname = $stripped;
}
// Check for a membership class
if (empty($_POST['class'])) {
$errors[] = 'You forgot to choose your membership class.';
} else {
$class = trim($_POST['class']);
}
// 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{
$ad1 = $stripped;
}
// Trim the second address
$ad2 = trim($_POST['addr2']); #6
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($ad2));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$ad2=NULL;
}else{
$ad2 = $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{
$cty = $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{
$cnty = $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? #7
if (empty($_POST['phone'])){
$ph=($_POST['phone']);
}
elseif (!empty($_POST['phone'])) {
// Strip out everything that is not a number
$phone = preg_replace('/\D+/', '', ($_POST['phone']));
$ph=$phone;
}
if (empty($errors)) { // If no problems occurred
//Determine whether the email address has already been registered for a user
$q = "SELECT user_id FROM users WHERE email = '$e' ";
$result=mysqli_query ($dbcon, $q) ;
if (mysqli_num_rows($result) == 0){//The mail address was not already registered
therefore register the user in the users table
// Make the query:
$q = "INSERT INTO users (user_id, title, fname, lname, email, psword, registration_date,
uname, class, addr1, addr2, city, county, pcode, phone, paid) VALUES (' ', '$title',
'$fn', '$ln', '$e', SHA1('$p'), NOW(), '$uname','$class', '$ad1', '$ad2', '$cty',
'$cnty', '$pcode', '$ph', '$pd ' )";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If the query ran OK
header ("location: register-thanks.php");
exit();
} else { // If the query did not run OK
// Errosr message
echo '<h2>System Error</h2>
<p class="error">You could not be registered due to a system error. We apologize for
the inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br>Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
// Include the footer and stop the script
include ('includes/footer.php');
exit();
} else {//The email address is already registered
echo '<p class="error">The email address is not acceptable because it is already registered</p>';
}
}else{ // Display the errors
echo '<h2>Error!</h2>
<p class="error">The following error(s) occurred:<br>';
foreach ($errors as $msg) { // Echo each error
echo " - $msg<br>\n";
}
echo '</p><h3>Please try again.</h3><p><br></p>';
}// End of if (empty($errors))
} // End of the main Submit conditional
?>
<div id="midcol">
<h2>Membership Registration</h2>
<h3 class="content">Items marked with an asterisk * are essential</h3>
<h3 class="content">When you click the 'Register' button, you will be switched to
a page<br>for paying your membership fee with PayPal or a Credit/Debit
card</h3>
<p class="cntr"><b>Membership classes:</b> Standard 1 year: £30, Standard 5years:
£125, Armed Forces 1 year: £5,<br>Under 21 one year: £2,
Other: If you can't afford £30 please give what you can, minimum £15 </p>
<form action="safer-register-page.php" method="post">
<label class="label" for="title">Title*</label>
<input id="title" type="text" name="title" size="15" maxlength="12"
value="<?php if (isset($_POST['title'])) echo $_POST['title']; ?>">
<br><label class="label" for="fname">First Name*</label>
<input id="fname" type="text" name="fname" size="30" maxlength="30"
value="<?php if (isset($_POST['fname'])) echo $_POST['fname']; ?>">
<br><label class="label" for="lname">Last Name*</label>
<input id="lname" type="text" name="lname" size="30" maxlength="40"
value="<?php if (isset($_POST['lname'])) echo $_POST['lname']; ?>">
<br><label class="label" for="email">Email Address*</label>
<input id="email" type="text" name="email" size="30" maxlength="60"
value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>" >
<br><label class="label" for="psword1">Password*</label>
<input id="psword1" type="password" name="psword1" size="12" maxlength="12"
value="<?php if (isset($_POST['psword1'])) echo $_POST['psword1']; ?>" >
8 to 12 characters
<br><label class="label" for="psword2">Confirm Password*</label><input id="psword2"
type="password" name="psword2" size="12" maxlength="12"
value="<?php if (isset($_POST['psword2'])) echo $_POST['psword2']; ?>" >
<br><label class="label" for="uname">Secret User Name*</label><input id="uname"
type="text" name="uname" size="12" maxlength="12"
value="<?php if (isset($_POST['uname'])) echo $_POST['uname']; ?>"> 8 to 12 characters
<br><label class="label" for="class">Membership Class*</label>
<select name="class">
<option value="">- Select -</option>
<option value="30"<?php if (isset($_POST['class']) AND ($_POST['class'] == '30'))
echo ' selected="selected"'; ?>>Standard 1 year £30</option>
<option value="125"<?php if (isset($_POST['class']) AND ($_POST['class'] == '125'))
echo ' selected="selected"'; ?>>Standard 5 years £125</option>
<option value="5"<?php if (isset($_POST['class']) AND ($_POST['class'] == '5'))
echo ' selected="selected"'; ?>>Armed Forces 1 year £5</option>
<option value="2"<?php if (isset($_POST['class']) AND ($_POST['class'] == '2'))
echo ' selected="selected"'; ?>>Under 22 1 year £2**</option>
<option value="15"<?php if (isset($_POST['class']) AND ($_POST['class'] == '15'))
echo ' selected="selected"'; ?>>Minimum 1 year £15</option>
</select
<br><label class="label" for="addr1">Address*</label><input id="addr1"
type="text" name="addr1" size="30" maxlength="30"
value="<?php if (isset($_POST['addr1']))
echo $_POST['addr1']; ?>">
<br><label class="label" for="addr2">Address</label><input id="addr2"
type="text" name="addr2" size="30" maxlength="30"
value="<?php if (isset($_POST['addr2']))
echo $_POST['addr2']; ?>">
<br><label class="label" for="city">City*</label><input id="city"
type="text" name="city" size="30" maxlength="30"
value="<?php if (isset($_POST['city']))
echo $_POST['city']; ?>">
<br><label class="label" for="county">County*</label><input id="county"
type="text" name="county" size="30" maxlength="30"
value="<?php if (isset($_POST['county']))
echo $_POST['county']; ?>">
<br><label class="label" for="pcode">Post Code*</label><input id="pcode"
type="text" name="pcode" size="15" maxlength="15"
value="<?php if (isset($_POST['pcode']))
echo $_POST['pcode']; ?>">
<br><label class="label" for="phone">Telephone</label><input id="phone"
type="text" name="phone" size="30" maxlength="30"
value="<?php if (isset($_POST['phone']))
echo $_POST['phone']; ?>">
<p><input id="submit" type="submit" name="submit" value="Register"></p>
</form>
</div></div></div>
<?php include ('includes/footer.php'); ?>
<!--End of the registratation page content-->
</body>
</html>

Explanation of the Code

// Trim the title #1
$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;
}

The title is trimmed to remove any spaces from the beginning and end. Any HTML tags are then removed using the function strip_tags(). The stripped input is then run through the mysqli_real_escape_string() function for extra security. Next the input is checked for its length with the function mb_strlen(). If the string length is zero (less than one character), an error message is inserted in the $errors array. Note that I changed the variable’s name ($_POST['title')] at each step of the security checks; this is a convention I adopt to remind me that a change has occurred. The variable finally reverts to the name $title ready for entry into the database table.

This security-check pattern is repeated several times in the rest of the code. See if you can recognize it.

$e = FALSE; #2
// Has an email address has been entered?
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';
}
//remove spaces from the 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.';
}

Setting the variable as FALSE at the start of a set of statements is a common PHP pattern. This can reduce the amount of subsequent code required. The filter_var() function checks the e-mail address for validity. If it conforms to the correct pattern, the address is assigned to the variable $eready to be entered into the user’s record.

// Check that a password has been entered, if so, does it match the confirmed password
if (empty($_POST['psword1'])){ #3
$errors[] ='Please enter a valid password';
}

The code in line #3 checks whether the user has entered a password.

if(!preg_match('/^\w{8,12}$/', $_POST['psword1'])) { #4
$errors[] = 'Invalid password, use 8 to 12 characters and no spaces.';
} else{
$psword1 = $_POST['psword1'];
}

Note the exclamation mark in front of preg_match. This means “If the password has NOT been entered, put an error message into the $errors array.” The Regex function preg_match() checks the first password for unacceptable characters. The regex pattern uses the symbol \w to accept the characters 0–9, a–z and A–Z, but no spaces. It also ensures that it contains between 8 and 12 characters. For more information on preg_match, see:

http://php.net/manual/en/function.preg-match.php.

if($_POST['psword1'] == $_POST['psword2']) { #5
$p = mysqli_real_escape_string($dbcon, trim($psword1));
}else{
$errors[] = 'Your two password do not match.';
}

If the first password is in an acceptable format, a check is made to ensure that the first password matches the confirmation password. If the checks is satisfactory, the password is assigned to the variable $p ready to be entered into the user’s record. If the passwords do not match, an error message is placed in the $errors array.

// Trim the second address
$ad2 = trim($_POST['addr2']); #6
// Strip HTML and apply escaping
$stripped = mysqli_real_escape_string($dbcon, strip_tags($ad2));
// Get string lengths
$strlen = mb_strlen($stripped, 'utf8');
// Check stripped string
if( $strlen < 1 ) {
$ad2=NULL;
}else{
$ad2 = $stripped;
}

The pattern is similar to most of the other entries except that, should the form field be empty, no error message is displayed because the field is optional. The column for this field was set to NULL when the table was created; therefore, an empty field is acceptable.

// Has a phone number been entered? #7
if (empty($_POST['phone'])){
$ph=($_POST['phone']);
}
elseif (!empty($_POST['phone'])) {
//Remove all characters except numbers
$phone = preg_replace('/\D+/', '', ($_POST['phone']));
$ph=$phone;
}

Phone number entries may contain numbers, spaces, brackets, and hyphens. Only the numbers are valid; therefore, all characters except numbers are stripped out by the function preg_replace(). The code in the brackets allows numbers, but it replaces all other characters with nothing. For instance, if the user enters (01297) 877-111, the number is changed to 01297877111, which is an acceptable phone number for entry into the database table. If the user accidentally typed a letter within the phone number, the letter would be removed. This will also strip out ‘+’ characters from international numbers, which may be undesirable. You might be able to find a regex function that will cope with this problem.

For more information on preg_replace, see http://www.php.net/manual/en/function.preg-replace.php.

Note In the downloadable files, the links in the headers that have a Register menu button, now connect to the safer registration page.

Run XAMPP, and enter http://localhost/finalpost/safer-register-page.php in the address field of a browser. Then register some members, but make deliberate errors to see the results.

In the previous chapter, we created and listed a screen for searching addresses and phone numbers. We will now make use of that screen so that we can edit addresses and phone numbers.

Search for an Address and Telephone Number

The screen for searching an address/phone number was shown and listed in the last chapter. Figure 6-3 is shown again as a reminder.

Figure 6-3. Searching for an address or phone number

The code will not be repeated in this chapter because it was listed in Chapter 5. However, if you wish to review the code, it can be seen in the downloaded files for Chapter 6. Just to remind you, the code begins with a session to ensure that only the administrator can view and amend the member’s record. In this case, we have assumed that the administrator’s session is sufficient protection. Only the administrator can search for users’ records.

Listing 6-3. Creating the Search Address Page (search_address.php)

<?php
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>

The <form> section of the file has been amended in the downloadable file so that it connects to the file view_found_address.php as follows:

<form action="view_found_address.php" method="post">

We will now examine the screen for the retrieved addresses and phone numbers.

Note that the Delete column is absent from the table displayed in Figure 6-4 because the act of deleting is rather final; it must be done using the delete_record.php page, where the administrator is given a second chance to decide whether to delete the record.

Figure 6-4. The new table displays the address and telephone number of the selected member

Viewing the Retrieved Address and Phone Number

The screen shot shown in Figure 6-4 appeared at the end of Chapter 5, where I promised that the listing would be given in this chapter.

The code that displays the two new columns is shown in bold type in Listing 6-4.

Listing 6-4. Displaying a Table with Address and Phone Number (view_found_address.php)

<?php
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1)) {
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>View found address page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; }
table, tr { width:850px; } #1
</style>
</head>
<body>
<div id="container">
<?php include("includes/header-admin.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the page content-->
<h2>Search Result</h2>
<?php
// This script fetches selected records from the users table
require ('mysqli_connect.php'); // Connect to the database
echo '<p>If no record is shown, this is because of an incorrect or missing entry in
the search form.<br>Click the Search button and try again</p>';
$fname=$_POST['fname'];
$fname = mysqli_real_escape_string($dbcon, $fname
$lname=$_POST['lname'];
$lname = mysqli_real_escape_string($dbcon, $lname);
$q = "SELECT title, lname, fname, addr1, addr2, city, county, pcode, phone, user_id
FROM users WHERE lname='$lname' AND fname='$fname' ";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran, display the records
// Table headings #2
echo '<table>
<tr><td><b>Edit</b></td>
<td><b>Title</b></td>
<td><b>Last Name</b></td>
<td><b>First Name</b></td>
<td><b>Addrs1</b></td>
<td><b>Addrs2</b></td>
<td><b>City</b></td>
<td><b>County</b></td>
<td><b>Pcode</b></td>
<td><b>Phone</b></td>
</tr>';
// Fetch and display the records #3
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td><a href="edit_address.php?id=' . $row['user_id'] . '">Edit</a></td>
<td>' . $row['title'] . '</td>
<td>' . $row['lname'] . '</td>
<td>' . $row['fname'] . '</td>
<td>' . $row['addr1'] . '</td>
<td>' . $row['addr2'] . '</td>
<td>' . $row['city'] . '</td>
<td>' . $row['county'] . '</td>
<td>' . $row['pcode'] . '</td>
<td>' . $row['phone'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If it failed to run
// Display message
echo '<p class="error">The current users could not be retrieved. We apologize for
any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br>Query: ' . $q . '</p>';
} // End of the if ($result)
//Now display a figure for the total number of records/members
$q = "SELECT COUNT(user_id) FROM users";
$result = @mysqli_query ($dbcon, $q);
$row = @mysqli_fetch_array ($result, MYSQLI_NUM);
$members = $row[0];
mysqli_close($dbcon); //Close the database connection
echo "<p>Total membership: $members</p>";
?>
</div><!-- End of found address page -->
<?php include("includes/footer.php"); ?>
</div>
</body>
</html>

Explanation of the Code

table, tr { width:850px; } #1

This internal style increases the table width to accommodate the extra columns for the address and telephone number. This overrides the table style in the main style sheet.

// Table headings #2
echo '<table>
<tr><td><b>Edit</b></td>
<td><b>Title</b></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Addrs1</b></td>
<td><b>Addrs2</b></td>
<td><b>City</b></td>
<td><b>County</b></td>
<td><b>Pcode</b></td>
<td><b>Phone</b></td>
</tr>';

The column headings now include the member’s address and telephone number.

// Fetch and display the records #3
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td><a href="edit_address.php?id=' . $row['user_id'] . '">Edit</a></td>
<td>' . $row['title'] . '</td>
<td>' . $row['lname'] . '</td>
<td>' . $row['fname'] . '</td>
<td>' . $row['addr1'] . '</td>
<td>' . $row['addr2'] . '</td>
<td>' . $row['city'] . '</td>
<td>' . $row['county'] . '</td>
<td>' . $row['pcode'] . '</td>
<td>' . $row['phone'] . '</td>
</tr>';
}
echo '</table>'; // Close the table

The table is displayed and populated with the member’s address and telephone number. Note the link for editing the address/phone table is now edit_address.php. We will be creating this file now.

Edit Addresses and Telephone Numbers

When the record is found and the Edit link is clicked, the screen shown in Figure 6-5 appears. This allows the administrator to edit any field in the display. Note that the administrator cannot delete a record from the address/phone search because this must be done using the record displayed by the delete.php page. Deleting a record from the delete.php page automatically removes the address and phone details because they are part of the record.

Figure 6-5. The screen for editing fields in the address/phone display

In this screen, all the fields can be edited. The second address and the phone fields are optional—that is, they can remain empty if necessary. The code for the screen is given in Listing 6-5.

Listing 6-5. Creating a Screen for Editing the Address and Phone Number (edit_address.php)

<?php
session_start(); #1
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Edit an address or phone number</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; }
input.fl-left { float:left; }
#submit { float:left; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header-admin.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col-cards.php"); ?>
<div id="content"><!--Start of the page-for editing addresses and phone numbers-->
<h2>Edit an Address or Phone number</h2>
<?php
// Click the Edit link in view_found_address.php page and this 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'])) ) { // Form submission
$id = $_POST['id'];
} else { // If no valid ID, quit 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();
// Look for the title
if (empty($_POST['title'])) {
$errors[] = 'You forgot to enter the title.';
} else {
$title = mysqli_real_escape_string($dbcon, trim($_POST['title']));
}
// Look for the first name
if (empty($_POST['fname'])) {
$errors[] = 'You forgot to enter the first name.';
} else {
$fn = mysqli_real_escape_string($dbcon, trim($_POST['fname']));
}
// Look for the last name
if (empty($_POST['lname'])) {
$errors[] = 'You forgot to enter the last name.';
} else {
$ln = mysqli_real_escape_string($dbcon, trim($_POST['lname']));
}
// Look for the 1st address
if (empty($_POST['addr1'])) {
$errors[] = 'You forgot to enter the first address.';
} else {
$addr1 = mysqli_real_escape_string($dbcon, trim($_POST['addr1']));
}
// Look for the address 2
if (!empty($_POST['addr2'])) {
$addr2 = mysqli_real_escape_string($dbcon, trim($_POST['addr2']));
}else{
$addr2 = NULL;
}
// Look for the city
if (empty($_POST['city'])) {
$errors[] = 'You forgot to change the city.';
} else {
$city = mysqli_real_escape_string($dbcon, trim($_POST['city']));
}
// Look for the county
if (empty($_POST['county'])) {
$errors[] = 'You forgot to change the county.';
} else {
$county = mysqli_real_escape_string($dbcon, trim($_POST['county']));
}
// Look for the post code
if (empty($_POST['pcode'])) {
$errors[] = 'You forgot to enter the post code.';
} else {
$pcode = mysqli_real_escape_string($dbcon, trim($_POST['pcode']));
}
// Look for the phone number
if (!empty($_POST['phone'])) {
$phone = mysqli_real_escape_string($dbcon, trim($_POST['phone']));
}else{
$phone = NULL;}
if (empty($errors)) { // If the entries are 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
$q = "UPDATE users SET title='$title', fname='$fn', lname='$ln', 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 it ran OK.
// Echo a message if the edit was satisfactory:
echo '<h3>The user has been edited.</h3>';
} else { // Echo an error message if the query failed.
echo '<p class="error">The user was not edited due to a system error.
We apologize for the inconvenience.</p>'; // Error 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 if (empty($errors))section.
} // End of the conditionals
// Select the user's information
$q = "SELECT title, fname, lname, addr1, addr2, city, county, pcode, phone FROM users
WHERE user_id=$id";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_num_rows($result) == 1) { // Valid user ID, display the form.
// Get the user's information:
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Create the form
echo '<form action="edit_address.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="addr1">Address:</label><input class="fl-left" id="addr1"
type="text" name="addr1" size="30" maxlength="50" value="' . $row[3] . '"></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[4] . '"></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[5] . '"></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[6] . '"></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[7] . '"></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[8] . '"></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">The page was accessed in error.</p>';
}
mysqli_close($dbcon);
include ('includes/footer.php');
?>
</div>
</div>
</body>
</html>

Explanation of the Code

The majority of the code you will have seen before. The administrator is not allowed to view users’ passwords or secret user names. This explained snippet of code is a reminder of the importance of using sessions to protect a private page.

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>

If an unauthorized person tries to access this file, the session redirects him to the login page.

Note The listings for all the other files are not included in this chapter because most of them are exactly the same as those in Chapter 5. To view the code, see the downloadable files for Chapter 6.

Summary

In this chapter, we created a copy of the database from Chapter 5 using a new name and password. We then created the table. The process for importing an SQL file was described, and the chapter provided a quick method for creating tables. The traditional method of manually creating a table was given in case you need more practice.

Next we tidied the filing system by putting all the included files into a folder named includes. We discussed security, briefly mentioned sanitization, and investigated the term validation. We then created a new safer registration page. We learned how to create pages for searching and editing addresses and telephone numbers.

In the next chapter, you will learn how to make those inevitable last-minute changes and also how to migrate a database to a remote host. Advice will be given on how to back up tables.