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

Chapter 10. Creating a Message Board

A message board can be a stand-alone feature or an important component in a forum. A basic forum has at least four tables, one each for messages, membership registrations, threads, and replies. However, to save space, and deferring to the subtitle of this book, “A Simplified Approach,” this chapter describes a simple message board with a table for messages and a table for members. My hope is that a grasp of the principles of this message board will inspire you to expand its features and explore more complex solutions. For your interest, at the end of this chapter I added a chart showing how you could enhance the message board to create a forum.

Message boards have fewer features than a forum; generally, they lack the ability to accept replies in a manner that allows them to be collected as threads. In a forum, the replies are connected to the original posting id and displayed in ascending date order. Although forums are used in many ways—for instance, for exchanging and providing technical information, help lines, and discussion groups—our message board is simply a means for collecting and displaying knowledge.

This chapter contains the following sections:

·     The plan

·     Create the message board database and tables

·     Create the template for the message board

·     Create the registration page

·        The Thank You page

·     Register some members

·     The login and logout pages

·     Creating a gateway to the message board categories

·     Posting quotations

·     The home page

·     Displaying the quotations

·     Adding search facilities

·     Searching for specific words or phrases

·     Brief suggestions for enhancing the message board and for converting the message board into a basic forum

 Caution  To prevent the display of unpleasant content, message boards require constant monitoring (moderating). Your potential clients should be warned about this before they commit themselves to a design contract with you.

The Plan

To simplify the message board in this chapter, users will not be able to view messages until they are registered and logged in. However, some of the messages will be shown on the home page to tempt users to register. In our example, the messages are quotations, and the aim is to build up a useful database of quotations. The login button on the home page will redirect registered users to a page where they will be able choose which of two types of quotation to view, either comical quotes or wise quotes.

When the message board is accessed, the registered member will be able to contribute a quote. Because the member is contributing quotations, I referred to threads as quotations in the tutorial. For further simplification, the number of columns in the tables is reduced to a practical minimum.

The user name for logging in will be a unique pseudonym chosen by the user because the great majority of message boards and forums insist that members remain anonymous. When members post a new quote, their pseudonym is the only name shown on the message board. However, when registering they might also be asked to provide their e-mail address so that the site administrator can contact them if necessary. Their e-mail address is never disclosed on the message board. If the e-mail address is not required when registering, the web site is unlikely to be subject to data-protection law because no personal information will be stored on the database. The registration form for this tutorial does include the e-mail address, but you can easily omit it. The plan outlined in the preceding text will be used for our message board tutorial.

We will now create the database and tables for the message board.

 Note  Because the message board tutorial was extracted from a larger, more complex forum, some files and a table both retain the word forum instead of the words message board.

Create the Database

Download the files for this chapter from the book’s page at Apress.com, and place them in a new folder named msgboard within the htdocs folder.

Start XAMPP, and in phpMyAdmin create the database named msgboarddb. Set the encoding to utf8._general_ci. This encoding is strongly recommended instead of the default collation because it can represent every Unicode character and is backwardly compatible with ASCII. Then select the Databases tab, select the box next to msgboarddb, and then click privileges. Add a new user with the following details:

User name: brunel

Host: localhost

Password: trailblazer

Scroll down, and look for the words Resource Limits. These fields should all be zero, which is the default.

Scroll down, and select All privileges.

Click Save (or Go in some versions).

The database connection file mysqli_connect.php is included in the downloadable files. Be sure to add it to your htdocs folder. If you wish to create the file manually, use the following code:

<?php
//Create a connection to the msgboarddb database.
// Set the access details as constants
DEFINE ('DB_USER', 'brunel');
DEFINE ('DB_PASSWORD', 'trailblazer');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'msgboarddb');
// 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');

Create the Tables

Either import the tables using the downloadable .sql files, or create them manually. Before creating the tables, you need to decide whether you will require a full text search facility. You will need this if users would benefit from being able to search the message board for a particular quote; for example, he or she might search for the words “Mark Twain” to view a list of quotations by Mark Twain. If you do need a full text search facility, you must choose the MyISAM storage engine for the table that will be searched. However, if you have MySQL version 5.6.4 or later, the INNODB storage engine allows full text searches. For the latest information, see the MySQL web site http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html.

Unfortunately, at the time of writing, version 5.6 of MySQL was not available in the XAMPP download version 1.8.2. However, XAMPP 1.8.3 (for PHP 5.5 and above) now contains MySQL 5.6.4.

 Tip  If you wish to change a table’s storage engine from INNODB to MYSQL or vice versa, this can be achieved by using phpMyAdmin. Instructions are given in the Appendix. As a precaution, before changing the storage engine, always back up your tables using phpMyAdmin’s Exporttab.

In the left panel of phpMyAdmin, click the msgboarddb database and then create two tables. First we will create the members table with six columns using the details shown in Table 10-1. Select the INNODB default storage engine.

Table 10-1. The members table

The member_id is the PRIMARY key, and the user_name has a UNIQUE index to prevent duplicate entries.

You can import the forum.sql downloadable file or manually create the forum table with six columns. It contains a column named post_id, and this is configured as the PRIMARY KEY. After filling out the fields in phpMyAdmin, look below the columns and find the pull-down list of storage engines. Select the MyISAM storage engine if you are using an earlier version than XAMPP 1.8.3 (for PHP 5.5 and above) and MySQL versions earlier than version 5.6.4. This is because the tutorial will include full text searches. Give the message column a FULL TEXT index.

The table details are shown in Table 10-2.

Table 10-2. The attributes for the forum table

Next we will examine some of the pages for the web site.

Create the Template for the Message Board Web Site

This step is not essential because the template will eventually be replaced by the home page. Most of the pages, including the index page, will be based on this template. On the home page, the body text in the template will eventually be replaced by some sample quotations to encourage users to register. The template for the pages is shown in Figure 10-1.

Figure 10-1. The basic template for the forum web site

The code for producing the template is given in Listing 10-1a.

Listing 10-1a.  Creating the Basic Template for the Forum’s Web Site (template.php)

The page contains a link to the style sheet for the project.

<!doctype html>
<html lang=en>
<head>
<title>Forum template</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#header #tab-navigation ul { margin-left:58px; }
</style>
</head>
<body>
<div id='container'>
<?php include('includes/header.php'); ?>
<?php include('includes/info-col.php'); ?>
<div id='content'><!--Start of page content-->
<h2>This is the message board template</h2>
<p>The message board content. The message board content. The message board content. The message board content. The message board content. <br>The message board content. The message board content. The message board content. The message board content. <br>The message board content. The message board content. <br>The message board content. The message board content. The message board content. </p></div><!--End of the template content-->
</div>
<?php include('includes/footer.php'); ?>
</body>
</html>

