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

Chapter 2. Create Web Pages That Interact with Users

This chapter will demonstrate how a simple database can be linked to a web page so that the page can interact with users. The general public will have access to the web site but will not have access to the database structure or the data contained in the tables. They will not be permitted to view the protected pages, such as a members-only page, because they have no password until they register. However, the web designer must provide a way for users to interact with the database in order to (i) register as a member, (ii) search the database, and (iii) change a password. The PHP language and MySQL code provide the solution.

This chapter covers the following main topics:

· Creating a folder for the database

· Creating the database and a template for the web site pages

· Introducing the PHP include function

· Creating the included pages

· How does the server process a PHP page?

· An interactive version of the template page

· Connecting to the database

· Creating a registration page for members

· The PHP keyword echo()

· Styling forms

· Sticky forms

· Using arrays

· Displaying members’ records

· Changing a password

· Dealing with apostrophes

· Testing the tutorials’ pages

· More about using arrays

We will use the simpleIdb database and the users table from the previous projects for our interactive web pages. Be aware that this tutorial is neither secure nor practical. It is a stepping stone to the more secure and ambitious projects described in subsequent chapters. In practice, you would never allow ordinary members to view a list of members. The interactive elements in this project are as follows:

· Users can register as members by inserting their details into a form displayed on the screen. The registration details would be entered into the database table and could be used by the administrator to send regular newsletters to members.

· Registered users can change their password.

· A user can view the list of members (for this project only). In later chapters, this facility would be available only to the webmaster and (to a limited extent) the membership secretary.

The features that make this example unsuitable for the real-world are as follows:

· No provision is made for registered members to subsequently log in to access a special section or page. This will be dealt with in Chapter 3.

· Users should never be able to access a table of members’ details.

· At this early stage, for simplicity, no filtering of the users’ information is provided. The table could therefore contain faulty data and bogus e-mail addresses.

· In this chapter only, any user who knows a member’s e-mail address could change the member’s password.

All these security issues will be dealt with in subsequent chapters. Despite the drawbacks, the project will provide you with valuable practice in using your XAMPP program to test the pages. You will also learn more database jargon and some basic PHP code.

Create the Folder for Holding the Database Pages

Within XAMPP’s htdocs folder, create a new folder named simpleIdb.

Note The upper case “I” between the letters “e” and “d” stands for Interactive.

All the pages created in this chapter will be placed within the simpleIdb folder. You have a choice between hand-coding the files from the listings supplied and loading the book’s code into the simpleIdb folder in htdocs. (Download the code from the book’s page at www.apress.com.) I recommend that you hand-code them for this chapter; the files are small and won’t delay you too much. You will learn more and learn faster if you type and test the code, especially if you make mistakes and learn to correct them.

Create the Temporary Template

Obviously, some aspects of an interactive database must be accessible to users. That means incorporating it into a real-world web page. We will name our web page template.php, and this is shown in Figure 2-1. As you can see, there is a main header with a graphic running behind it, some body text, a navigation sidebar on the left, an information column on the right, and a footer at the bottom of the page.

Figure 2-1. The template

The HTML5 Listing 2-1 for the template contains the DOCTYPE, a page title, and a link to a style sheet. The body of the page contains some PHP code, and this will be explained step by step at the end of the listing.

Because the file contains PHP code (no matter how little), the file is saved with the file type .php. The PHP code is indicated by bold type in Listing 2-1.

Listing 2-1. Creating a Template for the Project (template.php)

<!doctype html>
<html lang=en>
<head>
<title>Template for an interactive web page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id='container'>
<?php include('header-for-template.php'); ?>
<?php include('nav.php'); ?>
<?php include('info-col.php'); ?>
<div id='content'>
<h2>This is the Home Page</h2>
<p>The home page content. The home page content. The home page content. The home page
content. The home page content. <br>The home page content. The home page content. The
home page content. The home page content. <br>The home page content. The home page
content. <br>The home page content. The home page content. The home page content. </p>

</div>
</div>
<?php include('footer.php'); ?>
</body>
</html>

Note The <?php tag opens some PHP code, and the ?> closes the piece of PHP code.

The code in bold type uses the PHP include function, and this will now be explained. (Strictly speaking, the include() function is a PHP language construct, but the difference is so small that I will continue to call it a function for simplicity.)

Introducing the PHP include() Function

You will have noted that there does not seem to be enough code in Listing 2-1a to create the page displayed in Figure 2-1. Here is the reason why.

The four pieces of PHP code shown in bold type in Listing 2-1a have each pulled an additional file into the page. The page is therefore a combination of five files: a main file plus four external files. The four external files are pulled into the template page using the PHP include() function.

For updating and maintaining a web site, the include() function is a wonderful time saver. Let’s suppose you have a client web site with 40 pages, and each page needs the same block of menu buttons. If your client asks you to add or delete one menu button, normally you would have to amend 40 pages to add or delete the button on each page. Using the include() function, you would design the web site so that each page included the line of PHP code: <?php include('menu.php'); ?>. This would pull the block of menu buttons into to each web page. You would design the block of buttons in another file named, say, menu.php. To add a new button to all 40 pages, you would have to add the new button only to the one file called menu.php.

Note A PHP function is a tiny program that will perform a particular task. The include() function takes whatever is inside the brackets and pulls it into the page at the place where the include() function is located. PHP has two similar functions include() and require(). They both pull a file into a page so that the file is included in the displayed page. The difference is in the way they react to a missing or faulty file. If the file to be included is missing or corrupt, include() will not halt the execution of a page. A warning will appear, but the page will continue to load. In contrast, a fatal error will occur if require() can’t find the file or the file is faulty. In this case, the page will cease executing. Use include for most inclusions, but use require() for loading the details of a database because the page will be of little use if the database can’t be opened. Also, this makes the database more secure.

The four elements to be pulled into the page are (i) the header, (ii) the block of menu buttons, (iii) the info panel on the right side, and (iv) the footer. Included files can be any type—for instance, a .txt file, a .php file, or an .html file. An include() statement can be placed anywhere within an HTML page as long as it is surrounded by the PHP tags; these start with the tag <?php and close with the tag ?>. Examine one of the include() statements in bold type; note the quote symbols and the semi-colon are all very important. The details of the four included external files are explained next.

