17. Example—Message Board
The functionality of a message board (aka a forum) is really rather simple: a post can either start a new topic or be in response to an existing one; posts are added to a database and then displayed on a page. That’s really about it. Of course, sometimes implementing simple concepts can be quite hard!
To make this example even more exciting and useful, it’s not going to be just a message board but rather a multilingual message board. Each language will have its own forum, and the key elements—navigation, prompts, introductory text, etc.—will be language-specific.
In order to focus on the most important aspects of this Web application, this chapter omits some others. The three glaring omissions will be: user management, error handling, and administration. This shouldn’t be a problem for you, though, as the next chapter goes over user management and error handling in great detail. As for the administration, you’ll find some recommendations at the chapter’s end.
Making the Database
The first step, naturally, is to create the database. A sample message board database 
is developed in Chapter 6, “Database Design.” Although that database is perfectly fine, a variation on it will be used here instead 
. I’ll compare and contrast the two to better explain the changes.
The model for the forum database developed in Chapter 6.
The revised model for the forum database to be used in this chapter.
To start, the forums table is replaced with a languages table. Both serve the same purpose: allowing for multiple forums. In this new database, the topic—PHP and MySQL for Dynamic Web Sites—will be the same in every forum, but each forum will use a different language. The posts will differ in each forum (this won’t be a translation of the same forum in multiple languages). The languages table stores the name of a language in its own alphabet and in English, for the administrator’s benefit (this assumes, of course, that English is the administrator’s primary language).
The threads table in the new database acts like the messages table in the old one, with one major difference. Just as the old messages table relates to forums, threads relates to the languages and users tables (each message can only be in one forum and by one user; each forum can have multiple messages, and each user can post multiple messages). However, this threads table will only store the subject, not the message itself. There are a couple of reasons for this change. First, having a subject repeat multiple times with each reply (replies, in my experience, almost always have the same subject anyway) is unnecessary. Second, the same goes for the lang_id association (it doesn’t need to be in each reply as long as each reply is associated with a single thread). Third, I’m changing the way a thread’s hierarchy will be indicated in this database (you’ll see how in the next paragraph), and changing the table structures helps in that regard. Finally, the threads table will be used every time a user looks at the posts in a forum. Removing the message bodies from that table will improve the performance of those queries.
Moving on to the posts table, its sole purpose is to store the actual bodies of the messages associated with a thread. In Chapter 6’s database, the messages table had a parent_id column, used to indicate the message to which a new message was a response. It was hierarchical: message 3 might be the starting post; message 18 might be a response to 3, message 20 a response to 18, and so on 
. That version of the database more directly indicated the responses; this version will only store the thread that a message goes under: messages 18 and 20 both use a thread_id of 3. This alteration will make showing a thread much more efficient (in terms of the PHP and MySQL required), and the date/time that each message was posted can be used to order them.
How the relationship among messages was indicated using the older database schema.
Those three tables provide the bulk of the forum functionality. The database also needs a users table. In this version of the forum, only registered users can post messages, which I think is a really, really, really good policy (it cuts way down on spam and hack attempts). Registered users can also have their default language (from the languages table) and time zone recorded along with their account information, in order to give them a more personalized experience. A combination of their username and password would be used to log in.
The final table, words, is necessary to make the site multilingual. This table will store translations of common elements: navigation links, form prompts, headers, and so forth. Each language in the site will have one record in this table. It’ll be a nice and surprisingly easy feature to use. Arguably, the words listed in this table could also go in the languages table, but then the implication would be that the words are also related to the threads table, which would not be the case.
That’s the thinking behind this new database design. You’ll learn more as you create the tables in the following steps. As with the other examples in this book, you can also download the SQL necessary for this chapter—the commands suggested in these steps, plus more—from the book’s corresponding Web site (www.LarryUllman.com).
To make the database
1. Access your MySQL server and set the character set to be used for communicating 
:
CHARSET utf8;
In order to use Unicode data in queries, you need to change the character set used to communicate with MySQL.
I’ll be using the mysql client in the figures, but you can use whatever interface you’d like. The first step, though, has to be changing the character set to UTF-8 for the queries to come. If you don’t do this, some of the characters in the queries will be stored as gibberish in the database (see the sidebar “Strange Characters”). Note that if you’re using phpMyAdmin, you’ll need to establish the character set in its configuration file.
CREATE DATABASE forum2 CHARACTER SET utf8;
USE forum2;
Creating and selecting the database for this example. This database uses the UTF-8 character set, so that it can support multiple languages.
So as not to muddle things with the tables created in the original forum database (from Chapter 6), a new database will be created.
If you’re using a hosted site and cannot create your own databases, use the database provided for you and select that. If your existing database has tables with these same names—words, languages, threads, users, and posts, rename the tables (either the existing or the new ones) and change the code in the rest of the chapter accordingly.
Whether you create this database from scratch or use a new one, it’s very important that the tables use the UTF-8 encoding, in order to be able to support multiple languages (see Chapter 6 for more). If you’re using an existing database and don’t want to potentially cause problems by changing the character set for all of your tables, just add the CHARACTER SET utf8 clause to each table definition (Steps 3 through 7).
3. Create the languages table 
:
CREATE TABLE languages (
lang_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
lang VARCHAR(60) NOT NULL,
lang_eng VARCHAR(20) NOT NULL,
PRIMARY KEY (lang_id),
UNIQUE (lang)
);
Creating the languages table.
This is the simplest table of the bunch. There won’t be many languages represented, so the primary key (lang_id) can be a TINYINT. The lang column is defined a bit larger, as it’ll store characters in other languages, which may require more space. This column must also be unique. Note that I don’t call this column “language,” as that’s a reserved keyword in MySQL (actually, I could still call it that, and you’ll see what would be required to do that in Step 7). The lang_eng column is the English equivalent of the language so that the administrator can easily see which languages are which.
CREATE TABLE threads (
thread_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
lang_id TINYINT(3) UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
subject VARCHAR(150) NOT NULL,
PRIMARY KEY (thread_id),
INDEX (lang_id),
INDEX (user_id)
);
Creating the threads table. This table stores the topic subjects and associates them with a language (i.e., a forum).
The threads table contains four columns and relates to both the languages and users tables (through the lang_id and user_id foreign keys, respectively). The subject here needs to be long enough to store subjects in multiple languages (characters take up more bytes in non-Western languages).
The columns that will be used in joins and WHERE clauses—lang_id and user_id—are indexed, as is thread_id (as a primary key, it’ll be indexed).
5. Create the posts table 
:
CREATE TABLE posts (
post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
thread_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
message TEXT NOT NULL,
posted_on DATETIME NOT NULL,
PRIMARY KEY (post_id),
INDEX (thread_id),
INDEX (user_id)
);
Creating the posts table, which links to both threads and users.
The main column in this table is message, which stores each post’s body. Two columns are foreign keys, tying into the threads and users tables. The posted_on column is of type DATETIME, but will use UTC (Coordinated Universal Time, see Chapter 6). Nothing special needs to be done here for that, though.
6. Create the users table 
:
CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
lang_id TINYINT UNSIGNED NOT NULL,
time_zone VARCHAR(30) NOT NULL,
username VARCHAR(30) NOT NULL,
pass CHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE (username),
UNIQUE (email),
INDEX login (username, pass)
);
Creating a bare-bones version of the users table.
For the sake of brevity, I’m omitting some of the other columns you’d put in this table, such as registration date, first name, and last name. For more on creating and using a table like this, see the next chapter.
In my thinking about this site, I expect users will select their preferred language and time zone when they register, so that they can have a more personalized experience. They can also have a username, which will be displayed in posts (instead of their email address). Both the username and the email address must be unique, which is something you’d need to address in the registration process.
CREATE TABLE words (
word_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
lang_id TINYINT UNSIGNED NOT NULL,
title VARCHAR(80) NOT NULL,
intro TINYTEXT NOT NULL,
home VARCHAR(30) NOT NULL,
forum_home VARCHAR(40) NOT NULL,
`language` VARCHAR(40) NOT NULL,
register VARCHAR(30) NOT NULL,
login VARCHAR(30) NOT NULL,
logout VARCHAR(30) NOT NULL,
new_thread VARCHAR(40) NOT NULL,
subject VARCHAR(30) NOT NULL,
body VARCHAR(30) NOT NULL,
submit VARCHAR(30) NOT NULL,
posted_on VARCHAR(30) NOT NULL,
posted_by VARCHAR(30) NOT NULL,
replies VARCHAR(30) NOT NULL,
latest_reply VARCHAR(40) NOT NULL,
post_a_reply VARCHAR(40) NOT NULL,
PRIMARY KEY (word_id),
UNIQUE (lang_id)
);
Creating the words table, which stores representations of key words in different languages.
This table will store different translations of common elements used on the site. Some elements—home, forum_home, language, register, login, logout, and new_thread—will be the names of links. Other elements—subject, body, submit—are used on the page for posting messages. Another category of elements are those used on the forum’s main page: posted_on, posted_by, replies, and latest_reply.
Some of these will be used multiple times in the site, and yet, this is still an incomplete list. As you implement the site yourself, you’ll see other places where word definitions could be added.
Each column is of VARCHAR type, except for intro, which is a body of text to be used on the main page. Most of the columns have a limit of 30, allowing for characters in other languages that require more bytes, except for a handful of columns that might need to be bigger.
For each column, its name implies the value to be stored in that column. For one—language—I’ve used a MySQL keyword, simply to demonstrate how that can be done. The fix is to surround the column’s name in backticks so that MySQL doesn’t confuse this column’s name with the keyword “language.”
8. Populate the languages table:
INSERT INTO languages (lang, lang_eng) VALUES
('English', 'English'),
('Português', 'Portuguese'),
('Français', 'French'),
('Norsk', 'Norwegian'),
('Romanian', 'Romanian'),
('
', 'Greek'),
('Deutsch', 'German'),
('Srpski', 'Serbian'),
('
', 'Japanese'),
('Nederlands', 'Dutch'),
This is just a handful of the languages the site will represent thanks to some assistance provided me (see the sidebar “A Note on Translations”). For each, the native and English word for that language is stored 
.
The populated languages table, with each language written in its own alphabet.
9. Populate the users table 
:
INSERT INTO users (lang_id, time_zone, username, pass, email) VALUES
(1, 'America/New_York', 'troutster', SHA1('password'), '[email protected]'),
(7, 'Europe/Berlin', 'Ute', SHA1('pa24word'), '[email protected]'),
(4, 'Europe/Oslo', 'Silje', SHA1('2kll13'), '[email protected]'),
(2, 'America/Sao_Paulo', 'João', SHA1('fJDLN34'), '[email protected]'),
(1, 'Pacific/Auckland', 'kiwi', SHA1('conchord'), '[email protected]'),
A few users are added manually, as there is no registration process in this site (but see Chapter 18, “Example—User Registration,” for that).
Because the PHP scripts will show the users associated with posts, a couple of users are necessary. A language and a time zone are associated with each (see Chapter 6 for more on time zones in MySQL). Each user’s password will be encrypted with the SHA1( ) function.
10. Populate the words table:
INSERT INTO words VALUES
(NULL, 1, 'PHP and MySQL for Dynamic Web Sites: The Forum!', '<p>Welcome to our site.... please use the links above... blah, blah, blah.</p>
<p>Welcome to our site....please use the links above...blah, blah, blah.</p>', 'Home', 'Forum Home', 'Language', 'Register', 'Login', 'Logout', 'New Thread', 'Subject', 'Body', 'Submit', 'Posted on', 'Posted by', 'Replies', 'Latest Reply', 'Post a Reply'),
These are the words associated with each term in English. The record has a lang_id of 1, which matches the lang_id for English in the languages table. The SQL to insert words for other languages into this table is available from the book’s supporting Web site.
This chapter doesn’t go through the steps for creating the mysqli_connect.php page, which connects to the database. Instead, just copy the one from Chapter 9, “Using PHP with MySQL.” Then change the parameters in the script to use a valid username/password/hostname combination to connect to the forum2 database.
As a reminder, the foreign key in one table should be of the exact same type and size as the matching primary key in another table.
Writing the Templates
This example, like any site containing lots of pages, will make use of a template to separate out the bulk of the presentation from the logic. Following the instructions laid out in Chapter 3, “Creating Dynamic Web Sites,” a header file and a footer file will store most of the HTML code. Each PHP script will then include these files to make a complete HTML page . But this example is a little more complicated.
The basic layout and appearance of the site.
One of the goals of this site is to serve users in many different languages. Accomplishing that involves not just letting them post messages in their native language but making sure they can use the whole site in their native language as well. This means that the page title, the navigation links, the captions, the prompts, and even the menus need to appear in their language .
The home page viewed in French (compare with 
).
The instructions for making the database show how this is accomplished: by storing translations of all key words in a table. The header file, therefore, needs to pull out all these key words so that they can be used as needed. Secondarily, this header file will also show different links based upon whether the user is logged in or not. Adding just one more little twist: if the user is on the forum page, viewing all the threads in a language, the user will also be given the option to post a new thread .
The same home page as in , but with an added link allowing the user to start a new thread.
The template itself uses CSS for some formatting (there’s not much to it, really). You can download all these files from the book’s supporting Web site (www.LarryUllman.com).
To make the template
1. Begin a new document in your text editor or IDE, to be named header.html (Script 17.1):
<?php # Script 17.1 - header.html
header ('Content-Type: text/html; charset=UTF-8'),
As this script will need to do a fair amount of data validation and retrieval, it starts with a PHP block. The script also indicates to the Web browser its encoding—UTF-8, using the header( ) function. The idea of setting the encoding via a header( ) function call was mentioned in a tip in Chapter 11, “Web Application Development.”
2. Start a session:
$_SESSION['user_id'] = 1;
$_SESSION['user_tz'] = 'America/New_York';
// $_SESSION = array( );
To track users after they log in, the site will use sessions. Since the site doesn’t have registration and login functionality in this chapter, two lines can virtually log in the user. Ordinarily, both values would come from a database, but they’ll be set here for testing purposes. To virtually log the user out, uncomment the third line.
3. Include the database connection:
require ('../mysqli_connect.php'),
As with many other examples in this book, the assumption is that the mysqli_connect.php script is stored in the directory above the current one, outside of the Web root. If that won’t be the case for you, change this code accordingly.
Script 17.1. The header.html file begins the template. It also sets the page’s encoding, starts the session, and retrieves the language-specific key words from the database.
1 <?php # Script 17.1 - header.html
2 /* This script...
3 * - starts the HTML template.
4 * - indicates the encoding using header( ).
5 * - starts the session.
6 * - gets the language-specific words
from the database.
7 * - lists the available languages.
8 */
9
10 // Indicate the encoding:
11 header ('Content-Type: text/html;
charset=UTF-8'),
12
13 // Start the session:
14 session_start( );
15
16 // For testing purposes:
17 $_SESSION['user_id'] = 1;
18 $_SESSION['user_tz'] = 'America/New_York';
19 // For logging out:
20 //$_SESSION = array( );
21
22 // Need the database connection:
23 require ('../mysqli_connect.php'),
24
25 // Check for a new language ID...
26 // Then store the language ID in the
session:
27 if ( isset($_GET['lid']) &&
28 filter_var($_GET['lid'], FILTER_
VALIDATE_INT, array('min_range' => 1))
29 ) {
30 $_SESSION['lid'] = $_GET['lid'];
31 } elseif (!isset($_SESSION['lid'])) {
32 $_SESSION['lid'] = 1; // Default.
33 }
34
35 // Get the words for this language:
36 $q = "SELECT * FROM words WHERE lang_id
= {$_SESSION['lid']}";
37 $r = mysqli_query($dbc, $q);
38 if (mysqli_num_rows($r) == 0) { //
Invalid language ID!
39
40 // Use the default language:
41 $_SESSION['lid'] = 1; // Default.
42 $q = "SELECT * FROM words WHERE
lang_id = {$_SESSION['lid']}";
43 $r = mysqli_query($dbc, $q);
44
45 }
46
47 // Fetch the results into a variable:
48 $words = mysqli_fetch_array($r,
MYSQLI_ASSOC);
49
50 // Free the results:
51 mysqli_free_result($r);
52 ?>
53 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML
1.0 Transitional//EN"
54 "http://www.w3.org/TR/xhtml1/DTD/
xhtml1-transitional.dtd">
55 <html xmlns="http://www.w3.org/1999/xhtml"
xml:lang="en" lang="en">
56 <head>
57 <meta http-equiv="content-type"
content="text/html; charset=utf-8" />
58 <title><?php echo $words['title']; ?>
</title>
59 <style type="text/css" media="screen">
60 body { background-color: #ffffff; }
61
62 .content {
63 background-color: #f5f5f5;
64 padding-top: 10px; padding-right:
10px; padding-bottom: 10px;
padding-left: 10px;
65 margin-top: 10px; margin-right:
10px; margin-bottom: 10px;
margin-left: 10px;
66 }
67
68 a.navlink:link { color: #003366;
text-decoration: none; }
69 a.navlink:visited { color: #003366;
text-decoration: none; }
70 a.navlink:hover { color: #cccccc;
text-decoration: none; }
71
72 .title {
73 font-size: 24px; font-weight:
normal; color: #ffffff;
74 margin-top: 5px; margin-bottom:
5px; margin-left: 20px;
75 padding-top: 5px; padding-bottom:
5px; padding-left: 20px;
76 }
77 </style>
78 </head>
79 <body>
80
81 <table width="90%" border="0"
cellspacing="10" cellpadding="0"
align="center">
82
83 <tr>
84 <td colspan="2" bgcolor="#003366"
align="center"><p class="title"><?php
echo $words['title']; ?></p></td>
85 </tr>
86
87 <tr>
88 <td valign="top" nowrap="nowrap"
width="10%"><b>
89 <?php // Display links:
90
91 // Default links:
92 echo '<a href="index.php"
class="navlink">' . $words['home'] .
'</a><br />
93 <a href="forum.php" class="navlink">' .
$words['forum_home'] . '</a><br />';
94
95 // Display links based upon login status:
96 if (isset($_SESSION['user_id'])) {
97
98 // If this is the forum page, add a
link for posting new threads:
99 if (basename($_SERVER['PHP_SELF']) ==
'forum.php') {
100 echo '<a href="post.php"
class="navlink">' . $words['new_
thread'] . '</a><br />';
101 }
102
103 // Add the logout link:
104 echo '<a href="logout.php"
class="navlink">' . $words['logout'] .
'</a><br />';
105
106 } else {
107
108 // Register and login links:
109 echo '<a href="register.php"
class="navlink">' . $words['register']
. '</a><br />
110 <a href="login.php" class="navlink">'
. $words['login'] . '</a><br />';
111
112 }
113
114 // For choosing a forum/language:
115 echo '</b><p><form action="forum.php"
method="get">
116 <select name="lid">
117 <option value="0">' . $words['language']
. '</option>
118 ';
119
120 // Retrieve all the languages...
121 $q = "SELECT lang_id, lang FROM
languages ORDER BY lang_eng ASC";
122 $r = mysqli_query($dbc, $q);
123 if (mysqli_num_rows($r) > 0) {
124 while ($menu_row = mysqli_fetch_
array($r, MYSQLI_NUM)) {
125 echo "<option value="$menu_row
[0]">$menu_row[1]</option>
";
126 }
127 }
128 mysqli_free_result($r);
129
130 echo '</select><br />
131 <input name="submit" type="submit"
value="' . $words['submit'] . '" />
132 </form></p>
133 </td>
134
135 <td valign="top" class="content">';
136 ?>
if ( isset($_GET['lid']) &&
filter_var($_GET['lid'], FILTER_VALIDATE_INT, array('min_range' => 1))
) {
$_SESSION['lid'] = $_GET['lid'];
} elseif (!isset($_SESSION['lid'])) {
$_SESSION['lid'] = 1; // Default.
}
Next, the language ID value (abbreviated lid) needs to be established. The language ID controls what language is used for all of the site elements, and it also dictates the forum to be viewed. The language ID could be found in the session, after retrieving that information upon a successful login (because the user’s language ID is stored in the users table). Alternatively, any user can change the displayed language on the fly by submitting the language form in the navigation links (see ). In that case, the submitted language ID needs to be validated as an integer greater than 1: easily accomplished using the Filter extension (see Chapter 13, “Security Approaches”). If you’re not using a version of PHP that supports the Filter extension, you’ll need to use typecasting here instead (again, see Chapter 13).
The second clause applies if the page did not receive a language ID in the URL and the language ID has not already been established in the session. In that case, a default language is selected. This value corresponds to English in the languages table in the database. You can change it to any ID that matches the default language you’d like to use.
5. Get the keywords for this language:
$q = "SELECT * FROM words WHERE lang_id = {$_SESSION['lid']}";
$r = mysqli_query($dbc, $q);
The next step in the header file is to retrieve from the database all of the key words for the given language.
6. If the query returned no records, get the default words:
if (mysqli_num_rows($r) = = 0) {
$_SESSION['lid'] = 1;
$q = "SELECT * FROM words WHERE lang_id = {$_SESSION['lid']}";
$r = mysqli_query($dbc, $q);
}
It’s possible, albeit unlikely, that $_SESSION['lid'] does not equate to a record from the words table. In that case the query would return no records (but run without error). Consequently, the default language words must now be retrieved. Notice that neither this block of code, nor that in Step 5, actually fetches the returned record. That will happen, for both potential queries, in Step 7.
7. Fetch the retrieved words into an array, free the resources, and close the PHP section:
$words = mysqli_fetch_array($r, MYSQLI_ASSOC);
mysqli_free_result($r);
?>
After this point, the $words array represents all of the navigation and common elements in the user’s selected language (or the default language).
Calling mysqli_free_result( ) isn’t necessary, but makes for tidy programming.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title><?php echo
$words['title']; ?></title>
Note that the encoding is also indicated in a META tag, even though the PHP header( ) call already identifies the encoding. This is just a matter of being thorough.
The header file as written uses as the title of every page a value in the $words array (i.e., the page title will always be the same for every page in a chosen language). You could easily modify this code so that the page’s title is a combination of the language word and a page-specific variable, such as $page_title used in Chapter 3 and subsequent examples.
9. Add the CSS:
<style type="text/css"
media="screen">
body { background-color: #ffffff; }
.content {
background-color: #f5f5f5;
padding-top: 10px; padding-right: 10px; padding-bottom: 10px; padding-left: 10px;
margin-top: 10px; margin-right: 10px; margin-bottom: 10px; margin-left: 10px;
}
a.navlink:link { color: #003366; text-decoration: none; }
a.navlink:visited { color: #003366; text-decoration: none; }
a.navlink:hover { color: #cccccc; text-decoration: none; }
.title {
font-size: 24px; font-weight: normal; color: #ffffff;
margin-top: 5px; margin-bottom: 5px; margin-left: 20px;
padding-top: 5px; padding-bottom: 5px; padding-left: 20px;
}
</style>
This is all taken from a template I found somewhere some time ago. It adds a little decoration to the site.
10. Complete the HTML head and begin the page:
</head>
<body>
<table width="90%" border="0" cellspacing="10" cellpadding="0" align="center">
<tr>
<td colspan="2" bgcolor= "#003366" align="center"> <p class="title"><?php echo $words['title']; ?></p></td>
</tr>
<tr>
<td valign="top" nowrap= "nowrap" width="10%"><b>
The page itself uses a table for the layout, with one row showing the page title, the next row containing the navigation links on the left and the page-specific content on the right, and the final row containing the copyright . You’ll see in this code that the page title will also be language-specific.
The page layout showing the rows and columns of the main HTML table.
11. Start displaying the links:
<?php
echo '<a href="index.php" class="navlink">' . $words['home'] . '</a><br />
<a href="forum.php" class="navlink">' . $words['forum_home'] . '</a><br />';
The first two links will always appear, whether the user is logged in or not, and regardless of the page they’re currently viewing. For each link, the text of the link itself will be language-specific.
12. If the user is logged in, show “new thread” and logout links:
if (isset($_SESSION['user_id'])) {
if (basename($_SERVER['PHP_SELF']) = = 'forum.php') {
echo '<a href="post.php" class="navlink">' . $words['new_thread'] . '</a><br />';
}
echo '<a href="logout.php" class="navlink">' . $words['logout'] . '</a><br />';
Confirmation of the user’s logged-in status is achieved by checking for the presence of a $_SESSION['user_id'] variable. If it’s set, then the logout link can be created. Before that, a check is made to see if this is the forum.php page. If so, then a link to start a new thread is created (users can only create new threads if they’re on the forum page; you wouldn’t want them to create a new thread on some of the other pages, like the home page, because it wouldn’t be clear to which forum the thread should be posted). The code for checking what page it is, using the basename( ) function, was first introduced in Chapter 12, “Cookies and Sessions.”
13. Display the links for users not logged in:
} else {
echo '<a href="register.php" class="navlink">' . $words['register'] . '</a><br />
<a href="login.php" class= "navlink">' . $words['login'] . '</a><br />';
}
If the user isn’t logged in, links are provided for registering and logging in.
14. Start a form for choosing a language:
echo '</b><p><form action="forum.php" method="get">
<select name="lid">
<option value="0">' . $words ['language'] . '</option>
';
The user can choose a language (which is also a forum), via a pull-down menu . The first value in the menu will be the word “language,” in the user’s default language. The select menu’s name is lid, short for language ID, and its action attribute points to forum.php. When the user submits this simple form, they’ll be taken to the forum of their choice.
The language pull-down menu, with each option in its native language.
15. Retrieve every language from the database, and add each to the menu:
$q = "SELECT lang_id, lang FROM languages ORDER BY lang_eng ASC";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) {
while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
echo "<option value="$menu_row[0]">$menu_row[1] </option>
";
}
}
mysqli_free_result($r);
This query retrieves the languages and the language ID from the languages table. Each is added as an option to the select menu.
Again, calling mysqli_free_result( ) isn’t required, but doing so can help limit bugs and improve performance. In particular, when you have pages that run multiple SELECT queries, mysqli_free_result( ) can help avoid confusion issues between PHP and MySQL.
16. Complete the form and the PHP page:
echo '</select><br />
<input name="submit" type="submit" value="' . $words['submit'] . '" />
</form></p>
</td>
<td valign="top" class="content">';
?>
17. Save the file as header.html.
Even though it contains a fair amount of PHP, this script will still use the .html extension (which I prefer to use for template files). Make sure that the file is saved using UTF-8 encoding.
18. Create a new document in your text editor or IDE, to be named footer.html (Script 17.2):
<!-- Script 17.2 - footer.html -->
19. Complete the HTML page:
</td>
</tr>
<tr>
<td colspan="2"
align="center">© 2011 Larry Ullman</td>
</tr>
</table>
</body>
</html>
20. Save the file as footer.html.
Again, make sure that the file is saved using UTF-8 encoding.
21. Place both files in your Web directory, within a folder named includes.
Script 17.2. The footer file completes the HTML page.
1 <!-- Script 17.2 - footer.html -->
2 </td>
3 </tr>
4
5 <tr>
6 <td colspan="2" align="center">©
2011 Larry Ullman</td>
7 </tr>
8
9 </table>
10 </body>
11 </html>
Creating the Index Page
The index page in this example won’t do that much. It’ll provide some introductory text and the links for the user to register, log in, choose the preferred language/forum, and so forth. From a programming perspective, it’ll show how the template files are to be used.
To make the home page
1. Begin a new PHP document in your text editor or IDE, to be named index.php (Script 17.3):
<?php # Script 17.3 - index.php
Because all of the HTML is in the included files, this page can begin with the opening PHP tags.
2. Include the HTML header:
include ('includes/header.html'),
The included file uses the header( ) and session_start( ) functions, so you have to make sure that nothing is sent to the Web browser prior to this line. That shouldn’t be a problem as long as there are no spaces before the opening PHP tag.
3. Print the language-specific content:
echo $words['intro'];
The $words array is defined within the header file. It can be referred to here, since the header file was just included. The value indexed at intro is a bit of welcoming text in the selected or default language.
4. Complete the page:
include ('includes/footer.html'),
?>
That’s it for the home page!
5. Save the file as index.php, place it in your Web directory, and test it in your Web browser (see and 
in the previous section).
Once again, make sure that the file is saved using UTF-8 encoding. This will be the last time I remind you!
Script 17.3. The home page includes the header and footer files to make a complete HTML document. It also prints some introductory text in the chosen language.
1 <?php # Script 17.3 - index.php
2 // This is the main page for the site.
3
4 // Include the HTML header:
5 include ('includes/header.html'),
6
7 // The content on this page is introductory text
8 // pulled from the database, based upon the
9 // selected language:
10 echo $words['intro'];
11
12 // Include the HTML footer file:
13 include ('includes/footer.html'),
14 ?>
Creating the Forum Page
The next page in the Web site is the forum page, which displays the threads in a forum (each language being its own forum). The page will use the language ID, passed to this page in a URL and/or stored in a session, to know what threads to display.
The basic functionality of this page—running a query, displaying the results—is simple . The query this page uses is perhaps the most complex one in the book. It’s complicated for three reasons:
The forum page, which lists information about the threads in a given language. The threads are linked to a page where they can be read.
1. It performs a JOIN across three tables.
2. It uses three aggregate functions and a GROUP BY clause.
3. It converts the dates to the user’s time zone, but only if the person viewing the page is logged in.
So, again, the query is intricate, but I’ll go through it in detail in the following steps.
To write the forum page
1. Begin a new PHP document in your text editor or IDE, to be named forum.php (Script 17.4):
<?php # Script 17.4 - forum.php
include ('includes/header.html'),
Script 17.4. This script performs one rather complicated query to display five pieces of information—the subject, the original poster, the date the thread was started, the number of replies, and the date of the latest reply—for each thread in a forum.
1 <?php # Script 17.4 - forum.php
2 // This page shows the threads in a
forum.
3 include ('includes/header.html'),
4
5 // Retrieve all the messages in this
forum...
6
7 // If the user is logged in and has
chosen a time zone,
8 // use that to convert the dates and
times:
9 if (isset($_SESSION['user_tz'])) {
10 $first = "CONVERT_TZ(p.posted_on,
'UTC', '{$_SESSION['user_tz']}')";
11 $last = "CONVERT_TZ(p.posted_on,
'UTC', '{$_SESSION['user_tz']}')";
12 } else {
13 $first = 'p.posted_on';
14 $last = 'p.posted_on';
15 }
16
17 // The query for retrieving all the threads in this forum, along with the original user,
18 // when the thread was first posted, when it was last replied to, and how many replies it's had:
19 $q = "SELECT t.thread_id, t.subject, username, COUNT(post_id) - 1 AS responses, MAX(DATE_FORMAT
($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first
FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id =
u.user_id WHERE t.lang_id = {$_SESSION['lid']} GROUP BY (p.thread_id) ORDER BY last DESC";
20 $r = mysqli_query($dbc, $q);
21 if (mysqli_num_rows($r) > 0) {
22
23 // Create a table:
24 echo '<table width="100%" border="0" cellspacing="2" cellpadding="2" align="center">
25 <tr>
26 <td align="left" width="50%"><em>' . $words['subject'] . '</em>:</td>
27 <td align="left" width="20%"><em>' . $words['posted_by'] . '</em>:</td>
28 <td align="center" width="10%"><em>' . $words['posted_on'] . '</em>:</td>
29 <td align="center" width="10%"><em>' . $words['replies'] . '</em>:</td>
30 <td align="center" width="10%"><em>' . $words['latest_reply'] . '</em>:</td>
31 </tr>';
32
33 // Fetch each thread:
34 while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
35
36 echo '<tr>
37 <td align="left"><a href="read.php?tid=' . $row['thread_id'] . '">' . $row['subject']
. '</a></td>
38 <td align="left">' . $row['username'] . '</td>
39 <td align="center">' . $row['first'] . '</td>
40 <td align="center">' . $row['responses'] . '</td>
41 <td align="center">' . $row['last'] . '</td>
42 </tr>';
43
44 }
45
46 echo '</table>'; // Complete the table.
47
48 } else {
49 echo '<p>There are currently no messages in this forum.</p>';
50 }
51
52 // Include the HTML footer file:
53 include ('includes/footer.html'),
54 ?>
2. Determine what dates and times to use:
if (isset($_SESSION['user_tz'])) {
$first = "CONVERT_TZ (p.posted_on, 'UTC', '{$_SESSION['user_tz']}')";
$last = "CONVERT_TZ(p.posted_on, 'UTC', '{$_SESSION['user_tz']}')";
} else {
$first = 'p.posted_on';
$last = 'p.posted_on';
}
As already stated, the query will format the date and time to the user’s time zone (presumably selected during the registration process), but only if the viewer is logged in. Presumably, this information would be retrieved from the database and stored in the session upon login.
To make the query dynamic, what exact date/time value should be selected will be stored in a variable to be used in the query later in the script. If the user is not logged in, which means that $_SESSION['user_tz'] is not set, the two dates—when a thread was started and when the most recent reply was posted—will be unadulterated values from the table. In both cases, the table column being referenced is posted_on in the posts table (p will be an alias to posts in the query).
If the user is logged in, the CONVERT_TZ( ) function will be used to convert the value stored in posted_on from UTC to the user’s chosen time zone. See Chapter 6 for more on this function. Note that using this function requires that your MySQL installation includes the list of time zones (see Chapter 6 for more).
3. Define and execute the query:
$q = "SELECT t.thread_id, t.subject, username, COUNT (post_id) - 1 AS responses, MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} GROUP BY (p.thread_id) ORDER BY last DESC";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) {
The query needs to return six things: the ID and subject of each thread (which comes from the threads table), the name of the user who posted the thread in the first place (from users), the number of replies to each thread, the date the thread was started, and the date the thread last had a reply (all from posts).
The overarching structure of this query is a join between threads and posts using the thread_id column (which is the same in both tables). This result is then joined with the users table using the user_id column.
As for the selected values, three aggregate functions are used (see Chapter 7): COUNT( ), MIN( ), and MAX( ). Each is applied to a column in the posts table, so the query has a GROUP BY (p.thread_id) clause. MIN( ) and MAX( ) are used to return the earliest (for the original post) and latest dates. Both will be shown on the forum page (see ). The latest date is also used to order the results so that the most recent activity always gets returned first. The COUNT( ) function is used to count the number of posts in a given thread. Because the original post is also in the posts table, it’ll be factored into COUNT( ) as well, so 1 is subtracted from that value.
Finally, aliases are used to make the query shorter to write and to make it easier to use the results in the PHP script. If you’re confused by what this query returns, execute it using the mysql client or phpMyAdmin.
The results of running the complex query in the mysql client.
4. Create a table for the results:
echo '<table width="100%" border="0" cellspacing="2" cellpadding="2" align="center">
<tr>
<td align="left" width="50%"> <em>' . $words['subject'] . '</em>:</td>
<td align="left" width="20%"><em>' . $words ['posted_by'] . '</em>:</td>
<td align="center" width= "10%"><em>' . $words['posted_on'] . '</em>:</td>
<td align="center" width= "10%"><em>' . $words['replies'] . '</em>:</td>
<td align="center" width="10%"><em>' . $words['latest_reply'] . '</em>:</td>
</tr>';
As with some items in the header file, the captions for the columns in this HTML page will use language-specific terminology.
5. Fetch and print each returned record:
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
echo '<tr>
<td align="left"><a href= "read.php?tid=' . $row ['thread_id'] . '">' . $row['subject'] . '</a></td>
<td align="left">' . $row['username'] . '</td>
<td align="center">' . $row['first'] . '</td>
<td align="center">' . $row['responses'] . '</td>
<td align="center">' . $row['last'] . '</td>
</tr>';
}
This code is fairly simple, and there are similar examples many times over in the book. The thread’s subject is linked to read.php, passing that page the thread ID in the URL.
6. Complete the page:
echo '</table>';
} else {
echo '<p>There are currently no messages in this forum.</p>';
}
include ('includes/footer.html'),
?>
This else clause applies if the query returned no results. In actuality, this message should also be in the user’s chosen language. I’ve omitted that for the sake of brevity. To fully implement this feature, create another column in the words table and store for each language the translated version of this text.
7. Save the file as forum.php, place it in your Web directory, and test it in your Web browser .
The forum.php page, viewed in another language (compare with ).
If you see no values for the dates and times when you run this script, it is probably because your MySQL installation hasn’t been updated with the full list of time zones.
As noted in the chapter’s introduction, I’ve omitted all error handling in this example. If you have problems with the queries, apply the debugging techniques outlined in Chapter 8, “Error Handling and Debugging.”
Creating the Thread Page
Next up is the page for viewing all of the messages in a thread . This page is accessed by clicking a link in forum.php . Thanks to a simplified database structure, the query used by this script is not that complicated (with the database design from Chapter 6, this page would have been much more complex). All this page has to do then is make sure it receives a valid thread ID, display every message, and display the form for users to add their own replies.
The read. php page shows every message in a thread.
Part of the source code from forum.php shows how the thread ID is passed to read.php in the URL.
To make read.php
1. Begin a new PHP document in your text editor or IDE, to be named read.php (Script 17.5):
<?php # Script 17.5 - read.php
include ('includes/header.html'),
2. Begin validating the thread ID:
$tid = FALSE;
if (isset($_GET['tid']) && filter_var($_GET['tid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) {
$tid = $_GET['tid'];
To start, a flag variable is defined as FALSE, a way of saying: prove that the thread ID is valid, which is the most important aspect of this script. Next, a check confirms that the thread ID was passed in the URL and that it is an integer greater than 1. This is done using the Filter extension (see Chapter 13). Finally, the value passed to the page is assigned to the $tid variable, so that it no longer has a FALSE value.
If your version of PHP does not support the Filter extension, you’ll need to typecast $_GET['tid'] to an integer and then confirm that it has a value greater than 1 (as shown in Chapter 13).
Script 17.5. The read.php page shows all of the messages in a thread, in order of ascending posted date. The page also shows the thread’s subject at the top and includes a form for adding a reply at the bottom.
1 <?php # Script 17.5 - read.php
2 // This page shows the messages in a thread.
3 include ('includes/header.html'),
4
5 // Check for a thread ID...
6 $tid = FALSE;
7 if (isset($_GET['tid']) && filter_var($_GET['tid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) {
8
9 // Create a shorthand version of the thread ID:
10 $tid = $_GET['tid'];
11
12 // Convert the date if the user is logged in:
13 if (isset($_SESSION['user_tz'])) {
14 $posted = "CONVERT_TZ(p.posted_on, 'UTC', '{$_SESSION['user_tz']}')";
15 } else {
16 $posted = 'p.posted_on';
17 }
18
19 // Run the query:
20 $q = "SELECT t.subject, p.message, username, DATE_FORMAT($posted, '%e-%b-%y %l:%i %p') AS
posted FROM threads AS t LEFT JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON
p.user_id = u.user_id WHERE t.thread_id = $tid ORDER BY p.posted_on ASC";
21 $r = mysqli_query($dbc, $q);
22 if (!(mysqli_num_rows($r) > 0)) {
23 $tid = FALSE; // Invalid thread ID!
24 }
25
26 } // End of isset($_GET['tid']) IF.
27
28 if ($tid) { // Get the messages in this thread...
29
30 $printed = FALSE; // Flag variable.
31
32 // Fetch each:
33 while ($messages = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
34
35 // Only need to print the subject once!
36 if (!$printed) {
37 echo "<h2>{$messages['subject']}</h2>
";
38 $printed = TRUE;
39 }
40
41 // Print the message:
42 echo "<p>{$messages['username']} ({$messages['posted']})<br />{$messages['message']}</p><br />
";
43
44 } // End of WHILE loop.
45
46 // Show the form to post a message:
47 include ('includes/post_form.php'),
48
49 } else { // Invalid thread ID!
50 echo '<p>This page has been accessed in error.</p>';
51 }
52
53 include ('includes/footer.html'),
54 ?>
3. Determine if the dates and times should be adjusted:
if (isset($_SESSION['user_tz'])) {
$posted = "CONVERT_TZ (p.posted_on, 'UTC', '{$_SESSION['user_tz']}')";
} else {
$posted = 'p.posted_on';
}
As in the forum.php page (Script 17.4), the query will format all of the dates and times in the user’s time zone, if the user is logged in. To be able to adjust the query accordingly, this variable stores either the column’s name (posted_on, from the posts table) or the invocation of MySQL’s CONVERT_TZ( ) function.
4. Run the query:
$q = "SELECT t.subject, p.message, username, DATE_FORMAT($posted, '%e-%b-%y %l:%i %p') AS posted FROM threads AS t LEFT JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON p.user_id = u.user_id WHERE t.thread_id = $tid ORDER BY p.posted_on ASC";
$r = mysqli_query($dbc, $q);
if (!(mysqli_num_rows($r) > 0)) {
$tid = FALSE;
}
This query is like the query on the forum page, but it’s been simplified in two ways. First, it doesn’t use any of the aggregate functions or a GROUP BY clause. Second, it only returns one date and time. The query is still a JOIN across three tables, in order to get the subject, message bodies, and usernames. The records are ordered by their posted dates in ascending order (i.e., from the first post to the most recent).
If the query doesn’t return any rows, then the thread ID isn’t valid and the flag variable is made false again.
5. Complete the $_GET['tid'] conditional and check, again, for a valid thread ID:
} // End of isset($_GET['tid']) IF.
if ($tid) {
Before printing the messages in the thread, one last conditional is used. This conditional would be false if:
• No $_GET['tid'] value was passed to this page.
• A $_GET['tid'] value was passed to the page, but it was not an integer greater than 0.
• A $_GET['tid'] value was passed to the page and it was an integer greater than 0, but it matched no thread records in the database.
6. Print each message:
$printed = FALSE;
while ($messages = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
if (!$printed) {
echo
"<h2>{$messages['subject']}</h2>
";
$printed = TRUE;
}
echo "<p>{$messages['username']} ({$messages['posted']})<br /> {$messages['message']}</p> <br />
";
} // End of WHILE loop.
As you can see in , the thread subject needs to be printed only once. However, the query will return the subject for each returned message .
The results of the read.php query when run in the mysql client. This version of the query converts the dates to the logged-in user’s preferred time zone.
To achieve this effect, a flag variable is created. If $printed is FALSE, then the subject needs to be printed. This would be the case for the first row fetched from the database. Once that’s been displayed, $printed is set to TRUE so that the subject is not printed again. Then the username, posted date, and message are displayed.
7. Include the form for posting a message:
include ('includes/post_form.php'),
As users could post messages in two ways—as a reply to an existing thread and as the first post in a new thread, the form for posting messages is defined within a separate file (to be created next), stored within the includes directory.
8. Complete the page:
} else { // Invalid thread ID!
echo '<p>This page has been accessed in error.</p>';
}
include ('includes/footer.html'),
?>
Again, in a complete site, this error message would also be stored in the words table in each language. Then you would write here:
echo "<p>{$words['access_error']} </p>";
9. Save the file as read.php, place it in your Web directory, and test it in your Web browser .
The read.php page, viewed in Japanese.
Posting Messages
The final two pages in this application are the most important, because you won’t have threads to read without them. Two files for posting messages are required: One will make the form, and the other will handle the form.
Creating the form
The first page required for posting messages is post_form.php. It has some contingencies:
1. It can only be included by other files, never accessed directly.
2. It should only be displayed if the user is logged in (which is to say only logged-in users can post messages).
3. If it’s being used to add a reply to an existing message, it only needs a message body input .
The form for posting a message, as shown on the thread-viewing page.
4. If it’s being used to create a new thread, it needs both subject and body inputs .
The same form for posting a message, if being used to create a new thread.
5. It needs to be sticky .
The form will recall entered values when not completed correctly.
Still, all of this can be accomplished in 60 lines of code and some smart conditionals.
To create post_form.php
1. Begin a new PHP document in your text editor or IDE, to be named post_form.php (Script 17.6):
<?php # Script 17.6 - post_form.php
2. Redirect the Web browser if this page has been accessed directly:
if (!isset($words)) {
header ("Location: http://www.example.com/index.php");
exit( );
}
Script 17.6. This script will be included by other pages (notably, read.php and post.php). It displays a form for posting messages that is also sticky.
1 <?php # Script 17.6 - post_form.php
2 // This page shows the form for posting
messages.
3 // It's included by other pages, never
called directly.
4
5 // Redirect if this page is called
directly:
6 if (!isset($words)) {
7 header ("Location: http://www.example.
com/index.php");
8 exit( );
9 }
10
11 // Only display this form if the user is
logged in:
12 if (isset($_SESSION['user_id'])) {
13
14 // Display the form:
15 echo '<form action="post.php"
method="post" accept-charset="utf-8">';
16
17 // If on read.php...
18 if (isset($tid) && $tid) {
19
20 // Print a caption:
21 echo '<h3>' . $words['post_a_reply']
. '</h3>';
22
23 // Add the thread ID as a hidden
input:
24 echo '<input name="tid" type=
"hidden" value="' . $tid . '" />';
25
26 } else { // New thread
27
28 // Print a caption:
29 echo '<h3>' . $words['new_thread']
. '</h3>';
30
31 // Create subject input:
32 echo '<p><em>' . $words['subject']
. '</em>: <input name="subject"
type="text" size="60"
maxlength="100" ';
33
34 // Check for existing value:
35 if (isset($subject)) {
36 echo "value="$subject" ";
37 }
38
39 echo '/></p>';
40
41 } // End of $tid IF.
42
43 // Create the body textarea:
44 echo '<p><em>' . $words['body'] .
'</em>: <textarea name="body"
rows="10" cols="60">';
45
46 if (isset($body)) {
47 echo $body;
48 }
49
50 echo '</textarea></p>';
51
52 // Finish the form:
53 echo '<input name="submit" type="submit"
value="' . $words['submit'] . '" />
54 </form>';
55
56 } else {
57 echo '<p>You must be logged in to
post messages.</p>';
58 }
59
60 ?>
This script does not include the header and footer and therefore won’t make a complete HTML page. Consequently, the script must be included by a script that does all that. PHP has no been_included( ) function that will indicate if this page was included or loaded directly. Instead, since I know that the header file creates a $words variable, if that variable isn’t set, then header.html hasn’t been included prior to this script and the browser should be redirected.
Change the URL in the header( ) call to match your site.
3. Confirm that the user is logged in and begin the form:
if (isset($_SESSION['user_id'])) {
echo '<form action="post.php" method="post"
accept-charset="utf-8">';
Because only registered users can post, the script checks for the presence of $_SESSION['user_id'] before displaying the form. The form itself will be submitted to post.php, to be written next. The accept-charset attribute is added to the form to make it clear that UTF-8 text is acceptable (although this isn’t technically required, as each page uses the UTF-8 encoding already).
4. Check for a thread ID:
if (isset($tid) && $tid) {
echo '<h3>' . $words['post_a_reply'] . '</h3>';
echo '<input name="tid" type="hidden" value="' . $tid . '" />';
This is where things get a little bit tricky. As mentioned earlier, and as shown in and , the form will differ slightly depending upon how it’s being used. When included on read.php, the form will be used to provide a reply to an existing thread. To check for this scenario, the script sees if $tid (short for thread ID) is set and if it has a TRUE value. That will be the case when this page is included by read.php. When this script is included by post.php, $tid will be set but have a FALSE value.
If this conditional is true, the language-specific version of “Post a Reply” will be printed and the thread ID will be stored in a hidden form input.
5. Complete the conditional begun in Step 4:
} else { // New thread
echo '<h3>' . $words['new_thread'] . '</h3>';
echo '<p><em>' . $words['subject'] . '</em>: <input name="subject" type="text" size="60" maxlength="100" ';
if (isset($subject)) {
echo "value="$subject" ";
}
echo '/></p>';
} // End of $tid IF.
If this is not a reply, then the caption should be the language-specific version of “New Thread” and a subject input should be created. That input needs to be sticky. To check for that, look for the existence of a $subject variable. This variable will be created in post.php, and that file will then include this page.
6. Create the textarea for the message body:
echo '<p><em>' . $words['body'] . '</em>: <textarea name="body" rows="10" cols="60">';
if (isset($body)) {
echo $body;
}
echo '</textarea></p>';
Both uses of this page will have this textarea. Like the subject, it will be made sticky if a $body variable (defined in post.php) exists. For both inputs, the prompts will be language-specific.
7. Complete the form:
echo '<input name="submit" type="submit" value="' . $words['submit'] . '" />
</form>';
All that’s left is a language-specific submit button .
The form prompts and even the submit button will be in the user’s chosen language (compare with the other figures in this section of the chapter).
8. Complete the page:
} else {
echo '<p>You must be logged in to post messages.</p>';
}
?>
Once again, you could store this message in the words table and use the translated version here. I didn’t only for the sake of simplicity.
9. Save the file as post_form.php, place it in the includes folder of your Web directory, and test it in your Web browser by accessing read.php .
The result of the post_form.php page if the user is not logged in (remember that you can emulate not being logged in by using the $_SESSION = array( ); line in the header file).
Handling the form
This file, post.php, will primarily be used to handle the form submission from post_form.php. That sounds simple enough, but there’s a bit more to it. This page will actually be called in three different ways:
1. To handle the form for a thread reply
2. To display the form for a new thread submission
3. To handle the form for a new thread submission
This means that the page will be accessed using either POST (modes 1 and 3) or GET (mode 2). Also, the data that will be sent to the page, and therefore needs to be validated, will differ between modes 1 and 3 .
The various uses of the post.php page.
Adding to the complications, if a new thread is being created, two queries must be run: one to add the thread to the threads table and a second to add the new thread body to the posts table. If the submission is a reply to an existing thread, then only one query is required, inserting a record into posts.
Of course, successfully pulling this off is just a matter of using the right conditionals, as you’ll see. In terms of validation, the subject and body, as text types, will just be checked for a non-empty value. All tags will be stripped from the subject (because why should it have any?) and turned into entities in the body. This will allow for HTML, JavaScript, and PHP code to be written in a post but still not be executed when the thread is shown (because in a forum about Web development, you’ll need to show some code).
Script 17.7. The post.php page will process the form submissions when a message is posted. This page will be used to both create new threads and handle replies to existing threads.
1 <?php # Script 17.7 - post.php
2 // This page handles the message post.
3 // It also displays the form if creating
a new thread.
4 include ('includes/header.html'),
5
6 if ($_SERVER['REQUEST_METHOD'] == 'POST')
{ // Handle the form.
7
8 // Language ID is in the session.
9 // Validate thread ID ($tid), which
may not be present:
10 if (isset($_POST['tid']) && filter_var
($_POST['tid'], FILTER_VALIDATE_INT,
array('min_range' => 1)) ) {
11 $tid = $_POST['tid'];
12 } else {
13 $tid = FALSE;
14 }
15
16 // If there's no thread ID, a subject
must be provided:
17 if (!$tid && empty($_POST['subject'])) {
18 $subject = FALSE;
19 echo '<p>Please enter a subject
for this post.</p>';
20 } elseif (!$tid && !empty($_POST
['subject'])) {
21 $subject = htmlspecialchars(strip_
tags($_POST['subject']));
22 } else { // Thread ID, no need for
subject.
23 $subject = TRUE;
24 }
25
26 // Validate the body:
27 if (!empty($_POST['body'])) {
28 $body = htmlentities($_POST['body']);
29 } else {
30 $body = FALSE;
31 echo '<p>Please enter a body for this post.</p>';
32 }
33
34 if ($subject && $body) { // OK!
35
36 // Add the message to the database...
37
38 if (!$tid) { // Create a new thread.
39 $q = "INSERT INTO threads (lang_id, user_id, subject) VALUES ({$_SESSION['lid']},
{$_SESSION['user_id']}, '" . mysqli_real_escape_string($dbc, $subject) . "')";
40 $r = mysqli_query($dbc, $q);
41 if (mysqli_affected_rows($dbc) == 1) {
42 $tid = mysqli_insert_id($dbc);
43 } else {
44 echo '<p>Your post could not be handled due to a system error.</p>';
45 }
46 } // No $tid.
47
48 if ($tid) { // Add this to the replies table:
49 $q = "INSERT INTO posts (thread_id, user_id, message, posted_on) VALUES ($tid,
{$_SESSION['user_id']}, '" . mysqli_real_escape_string($dbc, $body) . "', UTC_TIMESTAMP( ))";
50 $r = mysqli_query($dbc, $q);
51 if (mysqli_affected_rows($dbc) == 1) {
52 echo '<p>Your post has been entered.</p>';
53 } else {
54 echo '<p>Your post could not be handled due to a system error.</p>';
55 }
56 } // Valid $tid.
57
58 } else { // Include the form:
59 include ('includes/post_form.php'),
60 }
61
62 } else { // Display the form:
63
64 include ('includes/post_form.php'),
65
66 }
67
68 include ('includes/footer.html'),
69 ?>
To create post.php
1. Begin a new PHP document in your text editor or IDE, to be named post.php (Script 17.7):
<?php # Script 17.7 - post.php
include ('includes/header.html'),
This page will use the header and footer files, unlike post_form.php.
2. Check for the form submission and validate the thread ID:
if ($_SERVER['REQUEST_METHOD'] = = 'POST') {
if (isset($_POST['tid']) && filter_var($_POST['tid'], FILTER_VALIDATE_INT, array ('min_range' => 1)) ) {
$tid = $_POST['tid'];
} else {
$tid = FALSE;
}
The thread ID will be present if the form was submitted as a reply to an existing thread (the thread ID is stored as a hidden input ). The validation process is fairly routine, thanks to the Filter extension.
The source code of read.php shows how the thread ID is stored in the form. This indicates to post.php that the submission is a reply, not a new thread.
3. Validate the message subject:
if (!$tid && empty($_POST ['subject'])) {
$subject = FALSE;
echo '<p>Please enter a subject for this post.</p>';
} elseif (!$tid && !empty($_POST ['subject'])) {
$subject = htmlspecialchars (strip_tags($_POST['subject']));
} else { // Thread ID, no need for subject.
$subject = TRUE;
}
The tricky part about validating the subject is that three scenarios exist. First, if there’s no valid thread ID, then this should be a new thread and the subject can’t be empty. If the subject element is empty, then an error occurred and a message is printed.
In the second scenario, there’s no valid thread ID and the subject isn’t empty, meaning this is a new thread and the subject was entered, so it should be handled. In this case, any tags are removed, using the strip_tags( ) function, and htmlspecialchars( ) will turn any remaining quotation marks into their entity format. Calling this second function will prevent problems should the form be displayed again and the subject placed in the input to make it sticky. To be more explicit, if the submitted subject contains a double quotation mark but the body wasn’t completed, the form will be shown again with the subject placed within value="", and the double quotation mark in the subject will cause problems.
The third scenario is when the form has been submitted as a reply to an existing thread. In that case, $tid will be valid and no subject is required.
4. Validate the body:
if (!empty($_POST['body'])) {
$body = htmlentities($_POST ['body']);
} else {
$body = FALSE;
echo '<p>Please enter a body for this post.</p>';
}
This is a much easier validation, as the body is always required. If present, it’ll be run through htmlentities( ).
5. Check if the form was properly filled out:
if ($subject && $body) {
6. Create a new thread, when appropriate:
if (!$tid) {
$q = "INSERT INTO threads (lang_id, user_id, subject) VALUES ({$_SESSION['lid']}, {$_SESSION['user_id']}, '" . mysqli_real_escape_string ($dbc, $subject) . "')";
$r = mysqli_query($dbc, $q);
if (mysqli_affected_rows($dbc) = = 1) {
$tid = mysqli_insert_id ($dbc);
} else {
echo '<p>Your post could not be handled due to a system error.</p>';
}
}
If there’s no thread ID, then this is a new thread and a query must be run on the threads table. That query is simple, populating the three columns. Two of these values come from the session (after the user has logged in). The other is the subject, which is run through mysqli_real_escape_string( ). Because the subject already had strip_tags( ) and htmlspecialchars( ) applied to it, you could probably get away with not using this function, but there’s no need to take that risk.
If the query worked, meaning it affected one row, then the new thread ID is retrieved.
7. Add the record to the posts table:
if ($tid) {
$q = "INSERT INTO posts (thread_id, user_id, message, posted_on) VALUES ($tid, {$_SESSION['user_id']}, '" . mysqli_real_escape_string($dbc, $body) . "', UTC_TIMESTAMP( ))";
$r = mysqli_query($dbc, $q);
if (mysqli_affected_rows ($dbc) = = 1) {
echo '<p>Your post has
been entered.</p>';
} else {
echo '<p>Your post could not be handled due to a system error.</p>';
}
}
This query should only be run if the thread ID exists. That will be the case if this is a reply to an existing thread or if the new thread was just created in the database (Step 6). If that query failed, then this query won’t be run.
The query populates four columns in the table, using the thread ID, the user ID (from the session), the message body, run through mysqli_real_escape_string( ) for security, and the posted date. For this last value, the UTC_TIMESTAMP( ) column is used so that it’s not tied to any one time zone (see Chapter 6).
Note that for all of the printed messages in this page, I’ve just used hard-coded English. To finish rounding out the examples, each of these messages should be stored in the words table and printed here instead.
} else { // Include the form:
include ('includes/post_form.php'),
}
} else { // Display the form:
include ('includes/post_form.php'),
}
include ('includes/footer.html'),
?>
The first else clause applies if the form was submitted but not completed. In that case, the form will be included again and can be sticky, as it’ll have access to the $subject and $body variables created by this script. The second else clause applies if this page was accessed directly (by clicking a link in the navigation), thereby creating a GET request (i.e., without a form submission).
9. Save the file as post.php, place it in your Web directory, and test it in your Web browser ( and ).
The result if no subject was provided while attempting to post a new thread.
The reply has been successfully added to the thread.
Review and Pursue
If you have any problems with the review questions or the pursue prompts, turn to the book’s supporting forum (www.LarryUllman.com/forums/).
Note: Most of these questions and some of the prompts rehash information covered in earlier chapters, in order to reinforce some of the most important points.
Review
• What impact does a database’s character set, or a PHP or HTML page’s encoding, have?
• Why does the encoding and character set have to be the same everywhere? What happens if there are differences?
• What is a primary key? What is a foreign key?
• What is the benefit of using UTC for stored dates and times?
• Why is the pass column in the users table set as a CHAR instead of a VARCHAR, when each user’s password could be of a variable length?
• How do you begin a session in PHP? How do you store a value in a session? How do you retrieve a previously stored value?
• How do you create an alias in a SQL command? What are the benefits of using an alias?
Pursue
• Review Chapter 6 if you need a refresher on database design.
• Review Chapter 6 to remind yourself as to what kinds of columns in a table should be indexed.
• Review Chapter 6’s section on time zones if your MySQL installation is not properly converting the dates and times from the UTC time zone to another (i.e., if the returned converted date value is NULL).
• Review Chapter 7, “Advanced SQL and MySQL,” for a refresher on joins and the aggregating functions.
• Modify the header and other files so that each page’s title uses both the default language page title and a subtitle based upon the page being viewed (e.g., the name of the thread currently shown).
• Add pagination—see Chapter 10—to the forum.php script.
• If you want, add the necessary columns to the words table, and the appropriate code to the PHP scripts, so that every navigational, error, and other element is language-specific. Use a Web site such as Yahoo! Babel Fish (http://babelfish.yahoo.com) for the translations.
• Apply the redirect_user( ) function from Chapter 12 to post_form.php here.
• Create a search page for this forum. If you need some help, see the search.php basic example available in the downloadable code.