The header uses a template that contains every horizontal menu button required by this chapter, but various buttons will be commented-out to suit each page; for instance, the registration page will have only a home page button; therefore, the other menu buttons will be commented-out for the registration page. You could, of course, have the same header for every page, but each page would then have several redundant buttons, something I dislike intensely.

The code for the template header is given in Listing 10-1b.

Listing 10-1b.  Creating the Header for the Template (includes/header.php)

The menu choices that are commented-out for the template page are shown in bold type.

<div id="header">
<h1>Quick Quotes</h1>
<div id="tab-navigation">
<ul>
<li><a href="login.php">Login</a></li>
<li><a href="logout.php">Logout</a></li>
<li><a href="safer-register-page.php">Register</a></li>
<!--<li><a href="post.php">Add a quote</a></li>-->
<!--<li><a href="forum_c.php">Comical Quotes</a></li>-->
<!--<li><a href="forum_w.php">Wise Quotes</a></li>-->
<!--<li><a href="view_posts.php">View your Posts</a></li>-->
<!--<li><a href="search.php">Search</a></li>-->
<li><a href="index.php">Home page</a></li>
</ul>
</div>
</div>

The header uses a horizontal menu to provide the maximum amount of room on the page for members’ postings and replies. The header menu block for all the pages is positioned absolutely (vertically), and the main style sheet displays the menu horizontally. However, the horizontal position of the menu block is eventually provided by an internal style in each page.

The CSS code for the main style sheet is given in Listing 10-1c.

Listing 10-1c.  Creating the Main Style Sheet for the Message Board Pages (msgboard.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; margin-bottom:10px; }
h3 { font-size:110%; color:navy; text-align:center; margin-bottom:0; }
#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'); 
color:white; background-repeat: repeat; padding:0; }
h1 {position:relative; top:40px; font-size:350%; color:white; margin:auto 0 auto 20px; 
width: 600px; }
#info-col { position:absolute; top:190px; right:10px; color:navy; width:135px; 
text-align:center; margin:5px 5px 0 0; }
#info-col h3 { width:130px; }
/* set general horizontal button styles */
li { list-style-type :none; margin-bottom: 3px; text-align: center; }
/* set general anchor styles */
li a { display: inline-block; 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:inline-block; background: #0a4adf; border: 4px outset #8abaff; }
/* onmousedown */
li a:active { background:#aecbff; border: 4px inset #aecbff; }
#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; clear:both; }
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; }
/*set the vertical position and an average horizontal position for the menu block*/
#tab-navigation ul { width:850px; position:absolute; top: 135px; left:310px; }
#tab-navigation li { height:25px; margin:5px; display:inline; }
#tab-navigation li a { height:25px; padding-left:10px; padding-right:10px; 
display:inline; }

 Note  At the moment, none of the Template Page buttons work because we have not yet created the other pages. This will be rectified later after we create some data to play with.

The code for the footer is given in Listing 10-1d.

Listing 10-1d.  Creating the Footer (footer.php)

<div id="footer">
<p>Copyright © Adrian West 2013 Designed by 
<a href="http://www.colycomputerhelp.co.uk/"> Adrian W West</a> </p>
</div>

The next step will be to create the registration page so that we can register some member’s data.

Create the Registration Form

The registration form is a cut-down version of the form in Chapter 7. The form is shown in Figure 10-2.

Figure 10-2. The registration page

As previously mentioned in the plan, the user name is not the actual name of the member, but a pseudonym for the purpose of logging in to the web site anonymously. In many forums, the e-mail field is omitted and only the user name and password are required for registration; those two items are usually sufficient for members to log in.

The code for the registration form is given in Listing 10-2.

Listing 10-2.  Create the Registration Page (safer-register-page.php)

The following code inserts a record into the members table.