The Included Header File

Figure 2-2 shows a display of the included header file.

Figure 2-2. The header for the template

This header is temporary. When we create an interactive version of the template, the new header will contain an additional menu so that users can register and thereby insert their details into the simpleIdb database.

Caution Don’t be tempted to create the four non-interactive pages yet (page-2.php, page-3.php, page-4.php, and page-5.php) because, later in the chapter, these will require a new version of the header.

The internal style in Listing 2-2 refers to an image. You will find the image tile-pale.jpg in the images folder that you can download from the book’s page at www.apress.com. Place it in an images folder within the htdocs folder.

Listing 2-2. Creating the Code for the Temporary Header (header-for-template.php)

Note The styling for the header is temporarily located in the included files for simplicity. This will cause error messages when the page is run through the W3C Validator. The validator does not approve of styles placed within an included file because those included styles will appear within the <body></body> section of an HTML document; style tags must appear in the <head></head> section. Future chapters will remedy this by relocating the styles in either the main style sheet or in the head section of a page.

The Included Menu File

The included block of menu buttons is shown in Figure 2-3.

Figure 2-3. The included menu buttons

The listing contains an internal style for the layout of the menu block. This is not good practice because styling should never be included within an included file. It is bad because the included file will not validate using the w3.org validator. I used it here because it is easier to style this way to begin with. Later chapters will remove the style to the main style sheet thus allowing the included file to validate.

Some trial and error is usually required to position the included menu on the page. The code for the menu block is given in Listing 2-3.

Listing 2-3. Creating the Code for the Included Menu (nav.php)

The Included Information Column

The included information column sits on the right side of the page and is shown in Figure 2-4.

Figure 2-4. The information column for inclusion in the template

Some trial and error is usually required to position the included info column on the page.

The code for the information column is given in Listing 2-4.

Listing 2-4. Creating the Code for the Information Column (info-col.php)

Later chapters will remove the internal style to the main style sheet thus allowing the included file to validate.

The Included Footer File

The included footer is shown in Figure 2-5.

Figure 2-5. The footer for inclusion in the template

The code for the footer is given in Listing 2-5a.

Listing 2-5a. Creating the Code for the Included Footer (footer.php)

Later chapters will remove the internal style to the main style sheet thus allowing the included file to validate.

How Does the Server Process the Page?

When an HTML file is saved as a PHP file, the .php extension alerts the server so that it processes the HTML as normal, but it also looks out for any PHP code. The server is in HTML mode by default, and any HTML code is sent to the browser as normal. When it finds a <?php tag, the server switches to PHP mode and executes the PHP code or echoes information to the browser. It continues in PHP mode until it encounters the PHP closing tag ?>; it then switches back to HTML mode. This cyclic behavior continues until the end of the page of code.

Styling the Temporary Template

Always try to put the majority of the styling into one main style sheet. In this tutorial, I named the CSS style sheet includes.css. However, during development some internal CSS styling may be needed within each included file. (See the internal styles in the code snippets for the four included files.) For instance, the only place where an <h1> heading is needed is in the header, so as a temporary measure, I put the styling for <h1> in the included header file.

Later, the separate internal styles will be placed in the main style sheet. Because several pages will eventually display a data table, the main style sheet includes styling for <table> and <td>. The main style sheet contains styling for the various user input forms associated with the registration process, such as the <label> and <input> elements. The code for the style sheet is given in Listing 2-5b.

Listing 2-5b. Creating the Template’s 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;
}
#midcol { margin-left:140px; margin-right:140px;
}
#content { margin-left:150px; margin-right:150px;
}
table { width:500px; 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;
}
.label { float:left; width:210px; text-align:right; clear:left; margin-right:5px;
}
#submit { margin-left:215px;
}

The temporary template is currently non-interactive. We will now modify the template so that it is ready to become interactive. To test the file, run XAMPP and then enter the file’s URL in a browser using http://localhost/simpleIdb/template.php.

The Interactive Version of the Template

In this version of the template, we will introduce interactivity by creating a new header file with an additional menu. This menu will allow users to register and enter their details into the simpleIdb database. It will also allow users to change their password and view the table of members.

The new header is shown in Figure 2-6.

Figure 2-6. A registration menu is added to the header

The interactive element will be embedded in the header; therefore, the previous header is now modified to include a menu. The new header will be named header.php, and the code is shown in Listing 2-6. The interactive header menu is shown in bold type.

Listing 2-6. Placing a Registration Menu in the New Header (header.php)

In the template file, swap the previous included header for the new header. The new template page will be named index.php, and it will now have two blocks of menu buttons as shown in Figure 2-7.

Figure 2-7. The new home page template with two menus

The only difference in the code between the old and new templates is that the new header is used. The name of the new header file is shown in bold type in the following snippet of code at the beginning of the new template. The snippet of code is given in Listing 2-7.

Listing 2-7. Including the New Header in the Home Page (index.php)

<!doctype html>
<html lang=en>
<head>
<title>Template for the home page of an interactive database</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id='container'>
<?php include('header.php'); ?>
<?php include('nav.php'); ?>
<?php include('info-col.php'); ?>

Now create the four ordinary web site pages, using the new template; save four copies of the file index.php, naming the copies page-2.php, page-3.php, page-4.php, and page-5.php. Change the content of those pages a little so that they differ from index.php and also to indicate which page the user is viewing. When this is done, you will have some pages to visit.

Note The listing for the following interactive pages was inspired by code devised by Larry Ullman, who kindly gave me permission to adapt some of his code. His book PHP and MySQL for Dynamic Web Sites (Peachpit Press, 2011) is the most practical manual I have encountered so far. It is particularly good on database security. His web site and forum are always at the forefront of database development and can be seen at http://www.LarryUllman.com.

Connecting to the Database

Before we can do anything in the database, we must connect to it. This is achieved by creating a connection file that we will call mysqli_connect.php. The code for the connection file is given in the next snippet.

Listing for the Snippet of Code That Connects to the Database (mysqli_connect.php)

