To build this system we're first going to create a basic system that allows reading and posting, then add to that from there - this allows us to fix bugs and generally get things right step by step rather than trying to accomplish everything at once.
So, let's start with the database schemata for our tables:
CREATE TABLE mblist (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(255), Password CHAR(40), Filtered TINYINT);
CREATE TABLE mbmsgs (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, MBID INT, Parent INT, Poster CHAR(255), Email CHAR(255), Title CHAR(255), Message TEXT, DateSubmitted INT, Password CHAR(255));
This is exactly the same as the plan from our analysis transported directly into SQL, so there should not be any issues there.
The first step to our system is to create create.php and index.php, the scripts to create messageboards and display the list of available messageboards. To create a messageboard we just need to know the name the messageboard will take (e.g. "Bob's messageboard" or "Lord of the Rings messageboard") and the moderator password to use for that board - this will be needed to edit and delete other people's posts. Naturally you do not need to have the ability for users to create their own messageboards if you do not think it fits in with your site. Listing the current messageboards and providing links to mbindex.php is as simple as looping through the return value of a SELECT statement, so we will start with that - here's index.php:
<?php
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$result = mysqli_query($db, "SELECT ID, Name FROM mblist ORDER BY Name;");
if (!mysqli_num_rows($result)) {
echo "There are no messageboards created yet - why not create one?";
} else {
while ($row = mysqli_fetch_assoc($result)) {
extract($row, EXTR_PREFIX_ALL, "mb");
echo "<a href=\"mbindex.php?MBID=$mb_ID\"> $mb_Name</a><br />";
}
}
?>
<br /><br /><a href="create.php">Create a messageboard</a>
That is all stock data listing code we've covered several times to date, so you should be familiar with it already. Note that we create a link to mbindex.php for each messageboard, passing in the $MBID variable set to that messageboard's ID number.
The create.php script is only marginally more complicated - here it is:
<?php
if (!empty($_POST['Name'])) {
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$Name = mysqli_real_escape_string($db, $_POST['Name']);
$Password = mysqli_real_escape_string($db, $_POST['Password']);
$Filtered = mysqli_real_escape_string($db, $_POST['Filtered']);
$result = mysqli_query($db, "INSERT INTO mblist (Name, Password, Filtered) VALUES ('$Name', '$Password', '$Filtered');");
if ($result) {
echo "Your messageboard has been created successfully.<br /><br />";
echo "<a href=\"index.php\">Back to MB index</a>";
exit;
} else {
echo "There was an error, please try again.<br /><br />";
}
}
?>
<form method="post" action="create.php">
Name: <input type="text" name="Name" /><br />
Password: <input type="password" name="Password" /><br />
Filtered: <select name="Filtered"><option value="1"> Yes</option> <option value="0">No</option></select><br />
<input type="submit" value="Create" />
</form>
<a href="index.php">Back</a>
That is also fairly standard. Yes, the swear filtering mechanism works fine to this point, allowing users to choose whether they want words filtered out or not. It would be better to have some better error handling in this script, such as not requiring users to fill in the form again if there is a problem - it is not hard to have the previous values filled out for them.
Create yourself a messageboard using create.php and it should appear in the list in index.php immediately. However, clicking on that messageboard takes you to a broken link - that is what we're going to create next.
The first iteration of our mbindex.php script is largely the same as index.php - we read from a table and output all matching rows. Later on we will be improving this a great deal, but right now we're focusing on pure functionality. Here is the first draft:
<?php
if (!isset($_GET['MBID'])) exit;
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$mbID = mysqli_real_escape_string($db, $_GET['MBID']);
$result = mysqli_query($db, "SELECT ID, Title, Poster, Email, DateSubmitted FROM mbmsgs WHERE MBID = $mbID AND Parent = 0 ORDER BY DateSubmitted DESC;");
if (!$result) exit;
if (!mysqli_num_rows($result)) {
echo "This messageboard has no posts.";
} else {
echo "<ul>";
while ($row = mysqli_fetch_assoc($result)) {
extract($row, EXTR_PREFIX_ALL, "msg");
$msg_DateSubmitted = date("jS of F Y", $msg_DateSubmitted);
echo "<li><a href=\"read.php?Msg=$msg_ID\">$msg_Title</a> posted by <a href=\"mailto:$msg_Email\">$msg_Poster</a> on $msg_DateSubmitted";
}
}
?>
<br /><br /><a href="post.php?MBID=<?php echo $_GET['MBID']; ?>">Post new message</a>
Note the link to mbread.php - we will get onto that soon. The SQL query is the only thing here that is somewhat out of the ordinary, because it filters by MBID so that it only shows messages for this board, but also because it only shows posts with a Parent value of 0 - top-level messages. This is because we need a separate method to handle replies so that they are properly placed under their parents, and then of course there is the problem of replies to replies, and replies to replies to replies, etc - more on that later.
At the very bottom of the script is a link to post.php, which is the next phase in the evolution of our messageboards - posting our first messages. The code for post.php is more complicated than what we've seen so far, so I am going to show you all the code at once, then explain the complex parts. Here we go:
<?php
if (!isset($_REQUEST['MBID'])) exit;
if (!isset($_REQUEST['Parent'])) {
$Parent = 0;
} else {
$Parent = $_REQUEST['Parent'];
}
if (isset($_POST['Title'])) {
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$Name = mysqli_real_escape_string($db, $_POST['Name']);
$Email = mysqli_real_escape_string($db, $_POST['Email']);
$Title = mysqli_real_escape_string($db, $_POST['Title']);
$Message = mysqli_real_escape_string($db, $_POST['Message']);
$CurrentTime = time();
$Password = sha1($_POST['Password']);
$Name = str_ireplace("dog", "***", $Name);
// other filtering here...
$result = mysqli_query($db, "INSERT INTO mbmsgs (MBID, Parent, Poster, Email, Title, Message, DateSubmitted, Password) VALUES ({$_REQUEST['MBID']}, $Parent, '$Name', '$Email', '$Title', '$Message', $CurrentTime, '$Password');");
if ($result) {
echo "Your message has been posted - thanks!<br /><br />";
echo "<A HREF=\"mbindex.php?MBID={$_REQUEST['MBID']}\">Back to messageboard</a>";
exit;
} else {
echo "There was a problem with your post - please try again.<br /><br />";
}
}
?>
<form method="post" action="post.php">
Name: <input type="text" name="Name" /><br />
Email: <input type="text" name="Email" /><br />
Message title: <input type"text" name="Title" /><br /><br />
Message:<BR />
<textarea name="Message" rows="10" cols="40"></textarea><br /><br />
Password: <input type="password" namne="Password" /><br /><br />
<input type="hidden" name="MBID" value="<?php echo $_REQUEST['MBID']; ?>" />
<input type="hidden" name="Parent" value="<?php echo $Parent; ?>" />
<input type="submit" value="Post" />
</form>
The first line is sanity checking - if no MBID (messageboard ID number) has been passed in using either GET or POST, then bail out because there is clearly a problem. Secondly, remember that Parent is being used to determine whether the message is a reply, in which case Parent will be set to the ID number of the parent post, or a top-level message, in which case Parent will be set to 0. On line two, we set Parent to 0 by default if it is not set already.
If you are wondering why $_REQUEST is used for some things and not others, note the two hidden form fields at the bottom. Both MBID and Parent will be passed in by GET to begin with, then POSTed back into the same form - rather than checking whether our value is in GET or POST, it is easier just to use the merged variable, REQUEST.
Everything else in there should be self-explanatory, except note that I have used "// other filtering here" to denote where you should continue to put more text filtering in.
Moving on, we need to create read.php, the script to display messages. As you can see from mbindex.php above, read.php will need to accept the message ID to show, and it will also need to have a reply link to go back to post.php. With this in mind, here's how the first draft looks:
<?php
if (!isset($_GET['Msg'])) exit;
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$msgID = mysqli_real_escape_string($db, $_GET['Msg']);
$result = mysqli_query($db, "SELECT MBID, Poster, Email, Title, Message, DateSubmitted FROM mbmsgs WHERE ID = $msgID;");
if (!$result) exit;
if (!mysqli_num_rows($result)) exit;
extract(mysqli_fetch_array($result), EXTR_PREFIX_ALL, 'msg');
$msg_DateSubmitted = date("jS of F Y", $msg_DateSubmitted);
echo "Posted by <a href=\"mailto:$msg_Email\"> $msg_Poster</a> on $msg_DateSubmitted<br />";
echo "$msg_Message<br /><br />";
echo "<a href=\"post.php?Parent={$_GET['Msg']}&MBID=$msg_MBID\"> Reply to this post</a>";
?>
Most of the above is obvious, although note the three sanity checks in there - if $Msg is not set we exit, if the query trying to extract data does not work we exit, and if no rows are returned we also exit. These conditions should only really occur if someone has tried to hack the system - you should really add some proper error handling here, such as outputting a friendly error message. Note that to post a reply we simply link back to post.php, sending the current message ID as the Parent number.
Now, try posting a reply to a message - see what happens. If you have followed the steps well so far you should get a "thanks for posting" message when you send it in, making you think that everything is OK. However, back on mbindex.php the reply is nowhere in sight. The reason for this is because back in the original draft of mbindex.php, we instructed it to show only posts that have a Parent value of 0 - top-level posts only, no replies. This is because we want to show replies so that they are stacked logically under their parents, which presents a problem - how do we indent replies when each reply can have more replies, and each of those replies can have even more replies?
This is definitely a case for recursive functions, so before we proceed we need to redo parts of mbindex.php to handle this recursive solution. Here is the new code:
<?php
if (!isset($_GET['MBID'])) exit;
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
function showpost(&$parent) {
GLOBAL $db;
$mbID = mysqli_real_escape_string($db, $_GET['MBID']);
extract($parent, EXTR_PREFIX_ALL, "msg");
$msg_DateSubmitted = date("jS of F Y", $msg_DateSubmitted);
echo "<li><A HREF=\"read.php?Msg=$msg_ID\">$msg_Title</a> posted by <a href=\"mailto:$msg_Email\">$msg_Poster</a> on $msg_DateSubmitted";
$result = mysqli_query($db, "SELECT ID, Title, Poster, Email, DateSubmitted FROM mbmsgs WHERE MBID = $mbID AND Parent = $msg_ID ORDER BY DateSubmitted DESC;");
if (mysqli_num_rows($result)) {
echo "<ul>";
while ($row = mysqli_fetch_assoc($result)) {
showpost($row);
}
echo "</ul>";
}
}
$mbID = mysqli_real_escape_string($db, $_GET['MBID']);
$result = mysqli_query($db, "SELECT ID, Title, Poster, Email, DateSubmitted FROM mbmsgs WHERE MBID = $mbID AND Parent = 0 ORDER BY DateSubmitted DESC;");
if (!$result) exit;
if (!mysqli_num_rows($result)) {
echo "This messageboard has no posts.";
} else {
echo "<ul>";
while ($row = mysqli_fetch_assoc($result)) {
showpost($row);
}
echo "</ul>";
}
?>
<br /><br /><a href="post.php?MBID=<?php echo $_GET['MBID']; ?>">Post new message</a>
Yes, that is substantially longer than the original draft, but give it a try - you will see replies (and replies to replies, and replies to replies to replies, etc) are all handled smoothly. The new script works by using the showpost() function defined near the top to handle showing each post and also checking for and displaying its replies.
The showpost() function first prints out our post as before, but then runs its own SQL query to see if there are any posts in the database that have their Parent value set to the current message ID value - messages that are replies to the current post. If there are, the function outputs the HTML element UL, which means unordered list, and calls itself for each of the replies. Unordered HTML lists are automatically indented a small way, so this will automatically thread the posts neatly for us. Note that LI stands for list item - an item inside an unordered list.
Using this new script, each message gets passed into the showpost() function, all its replies are passed through it, etc, etc, thereby forming a nicely threaded display. This is good enough for now - there are just three features left to implement before our first version is complete, which are editing, deleting, and searching.
Editing is, for the large part, exactly the same as posting, so it should be fairly easy. A couple of things will need to be added or taken away - we no longer want people to be able to change their email address or name, for example, but we do need them be able to able to insert their password for verification that it is their message. We also need the form to pre-fill itself with the current title and message, and we need to do the actual password checking itself. Here is how that looks:
<?php
if (!isset($_REQUEST['Msg'])) exit;
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$result = mysqli_query($db, "SELECT Title, Message, Password FROM mbmsgs WHERE ID = {$_REQUEST['Msg']};");
if (!$result) exit;
if (!mysqli_num_rows($result)) exit;
extract(mysqli_fetch_array($result), EXTR_PREFIX_ALL, 'msg');
if (isset($_POST['Title'])) {
if (sha1($_POST['Password']) != $msg_Password) {
echo "Incorrect password!";
exit;
}
$Title = mysqli_real_escape_string($db, $_POST['Title']);
$Message = mysqli_real_escape_string($db, $_POST['Message']);
$Title = str_ireplace("dog", "***", $Title);
$messageID = mysqli_real_escape_string($db, $_POST['Msg']);
// other filtering here...
$result = mysqli_query($db, "UPDATE mbmsgs SET Title = '$Title', Message = '$Message' WHERE ID = $messageID;");
if ($result) {
echo "Your message has been updated - thanks!<br /><br />";
echo "<a href=\"mbindex.php?MBID={$_REQUEST['MBID']}\">Back to messageboard</a>";
exit;
} else {
echo "There was a problem with your post - please try again.<br /><br />";
}
}
?>
<form method="post" action="edit.php">
Message title: <input type="text" name="Title" value="<?php echo $msg_Title; ?>" /><br /><br />
Message:<br />
<textarea name="Message" rows="10" cols="40"><?php echo $msg_Message; ?></textarea><br /><br />
Password: <input type="text" name="Password" /><br /><br />
<input type="hidden" name="MBID" value="<?php echo $_REQUEST['MBID']; ?>" />
<input type="hidden" name="Msg" value="<?php echo $_REQUEST['Msg']; ?>" />
<input type="submit" value="Edit" />
</form>
Note the modified sanity check at the beginning, as well as the removal of all unnecessary fields from the form. Checking the password is as simple as running the supplied password through the SHA1 algorithm using the sha1() function, and comparing the result to the value entered into the database when the post was created. To make this work, you need to edit read.php and add this line near the bottom:
echo "<a href=\"edit.php?Msg={$_GET['Msg']}&MBID=$msg_MBID\">Edit this post</a>";
That will allow people to get to edit.php while supplying the parameters it needs.
We're down to the last two problems now: deleting messages and searching. Deleting messages is quite an easy task to accomplish, and, luckily, so is searching, so we're going to dive right in and get it all done in one fell swoop.
First, edit read.php again and add the following new line near the bottom:
echo "<a href=\"delete.php?Msg={$_GET['Msg']}&MBID=$msg_MBID\">Delete this post</a>";
Deleting messages can be done in three ways - we either delete the post and leave its children intact (they will not be shown because the parent is gone), or we delete the post and recursively delete all the children, or we create a new field "Status", which is set to 1 (visible) by default and, when we're deleting, we set it to 2 (deleted).
Of these, option 1 is the easiest but will likely cause problems - particularly when you are searching for messages. Option 3 is the best, as it means you keep a full log of the messages on your messageboard no matter what. Having said that, our current database does not have a Status field in there, so we will have to do without this for now, which leaves us with option 2 - recursively delete messages. Luckily, this is much the same as recursively displaying messages as we did in mbindex.php, so it turns out to be a cinch.
Here is the code for delete.php:
<?php
if (!isset($_REQUEST['Msg'])) exit;
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
function delete_message(&$msg) {
GLOBAL $db;
extract($msg, EXTR_PREFIX_ALL, 'row');
$result = mysqli_query($db, "SELECT ID FROM mbmsgs WHERE Parent = $row_ID;");
while ($row = mysqli_fetch_array($result)) {
delete_message($row);
}
mysqli_query($db, "DELETE FROM mbmsgs WHERE ID = $row_ID;");
}
$result = mysqli_query($db, "SELECT Title, Password FROM mbmsgs WHERE ID = {$_REQUEST['Msg']};");
if (!$result) exit;
if (!mysqli_num_rows($result)) exit;
extract(mysqli_fetch_array($result), EXTR_PREFIX_ALL, 'msg');
if (isset($_POST['Password'])) {
if (sha1($_POST['Password']) != $msg_Password) {
echo "Incorrect password!";
exit;
}
$parentID = mysqli_real_escape_string($db, $_POST['Msg']);
$result = mysqli_query($db, "SELECT ID FROM mbmsgs WHERE Parent = $parentID;");
while ($row = mysqli_fetch_array($result)) {
delete_message($row);
}
$msgID = mysqli_real_escape_string($db, $_POST['Msg']);
mysqli_query($db, "DELETE FROM mbmsgs WHERE ID = $msgID;");
echo "Your message has been deleted.<br /><br />";
echo "<a href=\"mbindex.php?MBID={$_REQUEST['MBID']}\">Back to messageboard</a>";
exit;
}
?>
To delete the message "<?php echo $msg_Title; ?>", enter the appropriate password below.<br /><br />
<form method="post" action="delete.php">
Password: <input type="text" name="Password" /><br /><br />
<input type="hidden" name="MBID" value="<?php echo $_REQUEST['MBID']; ?>" />
<input type="hidden" name="Msg" value="<?php echo $_REQUEST['Msg']; ?>" />
<input type="submit" value="Confirm" />
</form>
There are no surprises there, and this should all be second nature to you by now.
The final step in creating the first phase of our messageboard - and you have got to admit it is certainly not easy to put all the original scripts together! - is to create the search functionality.
Back in the Databases chapter we looked at how full-text searches can be accomplished using MySQL's built-in functionality, and hopefully you remember that it was remarkably easy to do. Well, in order to maximise the search capabilities of our messageboard, we're going to use a full-text index on the messages on our boards. The first step to accomplish this is to add the index, like this:
ALTER TABLE mbmsgs ADD FULLTEXT(Message);
Once the index is in place, we can run our search using a query like this:
SELECT Title, Message FROM mbmsgs WHERE MATCH(Title) AGAINST ('$search_criteria' IN BOOLEAN MODE);
Naturally the $search_criteria variable will need to come from the user of our messageboard, so we need to get back to PHP to make the search.php page. First, add a link from index.php to search.php - this it the easy part. Thanks to MySQL's easy full-text search functionality, search.php is not all that much harder. Here is how it looks:
<?php
if (isset($_POST['Criteria'])) {
$db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
$searchCriteria = mysqli_real_escape_string($db, $_POST['Criteria']);
$result = mysqli_query($db, "SELECT ID, Title FROM mbmsgs WHERE MATCH(Message) AGAINST ('$searchCriteria' IN BOOLEAN MODE);");
if (!mysqli_num_rows($result)) {
echo "<em>Your search matched no messages!</em><br /><br />";
} else {
while ($row = mysqli_fetch_assoc($result)) {
extract($row, EXTR_PREFIX_ALL, 'msg');
echo "<a href=\"read.php?Msg=$msg_ID\">$msg_Title</a><br />";
}
}
}
?>
<form method="post" action="search.php">
Search for: <input type="text" name="Criteria" /> <input type="submit" value="Go" />
</form>
There - exactly as planned, and, when you think about it, it is a nice easy script to finish the first version of the messageboard off. It is taken us quite a long time to get this far, but that is because starting from scratch is not all that easy. Now that we have a base to work from now, we can make the messageboard that little bit more powerful and a lot more challenging.
I strongly recommend you take a break from reading for a while - sleep helps you store memories properly, so take a nap to let things sink in.
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Analysis: Messageboard v1.1 >>
Previous chapter: Analysis
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.