<!doctype html>
<html lang=en>
<head>
<title>Registration page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#midcol { width:98%; margin:auto; }
input, select { margin-bottom:5px; }
#tab-navigation ul { position:absolute; top: 135px; left:500px; }
p.warning { text-align:center; font-weight:bold; }
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/header_register.php"); ?>
<?php include("includes/info-col.php");?>
<div id="content"><!--Start of the page-specific content-->
<?php
require ('mysqli_connect.php'); // Connect to the database
// Has the form been submitted?
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$errors = array(); // Start an errors array
// Trim the username
$unme = trim($_POST['uname']);
// Strip HTML tags 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;
}
//Set the email variable to FALSE
$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'])){
$errors[] ='Please enter a valid password';
}
if(!preg_match('/^\w{8,12}$/', $_POST['psword1'])) {
$errors[] = 'Invalid password, use 8 to 12 characters and no spaces.';
}
if(preg_match('/^\w{8,12}$/', $_POST['psword1'])) {
$psword1 = $_POST['psword1'];
}
if($_POST['psword1'] == $_POST['psword2']) {
$p = mysqli_real_escape_string($dbcon, trim($psword1));
}else{
$errors[] = 'Your two passwords do not match.';
}
if (empty($errors)) { // If there are no errors, register the user in the database
// Make the query
$q = "INSERT INTO members (member_id, uname, email, psword, reg_date ) 
VALUES (' ', '$uname', '$e', SHA1('$p'), NOW()  )";
$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
// 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>';
// 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 { // Display the errors
echo '<h2>Error!</h2>
<p class="error">The following error(s) occurred:<br>';
foreach ($errors as $msg) { // Display 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 conditionals
?>
<div id="midcol">
<h2>Membership Registration</h2>
<h3>All the fields must be filled out</h3>
<h3>IMPORTANT: Do NOT use your real name as the User name
</h3>
<p class="cntr">Terms:Your registration and all your messages will be immediately 
cancelled<br>if you post unpleasant, obscene or defamatory messages to this forum.</p>
<h3 class="content">When you click the 'Register' button, you will see a confirmation 
page <br></h3>
<form action="safer-register-page.php" method="post">
<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">User Name:</label><input id="uname" type="text" 
name="uname" size="12" maxlength="12" value="<?php if (isset($_POST['uname'])) 
echo $_POST['uname']; ?>"> 6 to 12 characters
<p><input id="submit" type="submit" name="submit" value="Register"></p>
</form>
</div></div></div><!-- End of the registration page content -->
<?php include ('includes/footer.php'); ?>
</body>
</html>

No explanation of the code is required because it is very similar to the registration form in Chapter 7.

 Note  The included header file header_register.php is a copy of header.php. The redundant menu items are commented-out, leaving only the home page button. The Register button on the template’s header will now work because it can link to our newly created registration page.

The Thank You Page

If the registration is successful, the Thank You page is displayed as shown in Figure 10-3.

Figure 10-3. The Thank You page

 Note  The included file header_register.php is re-used in the Thank You file. The code for the Thank You page is given in Listing 10-3.

Listing 10-3.  Creating the Thank You Page (register_thanks.php)

<!doctype html>
<html lang=en>
<head>
<title>Registration thank you page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
p { text-align:center; }
#tab-navigation ul { position:absolute; top: 135px; left:500px;
}

</style>
</head>
<body>
<div id="container">
<?php include("includes/header_register.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the thank you page content-->
<div id="midcol">
<h2>Thank you for registering</h2>
<h3>On the home page you will now be able to log in and add quotes to the message board.</h3>
</div>
</div></div><!--End of the thankyou page content-->
<?php include("includes/footer.php"); ?>
</body>
</html>

Register Some Members

Now that you have a registration form, you can use the register button in the template’s header to register the members given in Table 10-3, or you can import the downloadable members.sql file.

Table 10-3. Register some members

The members will automatically have the default member_level of zero. In this tutorial, the administrator’s real name is Frank Incense. He is the last member on the preceding list. For maximum security, to log on as administrator, he also registered with the user name skivvy, the false e-mail address jsmith@outcook.com, and the password dogsbody12. He also has a member_level of 1. Although lack of space prevents the inclusion of a full administration facility in this tutorial, you might later wish to add full administration pages to the web site.

Now that we have some registered members, they can be allowed to log in.

The Login Page

The login page is shown in Figure 10-4.

Figure 10-4. The login page

The code for the login page is given in Listing 10-4a.

Listing 10-4a.  Creating the Login Page (login.php)

The header fileheader_login.php is a copy of header.php with the menu buttons commented-out except for Register and Home page. This code processes the submissions from the login form.

An internal style is used to position the form fields and to locate the menu buttons horizontally.

<!doctype html>
<html lang=en>
<head>
<title>login page </title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
h2 { color:navy; }
#tab-navigation ul { margin-left:140px; }
form { padding-left:295px; }
.label { width:80px; float:left; text-align:right;}
form p { width:250px; }
p.submit {margin-left:86px; }
</style>
</head>
<body>
<div id='container'>
<?php
include ( 'includes/header_login.php' ) ;
// Display any error messages if present
if ( isset( $errors ) && !empty( $errors ) )
{
echo '<p id="err_msg">A problem occurred:<br>' ;
foreach ( $errors as $msg ) { echo " - $msg<br>" ; }
echo 'Please try again or <a href="safer-register-page.php">Register</a></p>' ;
}
?>
<!-- Display the login form fields -->
<h2>Login</h2>
<form action="process_login.php" method="post">                                          #1
<p><label class="label" for="uname">User Name:</label><input id="uname" type="text"
name="uname" size="16" maxlength="16" value="<?php if (isset($_POST['uname']))
echo $_POST['uname']; ?>"></p>
<p><label class="label" for="psword">Password:</label><input id="psword"
type="password" name="psword" size="16" maxlength="16" value="<?php if
(isset($_POST['psword'])) echo $_POST['psword']; ?>" ></p>
<p class="submit"><input type="submit" value="Login" ></p>
</form>
<?php
include ( 'includes/footer.php' ) ;
?>
</div>
</body>
</html>

Explanation of the Code

You will have seen all the code before, but the line numbered #1 refers to a file that needs explaining:

<form action="process_login.php" method="post">                                          #1

The file named process_login.php launches the login process. The code for this is given in Listing 10-4b. Using external files for processing variables is good practice and assists in establishing security because hackers have to probe the web site for the related files before they can cause mischief.

Processing the Login

Place this file in the htdocs folder.

Listing 10-4b.  Creating the Code for Processing the Login (process_login.php)

<?php
// Was the form submitted?
if ( $_SERVER[ 'REQUEST_METHOD' ] == 'POST' )
{
// Connect to the database
require ( 'mysqli_connect.php' ) ;
// Load the validation functions
require ( 'login_functions.php' ) ;                                                      #1
// Check the login data
list ( $check, $data ) = validate ( $dbcon, $_POST[ 'uname' ], $_POST[ 'psword' ] ) ;
// If successful, set session data and display the forum.php page
if ( $check )
{
// Access the session details
session_start();
$_SESSION[ 'member_id' ] = $data[ 'member_id' ] ;
$_SESSION[ 'uname' ] = $data[ 'uname' ] ;
load ( 'forum.php' ) ;
}
// If it fails, set the error messages
else { $errors = $data; }
// Close the database connection
mysqli_close( $dbcon ) ;
}
// Because it failed, continue to display the login page
include ( 'login.php' ) ;
?>

Line #1 refers to a file named login_functions.php. This file contains two functions; one processes the data entered by the user, and the second loads the appropriate page. The code for login_functions.php is given in Listing 10-4c.

Create the Functions for Logging In

Listing 10-4c.  Creating the Functions Required for the Login Process (login_functions.php)

<?php
// Create the function for loading the URL of the login page
function load( $page = 'login.php' )
{
// The code for setting the page URL
$url = 'http://' . $_SERVER[ 'HTTP_HOST' ] . dirname( $_SERVER[ 'PHP_SELF' ] ) ;
// If the the URL has any trailing slashes, remove them and add a forward slash to the URL
$url = rtrim( $url, '/\\' ) ;
$url .= '/' . $page ;
//Redirect to the page and exit the script
header( "Location: $url" ) ;
exit() ;
}
// Create a function to check the user name and password
function validate( $dbcon, $uname = '', $p = '')
{
// Start an array to hold the error messages
$errors = array() ;
// Has the user name been entered?
if ( empty( $uname ) )
{ $errors[] = 'You forgot to enter your user name' ;
}
else  { $uname = mysqli_real_escape_string( $dbcon, trim( $uname ) ) ;
}
// Has the password been entered
if ( empty( $p ) )
{ $errors[] = 'Enter your password.' ;
}
else { $p = mysqli_real_escape_string( $dbcon, trim( $p ) ) ;
}
// If everything is OK, select the member_id and the user name from the members' table
if ( empty( $errors ) )
{
$q = "SELECT member_id, uname FROM members WHERE uname='$uname' AND psword=SHA1('$p')" ;
$result = mysqli_query ( $dbcon, $q ) ;
if ( @mysqli_num_rows( $result ) == 1 )
{
$row = mysqli_fetch_array ( $result, MYSQLI_ASSOC ) ;
return array( true, $row ) ;
}
// Create an error message if the user name and password do not match the database record
else { $errors[] = 'The user name and password do not match our records.' ;
}
}
// Retrieve the error messages
return array( false, $errors ) ;
}

The first function load() loads a page that is specified in the brackets. The second function validate() validates the user name and password entered by the user. It displays error messages in the event of an unsuccessful login.

Logging Out

When the member logs out, the logout page appears as shown in Figure 10-5.

Figure 10-5. The logout page

Logging out is an important security feature. The code for the logout page is given in Listing 10-5.

Listing 10-5.  Creating the Logout File (logout.php)

An internal style is used to position the menu button and the text.

<?php
session_start() ;
// Redirect users if they are not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) )
{
require ( 'login_functions.php' ) ; load() ;
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Logout code</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#tab-navigation ul { margin-left:190px; }
h3 { text-align:center; margin-top:-10px; }
</style>
</head>
<body>
<div id='container'>
<?php
//Re-use the registration header that has only one menu button i.e. the Home Page button
include ( 'includes/header_register.php' ) ;
// Remove the session variables from the session
$_SESSION = array() ;
// Destroy the session
session_destroy() ;
// Display the thank you message
echo '<h2>Thank you for logging out!</h2>
<br><h3>Logging out is a very important security measure</h3> ';
include ( 'includes/footer.php' ) ;
?>
</div>
</body>
</html>

The login page described earlier, redirects registered users to the forums page, where they can choose which forum to view. This page is described next.

Creating a Gateway to the Two Categories of Quotes

The gateway page enables members to choose which category to view. It is shown in Figure 10-6.

Figure 10-6. The forums page allows the user to choose a quotes category

I could have placed the two new buttons in the header, but I prefer to maximize their visibility by placing them in the body of the page.

The code for the gateway page is given in Listing 10-6.

Listing 10-6.  Creating a Gateway to the Two Categories (forum.php)

The header file header_forum_choice.php is a copy of the header file with the menu buttons commented-out except for Logout and Home page.

An internal style is used to position the horizontal menu and the two new category buttons.

<?php
session_start() ;
// Redirect the user if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) {
require ( 'login_functions.php' ) ; load() ; }
?>
<!doctype html>
<html lang=en>
<head>
<title>Forum page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#forum_links { position:absolute; top:115px; left:115px; }
#header #tab-navigation ul { margin-left:185px; }
</style>
</head>
<body>
<div id='container'>
<?php include ( 'includes/header_forum_choice.php' ) ;?>
<h2>Choose a forum</h2>
<div id="forum_links">
<div id="tab-navigation">
<ul>
<li><a href="forum_c.php">Comical Quotes</a></li>
<li><a href="forum_w.php">Wise Quotes</a></li>
</ul>
</div>
</div>
<br><br>
<br class="clear">
<?php include ( 'includes/footer.php' ) ; ?>
</div>
</body>
</html>

Before we create the two forum pages, we need to have some quotations to display in the forums pages. We will now create a form so that we can enter some quotations.

The Form for Posting Quotations

The posting form is shown in Figure 10-7.

Figure 10-7. The form for posting quotations

You can use the word “Subject” as I have or, if you wish, you can replace it with “Category”. In this tutorial, they are synonymous. A pull-down menu is used for the subject for two reasons:

·     I assumed that the message-board owner wants to limit the number of subjects (categories) because the owner does not want members to create new subjects. Members will instead add new quotations to either the Comical Quotations subject or the Wise Quotations subject.

·     The spelling needs to be consistent. A pull-down menu guarantees this. An owner can, of course, add new subjects (categories) to the pull-down menu.

The code for the posting page is given in Listing 10-7a.

Listing 10-7a.  Creating the Form for Posting New Quotations (post.php)

The included file header_post.php is a copy of header.php with the buttons commented-out except for Logout and Home Page. The Add a quote button is replaced by an Erase Entries button as follows:

<li><a href="post.php">Erase Entries</a></li>

A session is used to ensure that only members are allowed to post new quotations. An internal style positions the form fields and the menu block.

<?php
session_start() ;
// Redirect if not logged in.
if ( !isset( $_SESSION[ 'member_id' ] ) ) {
require ( 'login_functions.php' ) ; load() ; }
?>
<!doctype html>
<html lang=en>
<head>
<title>The form for posting subjects and messages</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#tab-navigation ul { margin-left:85px; }
form { padding-left:215px; }
</style>
</head>
<body>
<div id='container'>
<?php // The form for posting messages
include ( 'includes/header_post.php' ) ;
echo '<h2>Post a Quotation</h2>';
// Display the form fields
echo '<form action="process_post.php" method="post" accept-charset="utf-8">
<p>Choose the Subject: <select name="subject">
<option value="Comical Quotes">Comical Quotes</option>
<option value="Wise Quotes">Wise Quotes</option>
</select></p>
<p>Message:<br><textarea name="message" rows="5" cols="50"></textarea></p>
<p><input name="submit" type="submit" value="post"></p>
</form>';
include ( 'includes/footer.php' ) ; //posting an entry into the database table 
automatically sends a message to the forum moderator                                     #1
// Assign the subject using a ternary operator
$subject = "Posting added to Quick Quotes message board";
$user = isset($_SESSION['uname']) ? $_SESSION['uname'] : "";
$body = "Posting added by " . $user;
mail("admin@myisp.co.uk", $subject, $body, "From:admin@myisp.co.uk\r\n");
?>
</div>
</body>
</html>

Explanation of the Code

//posting an entry into the database table automatically sends an e-mail message 
to the forum moderator                                                                   #1
// Assign the subject using a ternary operator
$subject = "Posting added to Quick Quotes Forum";
$user = isset($_SESSION['uname']) ? $_SESSION['uname'] : "";
$body = "Posting added by " . $user;
mail("admin@myisp.co.uk", $subject, $body, "From:admin@myisp.co.uk\r\n");
?>

Naturally, you must replace the dummy e-mail addresses with your own. Note that the two e-mail addresses are the same when sending an e-mail to yourself.

The database can, of course, be moderated by an administrator who is familiar with phpMyAdmin; however, it would be good to create a user-friendly administration facility for someone who is not familiar with phpMyAdmin. (Review the previous Chapters, especially Chapter 3, for instructions on creating administration pages.) A user-friendly administration page allows the administrator to view a table of the latest posts. This table would have Delete and Edit links as described in Chapter 3.

The code beginning with line #1 sends an e-mail to the administrator’s real e-mail address (not his dummy e-mail address) to notify him that a member has posted a message. He would then use his administration facility to view the message to decide whether it complies with the rules of the forum. The e-mail in this example is the simplest possible. The PHP function mail() has the following format:

mail(to, subject, body, from)

The to and from must be e-mail addresses. The variables can be basic, like $subject is in this example, or very complex. In Listing 10-7a, the username of the person posting the message is pulled from the session and then concatenated with some text to form the body of the e-mail. The items subject, to, and from create the header of the e-mail. The header is the top section of the e-mail, the body is the window below the header. The resulting e-mail will look like Table 10-4.

Table 10-4. The appearance of the e-mail

 Note  E-mails will not be sent and received using your computer unless you install and configure a program such as Mercury (bundled with XAMPP). Mercury is tricky, but Internet help is available. I prefer to test the e-mail by uploading the file to one of my web sites together with a minimal database. For Internet help with Mercury, try the videos on YouTube. Begin with http://www.youtube.com/watch?v=VU4PT7xMSO0, use the pause button when you want to take notes. On the same YouTube page, you will find several other tutorials. The tutorials are easier than written instructions. Two example scripts for e-mailing are provided in the Appendix.

Processing the Postings

The user input is tested by means of two external files, process_post.php, and login_functions.php that are used to redirect a user who has not logged in.

The code for process_post.php is given in Listing 10-7b.

Listing 10-7b.  Creating the File for Processing the Postings (process_post.php)

<?php
// Start the session
session_start();
// Include the login functions to check for errors
require ( 'login_functions.php' ) ;
// If users are not logged in, redirect them
if ( !isset( $_SESSION[ 'member_id' ] ) ) { load() ; }
//Connect to the database
require ( 'mysqli_connect.php' ) ;
// Has the form been submitted?
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
// Check that the user has entered a subject and a message                               #1
if ( empty($_POST['subject'] ) ) { echo '<p>You forgot to enter a subject.</p>'; }
if ( empty($_POST['message'] ) ) { echo '<p>You forgot to enter a message.</p>'; }
if ( !empty( $_POST['message']))
{
$message = mysqli_real_escape_string( $dbcon, strip_tags(trim( $_POST['message'] )) ) ;  #2
}
// If successful, insert the post into the database table. This check is not essential, but it
// does ensure that the page has not been compromised by a hacker.
if( !empty($_POST['subject']) && !empty($_POST['message']) )
{
//Make the insert query                                                                  #2
$q = "INSERT INTO forum(uname, subject, message, post_date)
VALUES ('{$_SESSION['uname']}', '{$_POST['subject']}','$message',NOW() )";
$result = mysqli_query ( $dbcon, $q ) ;
// If it fails, display an error message
if (mysqli_affected_rows($dbcon) != 1) { echo '<p>Error</p>'.mysqli_error($dbcon); } else { load('forum.php'); }
// Close the database connection
mysqli_close( $dbcon ) ;
}
}
// Create a link back to the forum page
echo '<p><a href="forum.php">Forum</a>' ;
include ( 'includes/footer.php' ) ;
?>

Explanation of the Code

// Check that the user has entered a subject and a message                               #1
if ( empty($_POST['subject'] ) ) { echo '<p>You forgot to enter a subject.</p>'; }

This check is not essential, but it does ensure that the page has not been compromised by a hacker.

$message = mysqli_real_escape_string( $dbcon, strip_tags(trim( $_POST['message'] )) ) ;  #2
}
// If successful, insert the posting into the database table.
if( !empty($_POST['subject']) && !empty($_POST['message']) )
{

The text area for messages is a magnet for malevolent persons wanting to insert dangerous scripts. Therefore, special security filters must be built into the code. The message in the textarea is cleaned by using three functions in line #1; note that the functions have been nested. The functions are as follows:

·     trim removes spaces from the beginning and end of the message.

·     strip_tags removes HTML tags because these might contain harmful scripts, for example: <script>some JavaScript code</script>.

·     You have met mysqli_real_escape_string many times before. It will remove any unwanted characters, including apostrophes. For instance, if the user enters a message containing the words one’s or he’s, the apostrophe will be entered into the forum table correctly and an error message will be avoided.

//Make the insert query                                                        #3
$q = "INSERT INTO forum(uname, subject, message, post_date)
VALUES ('{$_SESSION['uname']}', '{$_POST['subject']}','$message', NOW() )";
$result = mysqli_query ( $dbcon, $q ) ;

The query values are as follows:

·     '{$_SESSION['uname']}': The user name is provide by the user initiated session.

·     '{$_POST['subject']}': The subject is a global variable provided by the user’s entry in post.php.

·     '$message': This is defined in the previous block of code beginning with line #1.

·     NOW(): This is automatically provided by the DATETIME entry when the user posts a quotation.

Post Some Quotations

Now that we have a form for inserting postings into the forum table, we will post the quotations shown in Table 10-5. Or you could use phpMyAdmin to import the file forum.sql that you will find in the downloaded files for this chapter. As each quotation is posted, you will be redirected to the forum page with the two buttons for choosing either comical or wise quotes. However, you will not be able to view the quotations yet because we have not created the two pages for displaying them.

Table 10-5. Post some quotations

Remember: The date and time of the postings will be added automatically.

Now that we have some quotations to display, we will create the home page.

The Home Page

The home page will display a small selection of the quotations to tempt the user to register as a member of the message board. The home page is shown in Figure 10-8.

Figure 10-8. The home page

The code selects five quotations from the table named forum. The code for the home page is given in Listing 10-8.

Listing 10-8.  Creating the Home Page (index.php)

An internal style is used to position the horizontal menu buttons. The header for the home page is the template header header.php.

<!doctype html>
<html lang=en>
<head>
<title>Home page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
table { background:white; color:black; }
th {padding:4px; border:1px black solid; }
#tab-navigation ul { margin-left:95px; }
h2 { margin-bottom:0; margin-top:10px; }
h3 {margin-bottom:0; margin-top:0; }
</style>
</head>
<body>
<div id='container'>
<?php include ( 'includes/header.php' ) ;?>
<h2>This home page shows a selection from our large collection of quotations.</h2>
<h2>To view the whole collection, please register.</h2><br>
<h3>You will then be able to contribute to this forum by adding quotations.</h3>
<?php
// Connect to the database
require ( 'mysqli_connect.php' ) ;
// Make the query
$q = "SELECT uname,post_date, subject, message FROM forum LIMIT 5" ;                     #1
$result = mysqli_query( $dbcon, $q ) ;
if ( mysqli_num_rows( $result ) > 0 )
{
echo '<br><table><tr><th>Posted By</th><th>Subject</th><th id="msg">Message</th></tr>';
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
echo '<tr><td>' . $row['uname'].'<br>'.$row['post_date'].'</td>
<td>'.$row['subject'].'</td><td>' . $row['message'] . '</td> </tr>';
}
echo '</table>' ;
}
else { echo '<p>There are currently no messages.</p>' ; }
mysqli_close( $dbcon ) ;
include ( 'includes/footer.php' ) ;
?>
</div>
</body>
</html>