<?php
// This file provides the information for accessing the database.and connecting
to MySQL. It also sets the language coding to utf-8
// First we define the constants: #1
DEFINE ('DB_USER', 'horatio');
DEFINE ('DB_PASSWORD', 'hmsvictory');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'simpleIdb');
// Next we assign the database connection to a variable that we will call $dbcon: #2
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die ('Could not connect to MySQL: ' . mysqli_connect_error () ); #3
// Finally, we set the language encoding.as utf-8
mysqli_set_charset($dbcon, 'utf8'); #4

Save the file as mysqli_connect.php, and place it in the htdocs folder. When a database is set up on a remote host, this file is placed one level above the root folder for security. This is described in Chapter 7.

Explanation of the Code

The mysqli_connect.php file contains some code conventions that you may not be familiar with, so I’ll briefly run through them here.

Single line comments begin with a double forward slash or with a hash symbol—for example:

// we will set the language coding to utf-8

Or # we will set the language coding to utf-8

Constants are fixed definitions created by using the keyword DEFINE:

DEFINE ('DB_USER', 'horatio');

Variables like $dbcon are storage devices for containing information that can be made to vary, they are preceded by a dollar symbol, like $dbcon shown next. Variables are created using the following format:

$var = some_information

The equals symbol means assign; the example assigns some_information to the variable $var. In other words, the item on the right of the equals symbol is assigned to the variable on the left.

We will now examine the code using the line numbers as references.

// First we define the constants: #1
DEFINE ('DB_USER', 'horatio');
DEFINE ('DB_PASSWORD', 'hmsvictory');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'simpleIdb');
// Next we assign the database connection to a variable that we will call $dbcon: #2
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

The text following the dollar sign can be anything as long as it is relevant to the information held in the memory. For instance, the variable for connecting to our database in the listing is named $dbcon. This was chosen to signify a connection to a database; it could be $db_c, $connect_db, or any other text that indicates a connection to the database. We use the host, username, password, and database name that we defined earlier to connect to the database. The @ symbol in our code suppresses error messages and prevents them from being shown to the user. Some error messages can help malevolent persons find their way around your database.

OR die ('Could not connect to MySQL: ' . mysqli_connect_error () ); #3

Normally, MySQL will display an explicit error message, but we have suppressed it completely by using the @ symbol. You will still want to notify users if something has gone wrong, though. The statement “OR die” provides an innocuous error message. If the file fails to connect to the database, the web site designer is alerted without a breach of security; however, the designer is warned so that the problem can be investigated and fixed.

mysqli_set_charset($dbcon, 'utf8'); #4

The final line in the code sets the encoding for the language to utf-8.

Note The code is different from the code set in an HTML document because it does not include the hyphen as in utf-8.

Next, we need some pages for the header’s new menu to work with. These pages will contain the interactive features. The pages will (i) allow users to register, (ii) allow users to view a table of the registered persons, and (iii) permit a password to be changed.

The Registration Page

Caution When you finally migrate a database to a remote host, be sure to comply with the Data Protection Act for your territory, and state clearly on the registration page that the user’s personal details will not be shared or sold to other organizations. The rules covering the protection of data vary from country to country, and it is essential that you read them and comply with them. Usually, any person within an organization who can access users’ details must sign a document agreeing never to share personal information. An annual registration fee is payable to the government organization that regulates the data protection law. These precautions do not apply to experimental databases using fictitious data such as those described in this book.

The registration page allows users to enter their personal details directly into a table in the database. The interface is shown in Figure 2-8.

Figure 2-8. The registration page

When the user clicks the Register menu button on the new header, the page shown in Figure 2-8 is displayed. If the user fills out the form correctly and then clicks the Register button (below the entry fields), the user’s entries are entered in the users table in the database. A Thank you page is then displayed.

Note that the Register and New Password buttons on the registration page are now redundant because the user has already accessed the registration page. Obviously, he or she does not yet have a password to change. The redundant buttons will be left in place for all the examples in this chapter to avoid complicating the instructions. The redundant buttons will be removed or changed in the next two chapters.

Now we will examine the code for the entire registration page. The new header is included in bold type in Listing 2-8.