Explanation of the Code

The majority of the code you have seen before, but the query in line #1 is a little different:

$q = "SELECT uname,post_date, subject, message FROM forum LIMIT 5" ;                     #1

The query selects items from the forum table for display, but the LIMIT keyword ensures that only five lines are shown.

We will now create two pages, one for each forum (subject).

The Comical Quotes Page

The comical quotes page is shown in Figure 10-9.

Figure 10-9. The comical quotes page

The code given in Listing 10-9 selects only the comical quotes from the database forum table.

Listing 10-9.  Creating the Comical Quotes Page (forum_c.php)

An internal style is used to format the table and to position the table and menu buttons.

<?php session_start() ;
// Redirect if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) { require ( 'login_functions.php' ) ; load() ; }
?>
<!doctype html>
<html lang=en>
<head>
<title>Comical quotes forum page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
table { background:white; color:black;  }
th {padding:4px; border:1px black solid; }
#tab-navigation ul { margin-left:-95px; }
</style>
</head>
<body>
<div id='container'>
<?php
include ( 'includes/header_comical.php' ) ;
// Connect to the database
require ( 'mysqli_connect.php' ) ;
// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum
WHERE subject = 'Comical Quotes' ORDER BY 'post_date' ASC";
$result = mysqli_query( $dbcon, $q ) ;
if ( mysqli_num_rows( $result ) > 0 )
{
echo '<h2>Comical Quotes</h2>
<table><tr><th>Posted By</th><th>Forum</th> <th id="msg">Quotation</th></tr>';
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
echo '<tr><td>' . $row['uname'].'<br>'.$row['post_date'].'</td>
<td>'.$row['subject'].'</td><td>' . $row['message'] . '</td> </tr>';
}
echo '</table>' ;
}
else { echo '<p>There are currently no messages.</p>' ; }
//Close the database connection
mysqli_close( $dbcon ) ;
include ( 'includes/footer.php' ) ;
?>
</div>
</body>
</html>

Explanation of the Code

Most of the code in Listing 10-9 will be familiar, and there are plenty of comments in the Listing. We will now examine line #1:

// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum
WHERE subject = 'Comical Quotes' ORDER BY 'post_date' ASC";

The query selects only the comical quotes from the forum table. The quotes will be sorted in ascending order of posting—that is, oldest first.

To confirm that the file is satisfactory, log in and load the file into the address bar of a browser as http://localhost/msgboard/forum_c.php

The Header for the Comical Quotes Page

After viewing the comical quotes page, the user may also wish to view the Wise Quotes page. To enable the user to do this, the comment-out is removed from the Wise Quotes button in the comical quotes header header_comical.php. The comment-outs have also been removed from those menu buttons that enable members to search the database. For the moment, those two buttons will be inactive because we have not yet designed the relevant pages.

The buttons to be displayed are shown in bold type in the code snippet for the new header as follows:

<div id="header">
<h1>Quick Quotes</h1>
<div id="tab-navigation">
<ul>
<!--<li><a href="login.php">Login</a></li>-->
<li><a href="logout.php">Logout</a></li>
<!--<li><a href="safer-register-page.php">Register</a></li>-->
<li><a href="post.php">Add a Quote</a></li>
<!--<li><a href="forum_c.php">Comical Quotes</a></li>-->
<li><a href="forum_w.php">Wise Quotes</a></li>
<li><a href="view_posts.php">View your Posts</a></li>
<li><a href="search.php">Search</a></li>
<li><a href="index.php">Home page</a></li>
</ul>
</div>
</div>