Caution If you type the listings in your text editor, do not include the line numbers (#1, #2, #3, and so on). These are provided to help you understand the explanation of the code given at the end of each listing. If you download the files from the book’s web site page atwww.apress.com, you will find that the line numbers are omitted from those files.

Listing 2-8. Creating the Complete Registration Page (register-page.php)

<!doctype html>
<html lang=en>
<head>
<title>Register page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p.error { color:red; font-size:105%; font-weight:bold; text-align:center; }
</style>
</head>
<body>
<div id="container">
<?php include("header.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<p>
<?php
// This script performs an INSERT query that adds a record to the users table.
if ($_SERVER['REQUEST_METHOD'] == 'POST') { #1
$errors = array(); // Initialize an error array.
// Was the first name entered?
if (empty($_POST['fname'])) {
$errors[] = 'You did not enter your first name.';
}
else { $fn = trim($_POST['fname']);
}
// Was the last name entered?
if (empty($_POST['lname'])) {
$errors[] = 'You did not enter your last name.';
}
else { $ln = trim($_POST['lname']);
}
// Was an email address entered?
if (empty($_POST['email'])) {
$errors[] = 'You did not enter your email address.';
}
else { $e = trim($_POST['email']);
}
// Did the two passwords match? #2
if (!empty($_POST['psword1'])) {
if ($_POST['psword1'] != $_POST['psword2']) {
$errors[] = 'Your passwords were not the same.';
}
else { $p = trim($_POST['psword1']);
}
}
else { $errors[] = 'You did not enter your password.';
}
//Start of the SUCCESSFUL SECTION. i.e all the fields were filled out
if (empty($errors)) { // If no problems encountered, register user in the database #3
require ('mysqli_connect.php'); // Connect to the database. #4
// Make the query #5
$q = "INSERT INTO users (user_id, fname, lname, email, psword, registration_date)
VALUES (' ', '$fn', '$ln', '$e', SHA1('$p'), NOW() )"; #6
$result = @mysqli_query ($dbcon, $q); // Run the query. #7
if ($result) { // If it ran OK. #8
...header ("location: register-thanks.php"); #9
exit(); #10
//End of SUCCESSFUL SECTION
...}
else { // If the form handler or database table contained errors #11
// Display any error message
....echo '<h2>System Error</h2>
<p class="error">You could not be registered due to a system error. We apologize for any
inconvenience.</p>';
// Debug the message:
....echo '<p>' . mysqli_error($dbcon) . '<br><br>Query: ' . $q . '</p>';
} // End of if clause ($result)
mysqli_close($dbcon); // Close the database connection.
// Include the footer and quit the script:
include ('footer.php');
exit();
}
else { // Display the errors
echo '<h2>Error!</h2>
<p class="error">The following error(s) occurred:<br>';
foreach ($errors as $msg) { // Print each error. #12
echo " - $msg<br>\n";
}
echo '</p><h3>Please try again.</h3><p><br></p>';
}// End of if (empty($errors)) IF.
} // End of the main Submit conditional.
?>
<h2>Register</h2> #13

<form action="register-page.php" method="post">
<p><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']; ?>"></p>
<p><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']; ?>"></p>
<p><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']; ?>" > </p>
<p><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']; ?>" >
Between 8 and 12 characters.</p>
<p><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']; ?>" ></p>
<p><input id="submit" type="submit" name="submit" value="Register"></p>
</form>
<?php include ('footer.php'); ?></p>
</div>
</div>
</body>
</html>

Note At this point, beginners may be mystified because they are more familiar with code that steps through a page from top to bottom. In the preceding example of interactive code, the beginner would expect the form fields to be at the top of the page of code. In fact, they come last in the listing. With a server-side script like PHP, the screen display is the last item to be actioned. The server scoops up the PHP code first and holds it in the server’s memory. The HTML form fields are then displayed by the user’s browser. When the user enters something into the fields, the input goes into the server’s memory; there the PHP code checks it and deals with any problems. It then waits for the submit button to be clicked. When the submit button is clicked, the user’s input (if it is correct) is inserted into the database table. If the input is incorrect, the server sends an error message to the user’s browser.

Explanation of the Code

You can skip the fine details of the explanations if you would rather press on with creating and experimenting with the database tutorials. However, I strongly urge you to at least look for patterns in the explanations and make sure you grasp the logic of them.

For instance, one recurring pattern is the conditional that has the following format:

If a certain item has been set or if a certain condition exists

Do this

Or else (i.e., if the condition does not exist)

Do that

Later, when you want to adapt the tutorials for your own web sites, you will no doubt be motivated to discover the details of how the PHP code works.

Many of the PHP statements were explained by comments in the listings, but some items need further comment. The <form> section looks horribly complicated, but that will also be explained. For readers who wish to understand the code right now, the explanations are as follows:

if ($_SERVER['REQUEST_METHOD'] == 'POST') { #1
$errors = array(); // Initialize an error array.

The array is initiated and given the name $errors. The array is a variable that can store multiple error messages. These multiple messages will be displayed by the file’s subsequent code. Some additional information on arrays and their use is given at the end of this chapter.

// Was the first name entered?
if (empty($_POST['fname'])) {

If the first name fname field is empty, then...

$errors[] = 'You did not enter your first name.';

A suitable error message is inserted into the array. Note the square brackets:

} else {

Or else, if the field has been correctly filled out...

$fn = trim($_POST['fname']);
}

An element in a global variable such as $_POST['fname'] is always enclosed in square brackets. This is a feature of global variables. The code beginning $fn = trim is an instruction to trim (remove) any spaces from the beginning and end of the user’s fname entry. Spaces at the beginning and end of input data are unwanted characters. The trimmed fname is then assigned to the variable $fn.

Note Any variable taking the $_POST[ ] format, such as $_POST['fname'], is a global variable—it is accessible to any page on the web site. Ordinary variables have the format $fname, and they can be accessed only by loading the page in which they appear.

// Did the two passwords match? #2
if (!empty($_POST['psword1'])) {
if ($_POST['psword1'] != $_POST['psword2']) {
$errors[] = 'Your passwords were not the same.';
}
else {
$p = trim($_POST['psword1']);
}
}

Note that the second line uses an exclamation mark and the equals character (!=) to denote not equal to. If the password in field 1 and the password in field 2 are not the same (not equal to), then the error message will be added to the errors array. This is shown in the earlier code in bold type:

if (empty($errors)) { // If no problems encountered, register user in the database #3

This block of code is in the successful section. This section starts the registration process when all the fields have been filled out correctly—in which case, the $errors array is empty. The line is saying, “Because there are no errors, we can connect to the database and insert the user’s data.”

require ('mysqli_connect.php'); // Connect to the database. #4

In this line, the database connection is made with require() instead of include() so that the script will run if the connection is made, but it will not run if no connection is available. This is because there is no point continuing if the database is not accessible because data cannot be inserted into its table. We use require() when something vital is to be included and data needs to be protected from hackers.

// Make the query: #5
$q = "INSERT INTO users (user_id, fname, lname, email, psword, registration_date)

Assuming that a successful connection to the database is achieved, this line prepares the data for entry into the table called users. The word query sometimes means “query,” but most often it means “Do something.” In this case, it means “Insert a new record using the following data....” The line is saying, “Insert into the table named users, under the column headings labeled user_id, fname, and so on....” The query is assigned to a variable $q. Line #5 and line #6 are, in fact, SQL queries. This demonstrates how well HTML, PHP, and SQL work together.

VALUES (' ', '$fn', '$ln', '$e', SHA1('$p'), NOW() )"; #6

This piece of SQL code describes the VALUES to be inserted. The user’s entries in the registration form provide these values. Note that the first value is deliberately empty because it is the field that is automatically incremented and entered by MySQL, not by the user.

What are SHA1('$p') and NOW() in line #6 ? They are pieces of MySQL code; SHA1('$p') is a function for protecting a password. The acronym SHA stands for Secure Hash Algorithm, which is a security measure created by the US National Security Agency. It instructs MySQL to encode the password by converting it to a string 40 characters in length (no matter what length of password the user enters into the form). This explains why the password’s attribute in the users table was set to CHAR(40). A hacker would have no idea which part of the 40-character string contains the password. In addition, the whole string would be thoroughly scrambled. The function NOW() instructs MySQL to automatically enter today’s date into the registration date field:

$result = @mysqli_query ($dbc, $q); // Run the query. #7

This line runs the query. It puts the details into the specified columns. The result is assigned to the variable $result:

if ($result) { // If it ran without a problem #8
header ("location: register-thanks.php"); #9
exit(); #10

Line #8 means, “If the operation was successful.”

Lines #9 and #10 mean “Go to the page called register-thanks.php and quit the current page.”

Or, in other words, “Quit the registration page, and display the Thank you page.”

else { // If the form handler or database table contained errors #11
// Display an error message
echo '<h2>System Error</h2>
<p class="error">You could not be registered due to a system error.
We apologize for any inconvenience.</p>';
// Debug information
echo '<p>' . mysqli_error($dbcon) . '<br><br>Query: ' . $q . '</p>';

If an error in the database or table is encountered, display the error

foreach($errors as $msg) { // Echo each error #12
echo " - $msg<br>\n";

This line is a foreach loop. It cycles through the $errors array, and if it finds any error messages, each one is memorized and then displayed (echoed) on the screen (foreach is one word without a space).

The code following line #13 is explained in the “Styling the Forms” section later in the chapter.

The PHP Keyword echo

The keyword echo appears in code lines #11 and #12. It is the PHP way of telling a browser to “Display something on the screen.” Some designers use the alternative keyword print, but this book will use echo because I have found that beginners confuse this with the command for sending something to an inkjet or laser printer; hard-copy printing is something that PHP cannot do.

Note Any HTML code that you want can be placed in an echo statement. The echo writes the content into the HTML document (virtually, not literally) so that it can be displayed in a browser.

Beginners can be puzzled by the behavior of echo when a line space is required. For instance, the following code displays no line space:

echo "I found that PHP ";
echo "was much easier to learn than Perl";

Browsers display this as follows:

I found that PHP was much easier to learn than Perl

To push the second line down, you must insert the line-break tag <br> as follows:

echo "I found that PHP<br>";
echo "was much easier to learn than Perl";

Browsers display this as follows:

I found that PHP

was much easier to learn than Perl

Tip Lines #7 through #9 show the standard PHP method for redirecting to a new page. Its format is as follows:if($result) { // If it ran without a problem.header ("location: register-thanks.php");exit();

The thankyou.php page is shown and explained later in the chapter.

Styling the Form Fields

An unformatted registration form is neither attractive nor helpful, because the fields are not neatly arranged on the page. Figure 2-9 shows the raw form. Figure 2-10 shows the form when CSS formatting is applied.

Figure 2-9. The unformatted form

Figure 2-10. The form when formatted

The HTML code for labels and fields in HTML forms can be quite simple, something like this:

<p><label for="fname">First Name:</label>
<input id="fname" type="text" name="fname" size="30" maxlength="30"></p>

Our form fields are much more complicated. This will be explained later. (See the “Sticky Forms” section.) Meanwhile, we will concentrate on formatting the labels and fields using CSS.

The input fields in the unformatted version in Figure 2-9 were created by the following code:

<form action="register-page.php" method="post">
<p><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']; ?>"></p>
<p><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']; ?>"></p>
<p><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']; ?>" > </p>
<p><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']; ?>" >
Between 8 and 12 characters.</p>
<p><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']; ?>" ></p>
<p><input type="submit" name="submit" value="Register"></p>
</form>

To format the fields, we need to add identifiers so that the main CSS sheet called includes.css can target those identifiers. The added identifiers class="label" are in bold type in the following snippet:

<form action="register-page.php" method="post">

<p><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']; ?>"></p>

<p><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']; ?>"></p>

<p><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']; ?>" > </p>

<p><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']; ?>" >
Between 8 and 12 characters.</p>

<p><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']; ?>" ></p>

<p><input id="submit" type="submit" name="submit" value="Register"></p>
</form>

If you look back at the main style sheet includes.css, you will see that the last two instructions style the form by using the class named label. The submit button is also styled using the id named submit in the HTML code and #submit in the style sheet. The two styles are as follows:

.label { float:left; width:210px; text-align:right; clear:left; margin-right:5px;
}
#submit { margin-left:215px;
}

Most of the code or fields and labels will be familiar to you, but what about that complicated PHP code enclosed in the values?

value="<?php if (isset($_POST['fname'])) echo $_POST['fname']; ?>"></p>

This feature provides a sticky form for displaying and correcting error messages.

Sticky Forms

In the registration page, if the user makes a mistake or forgets to fill out a field, the error messages appear and the form fields are redisplayed. Users would not mind amending the incorrect bit, but they would be rather annoyed if all the correct fields also needed filling out again. A sticky form retains the entries in the fields so that only the incorrect entries need to be edited by the user.

The formatted <label> and <input> statements in the form section are usually not as complicated as our code. They normally look something like this:

<form action="register-page.php" method="post">

<p><label class="label" for="fname">First Name:</label>
<input id="fname" type="text" name="fname" size="30" maxlength="30"></p>

In our tutorial, the additional and rather complicated bit is shown in bold type as follows:

<form action="register-page.php" method="post">

<p><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']; ?>"></p>

The PHP code (in bold type) remembers the previous entries and displays (echoes) them in the reloaded form fields.

Explanation of the Code

The value is the text to be shown in the field when the form is redisplayed.

The code segment if (isset($_POST['fname'])) is saying, “If the first name has been set (entered) by the user, then . . .”

The echo command redisplays the content that was previously entered by the user. This content is stored in the global variable $_POST['fname']; if users enter their registration details correctly, a thank you page is displayed. We will discuss this next.

The Thank You Page

The Thank you page is show in Figure 2-11.

Figure 2-11. The Thank You page

The registration page calls up the thank you page, and this is shown in the next code snippet in bold type. You will find this in the successful section of the registration page’s code. For convenience, I repeat it here:

//Start of SUCCESSFUL SECTION i.e all entries were correct
if (empty($errors)) { // If everything's OK, register the user in the database
require ('./mysqli_connect.php'); // Connect to the database.
// Make the query:
$q = "INSERT INTO users (user_id, fname, lname, email, psword, registration_date)
VALUES (' ', '$fn', '$ln', '$e', SHA1('$p'), NOW() )";
$result = @mysqli_query ($dbc, $q); // Run the query.
if ($result) { // If it ran without a problem
header ("location: register-thanks.php");
exit();
//End of SUCCESSFUL SECTION

The entire thank you page code is given in Listing 2-11.

Listing 2-11. Creating the Thank You Page (register-thanks.php)

<!doctype html>
<html lang=en>
<head>
<title>Registion thank you page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id="container">
<?php include("header.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>Thank you for registering</h2>
<p>The thank you page content. The thank you page content. The thank you page content.<br>
The thank you page.The thank you page content. The thank you page content.<br>The thank
you page content. The thank you page. The thank you page content.<br>The thank you page
content. The thank you page content. The thank you page content</p>

</div>
</div>
<?php include("footer.php"); ?>
</body>
</html>

We initiated a PHP array to contain all the error messages. We will now use that array to display error messages.

Displaying Error Messages That Are Collected in an Array

If the user enters nothing in all the fields and then clicks the Register button, four errors will be created. The corresponding error messages are inserted into the errors array. These are then displayed so that the user knows what he or she has done wrong. It would be annoying if only the first error was shown and then, after clicking the Register button a second time, the second error was displayed, and so on.

Figure 2-12 shows all the errors that would be displayed if the user failed to fill out all the fields in the form.

Figure 2-12. The errors displayed if the user failed to enter any data

The errors are displayed on the Registration page, and a sticky form is used to retain the user’s entries. In the extreme case shown in Figure 2-12, the user entered nothing and then clicked the Register button.

Viewing Members’ Records

When a user has registered, the web site designer can use phpMyAdmin to view the table and its entries. Access phpMyAdmin, and click the relevant database and table. Then click the Browse tab. Let’s suppose a user registered last year on September 20, 2012 and she entered the following information:

First name: Rose Last name: Bush, Email: rbush@myisp.co.uk, and Password: redblooms

phpMyAdmin will allow you to view each record. Access phpMyAdmin, select the simpleIdb database, click the users table, and then click the Browse tab. Figure 2-13 shows Rose Bush’s entries.

Figure 2-13. Viewing a record in phpMyAdmin

Note that the password redblooms is encrypted as 0f2ded3794a9f1ae11c2aba8ff6dff00dd1e4ac6.

Often web site designers and database administrators need to view a table of database entries created by all the registered users. The next item provides the information for doing this.

The View Users Page

When the View Users button is clicked, a table of registered users is displayed as shown in Figure 2-14.

Figure 2-14. A table of users is displayed

The table is displayed when the user clicks the View Users button on the top-right menu. The code for displaying the table of users is given in Listing 2-14.

Listing 2-14. Displaying a Table of Registered Members on the Screen (register-view-users.php)

<!doctype html>
<html lang=en>
<head>
<title>View users page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id="container">
<?php include("header.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>These are the registered users</h2>
<p>
<?php
// This script retrieves all the records from the users table.
require('mysqli_connect.php'); // Connect to the database.
// Make the query: #1
$q = "SELECT CONCAT(lname, ', ', fname) AS name,
DATE_FORMAT(registration_date, '%M %d, %Y') AS regdat FROM users
ORDER BY registration_date ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query.
if ($result) { // If it ran OK, display the records.
// Table header. #2
echo '<table>
<tr><td><b>Name</b></td><td><b>Date Registered</b></td></tr>';
// Fetch and print all the records: #3
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr><td>' . $row['name'] . '</td><td>' . $row['regdat'] . '</td></tr>'; }
echo '</table>'; // Close the table so that it is ready for displaying.
mysqli_free_result ($result); // Free up the resources.
} else { // If it did not run OK.
// Error message:
echo '<p class="error">The current users could not be retrieved. We apologize
for any inconvenience.</p>';
// Debug message:
echo '<p>' . mysqli_error($dbcon) . '<br><br>Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection.
?>
</div>
<?php include("footer.php"); ?>
</div>
</body>
</html>

Explanation of the Code

You will have seen most of the code before, but here is an explanation of the new items:

// Make the query: #1
$q = "SELECT CONCAT(lname, ', ', fname) AS name,
DATE_FORMAT(registration_date, '%M %d, %Y') AS regdat FROM users
ORDER BY registration_date ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query.

The SQL query selects and strings together (concatenates) the last name, then a comma, then the first name, and then the registration date. It sets the temporary headings for these as name and regdat. It rearranges the registration dates in the format month-day-year. It then states that the information should be extracted from the users table. Finally, it requests that each row of information be displayed in ascending date order (oldest first); the code for this is ASC for ascending. To display the records with the latest registrations first (descending order), you use DESC instead of ASC:

// Table header. #2
echo '<table><tr><td><b>Name</b></td><td><b>Date Registered</b>
</td></tr>';

This block of code displays (echoes) the first row of the table on the screen. The headings are displayed in bold type in the code and are made visible on the screen; the two headings are Name and Date Registered.

// Fetch and print all the records: #3
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td>' . $row['name'] . '</td>
<td>' . $row['regdat'] . '</td>
</tr>';
}
echo '</table>'; // Close the table.

This block of code loops through the rows and displays them while rows are available to display. Then, when no more rows are available, the table is closed.

Users sometimes wish to change their password. The next section demonstrates how this is done.

The Change Password Page

When the user clicks the “New Password” button on the menu at the top right of the header, the form shown in Figure 2-15 appears.

Figure 2-15. The change password form

The form is suitable only if the user knows her current password and e-mail address. If she has forgotten her password, a different approach is needed that will be discussed in a later chapter. However, this “new password” page is useful if the user does not like the password she originally chose. The code for the change password form is given in Listing 2-15.

Listing 2-15. Creating a Page to Allow Users to Change a Password (register-password.php)

<!doctype html>
<html lang=en>
<head>
<title>Change password</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p.error { color:red; font-size:105%; font-weight:bold; text-align:center; }
</style>
</head>
<body>
<div id="container">
<?php include("header.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<?php
// This page lets users change their password.
// Was the submit button clicked?
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
require ('mysqli_connect.php'); // Connect to the db.
$errors = array(); // Initialize the error array.
// Check for an email address:
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter your email address.';
} else {
$e = mysqli_real_escape_string($dbcon, trim($_POST['email'])); #1
}
// Check for the current password:
if (empty($_POST['psword'])) {
$errors[] = 'You forgot to enter your current password.';
} else {
$p = mysqli_real_escape_string($dbcon, trim($_POST['psword']));
}
// Check for a new password and match against the confirmed password:
if (!empty($_POST['psword1'])) {
if ($_POST['psword1'] != $_POST['psword2']) {
$errors[] = 'Your new password did not match the confirmed password.';
} else {
$np = mysqli_real_escape_string($dbcon, trim($_POST['psword1']));
}
} else {
$errors[] = 'You forgot to enter your new password.';
}
if (empty($errors)) { // If no problems occurred
// Check that the user has entered the right email address/password combination:
$q = "SELECT user_id FROM users WHERE (email='$e' AND psword=SHA1('$p') )";
$result = @mysqli_query($dbcon, $q);
$num = @mysqli_num_rows($result);
if ($num == 1) { // Match was made.
// Get the user_id:
$row = mysqli_fetch_array($result, MYSQLI_NUM);
// Make the UPDATE query:
$q = "UPDATE users SET psword=SHA1('$np') WHERE user_id=$row[0]";
$result = @mysqli_query($dbcon, $q);
if (mysqli_affected_rows($dbcon) == 1) { // If the query ran without a problem
// Echo a message
echo '<h2>Thank you!</h2>
<h3>Your password has been updated.</h3>';
} else { // If it encountered a problem
// Error message
echo '<h2>System Error</h2>
<p class="error">Your password could not be changed due to a system error.
We apologize for any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br /><br />Query: ' . $q . '</p>';
}
mysqli_close($dbcon); // Close the database connection.
// Include the footer and quit the script (to not show the form).
include ('footer.php');
exit();
} else { // Invalid email address/password combination.
echo '<h2>Error!</h2>
<p class="error">The email address and password do not match those on file.</p>';
}
} else { // Report the errors.
echo '<h2>Error!</h2>
<p class="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) { // Print each error.
echo " - $msg<br />\n";
}
echo '</p><p class="error">Please try again.</p><p><br /></p>';
} // End of if (empty($errors))
mysqli_close($dbcon); // Close the database connection.
} // End of the main Submit conditional
?>

<h2>Change Your Password</h2>
<form action="register-password.php" method="post">
<p><label class="label" for="email">Email Address:</label>
<input id="email" type="text" name="email" size="40" maxlength="60"
value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>" > </p>
<p><label class="label" for="psword">Current Password:</label>
<input id="psword" type="password" name="psword" size="12" maxlength="12"
value="<?php if (isset($_POST['psword'])) echo $_POST['psword']; ?>"></p>
<p><label class="label" for="psword1">New Password:</label>
<input id="psword1" type="password" name="psword1" size="12" maxlength="12"
value="<?php if (isset($_POST['psword1'])) echo $_POST['psword1']; ?>"></p>
<p><label class="label" for="psword2">Confirm New Password:</label>
<input id="psword2" type="password" name="psword2" size="12" maxlength="12"
value="<?php if (isset($_POST['psword2'])) echo $_POST['psword2']; ?>"></p>
<p><input id="submit"type="submit" name="submit" value="Change Password"></p>
</form>
<?php include ('footer.php'); ?></p>
</div>
</div>
</body>
</html>

Explanation of the Code

$e = mysqli_real_escape_string($dbcon, trim($_POST['email'])); #1

This line of code prevents apostrophes from causing the script to stop running. The code also removes most of the characters in the users’ input that would conflict with MySQL.

Take note of this pattern in line #1; we will be using it extensively throughout the book. The function mysqli_real_escape_string(the connection variable, trim()) removes undesirable characters from a string (string means “some text”). This prevents some forms of database attacks by hackers. Without this function, mischief-makers could log in without a valid e-mail or password. In the example shown, the global variable $_POST['email'] is trimmed—that is, any spaces are removed from the beginning and end of the e-mail address. Then it is located as the second argument after the connection to the database $dbcon. The whole lot is then cleansed of undesirable characters by the function mysqli_real_escape_string().

The user would be pleased to see confirmation that the password was changed. This is demonstrated next.

Confirming a Successful Password Change

If the password change is successful, the page shown in Figure 2-16 is displayed.

Figure 2-16. The password has been changed

The page shown in Figure 2-16 is not a new page. It is the current “New Password” page with the form fields removed automatically. The echoed message “Thank you” replaces the form fields.

Dealing with an Apostrophe

Some characters that are input by a user can cause a page to stop working.

I recommend that you try this experiment to learn about escaping (converting) problem characters. Problem characters are characters that act as both code and text—for instance, an apostrophe is text, but to PHP it is also code because it signifies the start and finish of a piece of text. Escaping the apostrophe tells PHP that the problem character is not to be treated as code.

Try registering the following new member using the details in Table 2-1. His name contains an apostrophe that will halt the script:

Table 2-1. Register a member who has an apostrophe in his name

Because O’Brien contains an apostrophe, you will receive an error message and the page will halt. PHP assumes that the apostrophe is code; therefore, the page stops when it arrives at the unexpected apostrophe.

Tutorial Dealing with the Apostrophe

We were unable to register names that included an apostrophe, such as O’Brien. We will now examine this problem more closely.

The function mysqli_real_escape_string($dbcon, $variable); was used in the last example, register-password.php. If we add this function to the registration file register-page.php, it will allow users to enter names with apostrophes.

Open your htdocs folder, and save a copy of register-page.php with the new name register-page-apos.php. This is a backup in case you mess up the original page.

In the new file register-page-apos.php, find this line:

else { $ln = trim($_POST['lname']);
}

Change the line to

else {$ln = mysqli_real_escape_string($dbcon, trim($_POST['lname'])) ;
}

IMPORTANT: The new function will work only if the language encoding is utf-8 and the database connection is opened before the function is called. The language is already set to utf-8 in the database connection file and within the meta tag (shown in bold). Next, in the file register-page-apos.php, move the database connection line require ('mysqli_connect.php') to the start of the PHP code. This is shown in bold in the partial listing for the register-page-apos.php file. The file is not included in the downloads because it is assumed that you will create it, a part of the code is given in the next snippet.

Listing A Snippet Showing the Amendments to the Registration Page (register-page-apos.php)

<!doctype html>
<html lang=en>
<head>
<title>Register page for apostrophes</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id="container">
<?php include("header.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<p>
<?php
//The link to the database is moved here to the top of the PHP code.
require ('mysqli_connect.php'); // Connect to the db.
// This query INSERTs a record in the users table.

Scroll down to find the piece of code listed next, and change the line as shown in bold type:

// Check for a last name:
if (empty($_POST['lname'])) {
$errors[] = 'You forgot to enter your last name.';
} else {
$ln = mysqli_real_escape_string($dbcon, trim($_POST['lname']));
}

Scroll down to find the piece of code listed next, and delete the line shown in bold type (or, as I have done, add two forward slashes to the front of the line to comment it out):

//Start of the SUCCESSFUL SECTION. i.e all the fields were filled out
if (empty($errors)) { // If everything's OK, register the user in the database
//require ('mysqli_connect.php'); // Connect to the database.

Table 2-2. The Details for Registering Paddy O’Brien

Save the file with the new name register-page-apos.php. Fire up XAMPP, and in a browser type this into the address field: http://localhost/simpleIdb/register-page-apos.php. Then try registering Paddy O’Brien again using the details shown in Table 2-2. :

The registration should be accomplished successfully, and the apostrophe should be present when you view the name O’Brien using phpMyAdmin.

Note Using the two functions, mysqli_real_escape_string() and trim()is an important security step to prevent crashes and to ensure that clean data is inserted into the database table. Among the characters that are escaped by the function are the following: ‘, “, \, ASCII 0, \n, \r, \control+z.Since the advent of PHP 5.3, the function mysqli_real_escape_string() replaces the deprecated mysql_ escape_string() and addslashes().

The function has the format mysqli_real_escape_string(connection variable, trim(string)). It converts the apostrophe by escaping it. The function looks for apostrophes, and it then places a backslash in front of the apostrophe like this: O\’Brien. The backslash escapes the apostrophe—in other words, it converts the apostrophe so that PHP does not regard it as code.

You will notice that there are two items (called “arguments”) within the brackets: the database connection code $dbcon is the first item, and the user’s input is the second item.

Note From here forward in this book, all user input will be cleaned before being used in a database query. PHP has several ways of cleaning input, and these will be revealed and explained as we work through subsequent chapters. Since PHP version 5.2, new methods of cleaning and validating input have become available, and these will be used in Chapters 3 through 5, and a full explanation of several security measures will be given in Chapter 6.

Apostrophes Within PHP code

User input is not the only source of apostrophes, they can be introduced by the web site designer. Apostrophes can produce a PHP parsing error when they occur within a block of PHP code because they will be interpreted as code by the parser and the database. Examine this snippet for example:

<?php
echo '<h2>Welcome to the Members' Page ';
if (isset($_SESSION['fname'])){
echo "{$_SESSION['fname']}";
}
echo '</h2>';
?>

The apostrophe after the word “Members” will result in an error message saying that there is an unexpected word Page within the line that contains the apostrophe.

To suppress the error and continue running the code, the apostrophe must be “escaped.” This is achieved by means of a backslash. The backslash instructs the PHP parser “The apostrophe is not the end of a statement, therefore accept the apostrophe and display it.” The backslash has been included in the next snippet:

<?php
echo '<h2>Welcome to the Members\' Page ';
if (isset($_SESSION['fname'])){
echo "{$_SESSION['fname']}";
}
echo '</h2>';
?>

We will now add some users so that we can use a browser to display a table of registered users.

Testing the Tutorial’s Pages

To see the interactive pages working, double-click the XAMPP icon on your desktop. When the control panel appears, check that Apache and MySQL are running, and then minimize the XAMPP control panel. Type http://localhost/simpleIdb/index.php into the address field of a browser, and then click the Register button so that you can enter some users.

To save you time and the effort of dreaming up fictitious persons, use the suggestions provided in Table 2-3.

Table 2-3. Suggesions for Entering Members’ Details.

Name	Email	Password
Mike Rosoft	miker@myisp.com	willgates
Olive Branch	obranch@myisp.co.uk	anoilyone
Frank Insence	finsence@myisp.net	perfume
Annie Versary	aversary@myisp.com	birthdaygirl
Terry Fide	tfide@myisp.de	scaredstiff
Rose Bush	rbush@myisp.co.uk	redblooms

Now that you have some more data in your users table, run XAMPP and use a browser to try the menu to display the table of registered users.

Tip No doubt, you will encounter error messages as you create code and test it. For help, refer to the troubleshooting tips in Chapter 12.

Earlier in this chapter, I promised more information on the use of PHP arrays. The next section will help you understand additional aspects of this most useful feature.

More About Arrays

Arrays are used throughout this book, and if you access forums for advice on the use of PHP, you will be confronted with many arrays. Although there is no need right now to learn about the following method for assigning values to arrays, this next section will be a useful reference for the future.

Arrays can be populated with elements in several ways. If we create an array named $cereals, as follows

<?php
$cereals = array();
?>

a few elements can be inserted as follows (note that the first array element is zero):

<?php
$cereals = array();
$cereals[0] = "oats";
$cereals[1] = "barley";
$cereals[2] = "corn";
$cereals[3] = "wheat";
?>

To display these elements, insert the following code just before the closing tag ?>:

echo ("$cereals[0] " . "$cereals[1] " . "$cereals[2] " . $cereals[3]);?>

The display would be

Oats barley corn wheat

The full stop between each element in the code means concatenate the elements (that is, string the elements together). Note the space after the right square bracket in the elements numbered 2 and 3. The space can also be an entity as shown in the element numbered zero. This is useful if you wish to display a character such as a bullet point between each element.

A file containing this array is included in the downloads for this chapter. To see the array elements displayed, start XAMPP and enter http://localhost/simpleIdb/array.php in the address bar of a browser.

Caution The databases created by the tutorials in this chapter are not secure. They have been deliberately stripped of most filters and cleaners so that the essential processes are uncluttered and clearly visible. An increasing number of security features will be added in later chapters.

Summary

In this chapter, you created your first interactive pages and tested them. You learned a little more about PHP—in particular, you should have grasped the idea of looking for and recognizing logical patterns in the code. You learned how to use two PHP functions: include() and echo(). You discovered the importance of PHP conditionals for creating interactive database pages. You also learned how to check whether the user has entered information before sending the data to the database table. In the next chapter, we will add some extra security and demonstrate how users and an administrator can log in and log out of private pages on a web site. You will also learn how to remove or replace the redundant links in the header menus.