To confirm that the file is satisfactory, with XAMPP running, log in and load the file into the address bar of a browser as http://localhost/msgboard/forum_c.php.

The Wise Quotes Page

This page is almost the same as the Comical Quotes page except for the MySQL query. Also, the Wise Quotes button has been commented-out, and the Comical Quotes button is made active by removing its comment-out. Figure 10-10 shows the Wise Quotes page.

Figure 10-10. The Wise Quotes page

The page displays only the wise quotes. The code for the Wise Quotes page is given in Listing 10-10.

Listing 10-10.  Creating the Wise Quotes Page (forum_w.php)

An internal style is used to position the table and the menu buttons.

<?php session_start() ;
// Redirect if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) { require ( 'login_functions.php' ) ; load() ; }
?>
<!doctype html>
<html lang=en>
<head>
<title>Wise quotes Forum page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
table { background:white; color:black; }
th {padding:4px; border:1px black solid; }
#tab-navigation ul { margin-left:-107px; }
</style>
</head>
<body>
<div id='container'>
<?php
include ( 'includes/header_wise.php' ) ;
// Connect to the database
require ( 'mysqli_connect.php' ) ;
// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum
WHERE subject = 'Wise Quotes' ORDER BY 'post_date' ASC";
$result = mysqli_query( $dbcon, $q ) ;
if ( mysqli_num_rows( $result ) > 0 )
{
echo '<h2>Wise Quotes</h2><table><tr><th>Posted By</th> 
<th>Forum</th><th id="msg">Quotation</th></tr>';
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
echo '<tr><td>' . $row['uname'].'<br>'.$row['post_date'].'</td>
<td>'.$row['subject'].'</td><td>' . $row['message'] . '</td> </tr>';
}
echo '</table>' ;
}
else { echo '<p>There are currently no messages.</p>' ; }
//Close the database connection
mysqli_close( $dbcon ) ;
?>
</div>
<?php include ( 'includes/footer.php' ) ; ?>
</body>
</html>

Explanation of the Code

The code is almost identical to the listing for the Comical Quotes forum page, except for the items shown in bold type in Listing 10-10:

// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum
WHERE subject = 'Wise Quotes' ORDER BY 'post_date' ASC";

The query selects only the records where the subject is “Wise Quotes.” The quotes will be sorted in ascending order of posting—that is, oldest first.

As with the comical quotes page, a new header named header_wise.php will allow the user to redirect to the Comical Quotes page.

The Header for the Wise Quotes Page

The header code is identical to the header for the Comical Quotes page except that the Wise Quotes button is commented-out, and the comment-out is removed from the Comical Quotes button. Note that the View your Posts and Search buttons will not work yet, because the relevant pages have not been created. The displayed buttons are shown in bold type in the code snippet for the header as follows:

<div id="header">
<h1>Quick Quotes</h1>
<div id="tab-navigation">
<ul>
<!--<li><a href="login.php">Login</a></li>-->
<li><a href="logout.php">Logout</a></li>
<!--<li><a href="safer-register-page.php">Register</a></li>-->
<li><a href="post.php">Add a Quote</a></li>
<li><a href="forum_c.php">Comical Quotes</a></li>
<!--<li><a href="forum_w.php">Wise Quotes</a></li>- ->
<li><a href="view_posts.php">View your Posts</a></li>
<li><a href="search.php">Search</a></li>
<li><a href="index.php">Home page</a></li>
</ul>
</div>
</div>

Adding Search Facilities

The most likely reasons for searching are as follows:

·     Members may want to view a list of their own postings.

·     Members might wish to search the messages for particular words or phrases, or quotes by a particular author.

Before we can implement these searches, we must create the pages that will display the search results.

Figure 10-11 shows the display for viewing an individual member’s postings.

Figure 10-11. Displaying one member’s postings

The code for the page displaying a member’s posting is given in Listing 10-11.

Listing 10-11.  Creating a Page to Display an Individual Member’s Postings (view_posts.php)

An internal style positions the menu buttons and sets the table details.

<!doctype html>
<html lang=en>
<head>
<title>View a member’s postings</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
table { background:white; color:black; }
th {padding:4px; border:1px black solid; }
#tab-navigation ul { margin-left:-107px; }
</style>
</head>
<body>
<div id='container'>
<?php
// Start the session
session_start() ;
// Redirect if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) { require ( 'login_functions.php' ) ; load() ; }
include ( 'includes/header_your_posts.php' ) ;
// Connect to the database
require ( 'mysqli_connect.php' ) ;
// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum 
WHERE uname = '{$_SESSION['uname']}' ORDER BY post_date ASC";
$result = mysqli_query( $dbcon, $q ) ;
if ( mysqli_num_rows( $result ) > 0 )
{
echo '<h2>Your Postings</h2><table><tr><th>Posted By</th><th>Forum</th>
<th id="msg">Quotation</th></tr>';
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
echo '<tr><td>' . $row['uname'].'<br>'.$row['post_date'].'</td>
<td>'.$row['subject'].'</td><td>' . $row['message'] . '</td> </tr>';
}
echo '</table>' ;
}
else { echo '<p>There are currently no messages.</p>' ; }
mysqli_close( $dbcon ) ;
?>
</div>
<?php include ( 'includes/footer.php' ) ; ?>
</body>
</html>

Explanation of the Code

You will have seen most of the code before, but the query needs some explanation.

// Make the query                                                                        #1
$q = "SELECT uname,post_date,subject,message FROM forum 
WHERE uname = '{$_SESSION['uname']}' ORDER BY post_date ASC";

The query selects the items to be displayed and specifies two conditions: (i) show postings only for the user name specified in the session, (ii) order the table row display in ascending order of date of posting.

The Header for View Posts.php

The header menu will have six buttons. These are shown in bold type in the following snippet of code for header_your_posts.php.

<div id="header">
<h1>Quick Quotes</h1>
<div id="tab-navigation">
<ul>
<!--<li><a href="login.php">Login</a></li>-->
<li><a href="logout.php">Logout</a></li>
<!--<li><a href="safer-register-page.php">Register</a></li>-->
<li><a href="post.php">Add a Quote</a></li>
<li><a href="forum_c.php">Comical Quotes</a></li>
<li><a href="forum_w.php">Wise Quotes</a></li>
<!--<li><a href="view_posts.php">View your Posts</a></li>-->
<li><a href="search.php">Search</a></li>
<li><a href="index.php">Home page</a></li>
</ul>
</div>
</div>

We will now enable the members to undertake full text searches.

Search for Specific Words or Phrases

Members might wish to see a table of quotes by Mark Twain, or a list of quotes about golf; the members would therefore be searching for particular words or phrases. This would require a Search button in the headers of both forum pages linked to a form for entering search criteria. As an alternative, the search field could be incorporated into the forum pages; however, in keeping with the book’s subtitle, we will employ the simpler method using a button that links to a search form.

A full text search will search though every message to find the word(s). To achieve this, the forum table must use the MyISAM storage engine unless you are using later programs, such as XAMPP 1.8.3 with MySQL 5.6.4 and PHP 5+. The column named messages must be indexed.

Full text searches can be used on VARCHAR and TEXT columns. Full text searches are case insensitive and will ignore the following:

·     Partial words. If you want to search for “spoiled,” you have to search for the full word. Just searching for “spoil” won’t return what you are looking for.

·     Words containing less than four characters.

·     Stop words. These are words that are extremely common, such as the, a, an, as, by, his, her, with, and you.

·     A word or phrase that is included in more than 50% of the rows in the column being searched. The word or phrase in this case is treated as a stop word, and you will receive an error message saying “MATCH AGAINST returns zero rows.” This can usually be avoided by having more records in a table.

Depending on which version of XAMPP you are using, you might have to change the type of storage engine. In any case, you will need to select FULL TEXT index and these steps are described next.

Preparing the Table for Full Text Searches

1.    If you are using programs earlier than XAMPP 1.8.3, MySQL 5.6.4, and PHP 5+, the forum table should have been created to use the MyISAM storage engine. If you forgot to do this, use phpMyAdmin to change it. Full instructions are given in the Appendix. Alternatively, you could install the later version of XAMPP and MySQL.

2.    In phpMyAdmin, click the msgboarddb database and then click the forum table. Click the Structure tab, select the box next to the message column, and then use the pull-down More menu to select a FULL TEXT index.

 Caution  If you fail to select FULL TEXT index, you will see an error message when you run a search. Note that only one full text search index is allowed per table, although two columns can be combined with one full text index.

The Full Text Search Form

We will now create a form for searching specific words or phrases within messages (quotations).

The search form is shown in Figure 10-12.

Figure 10-12. The search form

In Figure 10-12, the member has entered the search words “Mark Twain”. The code for the search form is given in Listing 10-12.

Listing 10-12.  Creating the Search Form (search.php)

An internal style positions the menu horizontally.

<!doctype html>
<html lang=en>
<head>
<title>Search form</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
#tab-navigation ul { margin-left:145px; }
</style>
</head>
<body>
<div id="container">
<?php
// Start the session
session_start() ;
// Redirect if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) { require ( 'login_functions.php' ) ; load() ; }
// Use the same header as for forum.php
include("includes/header_forum_choice.php");
?>
<div id="content"><!-- Start of search page content. -->
<h2>Search for a word or phrase in the quotes</h2>
<form action="quotes_found.php" method="post">
<p><label class="label" for="target">Enter a word or phrase: </label>
<input id="target" type="text" name="trget" size="40" maxlength="60" 
value="<?php if (isset($_POST['target'])) echo $_POST['target']; ?>"></p>
<p><input id="submit" type="submit" name="submit" value="Search"></p>
</form>
<?php include ('includes/footer.php'); ?>
<!-- End of the search page content. -->
</div>
</div>
</body>
</html>

The search form passes the search word(s) to the page that displays the search results.

Displaying the Search Results

Figure 10-13 shows how the full text search displays the results.

Figure 10-13. The search results for quotations by Mark Twain are displayed

Note that the number of quotations by Mark Twain is less than 50% of all the quotations contained in our forum table. If the number of quotes by Mark Twain had been more than 50% of the quotations, no results would have been displayed. In our example database table, there are 23 quotes. Mark Twain is cited for three quotes; he is therefore cited for 13% of the total. Because this is less than 50% of the total, he will be found and displayed. If the table contained only six quotes, Mark Twain would be cited for 50% of the total and therefore he would not be found and displayed in a search for “Mark Twain”. In other words, the group of words “Mark Twain” would be treated as if it was a stop word such as his, her, with, and you. If you know that a word or phrase exists in the database table, but a full text search fails to find it, either you don’t have enough quotes in the table or at least 50% of the quotes contain the search word or phrase. The code for the quotes_found page is given in Listing 10-13a.

Listing 10-13a.  Creating the Search Results Page (quotes_found.php)

An internal style sheet positions the menu buttons horizontally.

<!doctype html>
<html lang=en>
<head>
<title>Quotes found</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="msgboard.css">
<style type="text/css">
table { background:white; color:black; }
th {padding:4px; border:1px black solid; }
#tab-navigation ul { margin-left:-167px; }
</style>
</head>
<body>
<div id='container'>
<?php include('includes/header_quotes_found.php'); ?>
<div id='content'><!--Start of the quotes found page content-->
<?php
// Start the session
session_start() ;
// Redirect if not logged in
if ( !isset( $_SESSION[ 'member_id' ] ) ) { require ( 'login_functions.php' ) ; load() ; }
// Connect to the database
require ( 'mysqli_connect.php' ) ;
//if POST is set                                                                         #1
if($_SERVER['REQUEST_METHOD'] == 'POST' ) {
$target = $_POST['target'];//Set variable
}
// Make the full text query                                                              #2
$q = "SELECT uname,post_date,subject,message FROM forum WHERE MATCH (message) 
AGAINST ( '$target') ORDER BY post_date ASC";
$result = mysqli_query( $dbcon, $q ) ;
if ( mysqli_num_rows( $result ) > 0 )
{
echo '<h2>Full Text Search Results</h2>
<table><tr><th>Posted By</th><th>Forum</th><th id="msg">Quotation</th></tr>';
while ( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ))
{
echo '<tr><td>' . $row['uname'].'<br>'.$row['post_date'].'</td>
<td>'.$row['subject'].'</td><td>' . $row['message'] . '</td> </tr>';
}
echo '</table>' ;
}
else { echo '<p>There are currently no messages.</p>' ; }
mysqli_close( $dbcon ) ;
?>
</div><!--End of the quotes found page content.-->
</div>
<?php include('includes/footer.php'); ?>
</body>
</html>

 Note  If you run this file and you see this error message, “Warning: mysqli_num_rows( ) expects parameter 1 to be mysqli_result, boolean given in . . . ,” you probably forgot to choose Full Text Search for the table.

Explanation of the Code

//if POST is set                                                                         #1
if($_SERVER['REQUEST_METHOD'] == 'POST' ) {
$target = $_POST['target'];//Set variable
}

The search form sent the target word or phrase (in this example, it was Mark Twain) to this page, where it is assigned to the variable $target.

// Make the full text query                                                              #2
$q = "SELECT uname,post_date,subject,message FROM forum WHERE MATCH (message) 
AGAINST ( '$target') ORDER BY post_date ASC";
$result = mysqli_query( $dbcon, $q ) ;

The full text query searches the forum table for messages (quotations) that contain the words “Mark Twain” as stored in the variable $target. Note the brackets, the commas, and the double and single quote marks; they are important. The format for a full text search query is as follows:

SELECT list of items FROM some table WHERE MATCH (the column) AGAINST(the search words) ;

The key words MATCH and AGAINST are the main differences between a standard SELECT query and a full text search query.

The Header for the quotes_found Page

The code for the header is given in Listing 10-13b.

Listing 10-13b.  Creating the Header for the Quotes Found Page (header_quotes_found.php)

<div id="header">
<h1>Quick Quotes</h1>
<div id="tab-navigation">
<ul>
<!--<li><a href="login.php">Login</a></li>-->
<li><a href="logout.php">Logout</a></li>
<!--<li><a href="safer-register-page.php">Register</a></li>-->
<li><a href="post.php">Add a quote</a></li>
<li><a href="forum_c.php">Comical Quotes</a></li>
<li><a href="forum_w.php">Wise Quotes</a></li>
<li><a href="view_posts.php">View your Posts</a></li>
<li><a href="search.php">Search</a></li>
<li><a href="index.php">Home page</a></li>
</ul>
</div>
</div>

Several enhancements can be added to the message board, but I only have space to mention them very briefly.

Enhancing the Message Board

The message board in this chapter was greatly simplified for the benefit of readers who are not familiar with databases. By using the knowledge you gained from previous chapters, it is possible to add enhancements as follows:

·     The pages for displaying the two categories of quotes could display paginated results. (Review Chapters 5 and 8 for a reminder of how to do this.)

·     Members may wish to change their passwords. (Review Chapter 3 for details.)

·     Members may have forgotten their passwords; they can be sent a new password. This is covered in Chapter 11.

Converting the Message Board to a Forum

At the beginning of this chapter, I said that I would briefly describe a structure for converting the message board to a forum. A forum requires maximum normalization and atomicity. (See Chapter 9 to refresh your memory on the definition of those terms.) More tables are required, and several of these would be linked by queries containing the keyword JOIN.

A forum requires additional tables for threads and replies. With earlier versions of XAMPP, MySQL, and PHP, there is a mixture of storage engines for the tables. Some use INNODB, and others use MyISAM. Later versions use INNODB only.

The message board was moderated by the administrator as a result of an automatic e-mail containing the name of the poster and the date. When the quote was submitted, it was inserted immediately into the database table; a better solution is to create a temporary table for postings so that the quotes are not accessible to members until the moderator approves them.

It would be helpful if the administrator had facilities to delete or edit a quote without using phpMyAdmin. His admin page could display a table of recent posts that contained links for editing and deleting as described in Chapter 3.

The minimum number of tables required for a forum is four, as shown in Figure 10-14.

Figure 10-14. The tables for a forum

If you would like to experiment with a more complex forum similar to the one shown in Figure 10-14, you can download a tutorial and the code from the following web site:

http://net.tutsplus.com/tutorials/php/how-to-create-a-phpmysql-powered-forum-from-scratch/?search_index=36

Summary

In this chapter, we studied the plan and structure for a basic message board. We created a registration page and login form. We developed a gateway to two pages of quotes and then created those pages. We learned how to create a form for posting messages. We created two search forms: one enabled members to search for a list of their own postings, and another was designed to undertake full text searches. Some enhancements to the basic message board were suggested, and finally a brief outline of a basic forum was provided together with a resource for exploring it. The next chapter describes a basic e-commerce web site.