Making Messageboard v1.1

Adding links to and from the various pages is something I will leave to you - if you have problems with this, I've clearly failed somewhere! We'll start at upgrade #2: adding the messageboard name to the top of mbindex.php. This is as simple as running a query in mbindex.php to grab the messageboard name and print it out, and placing this code after the showpost() function and before the messages query. Here is how it looks, with the last three lines of showpost() included to help you place it:

        echo "</ul>";
    }
}

$mbID = mysqli_real_escape_string($db, $_GET['MBID']);
$result = mysqli_query($db, "SELECT Name FROM mblist WHERE ID = $mbID;");
if (!$result) exit;
if (!mysqli_num_rows($result)) exit;

extract(mysqli_fetch_array($result), EXTR_PREFIX_ALL, 'mb');

echo "<h1>$mb_Name</h1>"; $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)) {

Note that you can remove the second to last line, which checks whether $result is set to false - this is no longer needed as we have already verified that $mbID is good in the earlier query and sanity checked where we grabbed the messageboard name. I have only included it here to avoid confusion!

Upgrade #3 is that we should be able to filter our searching to restrict it to just one messageboard. From a user interface perspective, what we want here is to have a <select> HTML element with all the messageboard listed in there, and a final option, "All messageboards", that searches all the boards and is selected by default. We could do this using a query along these lines:

SELECT blah FROM mbmsgs WHERE MBID = $filter;

However, how would that be able to search through all boards? The query would need to be something like this:

SELECT blah FROM mbmsgs WHERE MBID = 1 OR MBID = 2 OR MBID = 3 OR......;

Messy, huh? Instead, it is easier to use the IN() query function and pass it an array of the possible messageboards, like this:

SELECT blah FROM mbmsgs WHERE MBID IN (1,2,3,4,5,6,...);

We can assemble this comma-separated list of messageboard IDs by storing each individual messageboard ID in an array as we cycle through them to assemble the <select> box, then use the implode() function to create the comma separation. Here's the new search.php script:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    
    if (isset($_POST['Criteria'])) {
        $filter = mysqli_real_escape_string($db, $_POST['Filter']);
        $criteria = mysqli_real_escape_string($db, $_POST['Criteria']);

        $result = mysqli_query($db, "SELECT ID, Title FROM mbmsgs WHERE MBID IN ($filter) AND MATCH(Message) AGAINST ('$criteria' 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" /> in <select name="Filter">

<?php
    $result = mysqli_query($db, "SELECT ID, Name FROM mblist ORDER BY Name;");
    while ($row = mysqli_fetch_assoc($result)) {
        extract($row, EXTR_PREFIX_ALL, 'mb');
        $mblist[] = $mb_ID;
        echo "<option value=\"$mb_ID\">$mb_Name</option>";
    }
    
    $mblist = implode(',', $mblist);
    echo "<option value=\"$mblist\">All messageboards</option>";
?>
</select>
<input type="submit" value="Go" />
</form>

There are three key things to note in that script:

• The MySQL connection code has now been moved to the top, outside of the conditional statement about $Criteria being set - this is because we need to connect to the database no matter what so that we can get the list of messageboards.

• The filter against messageboard ID is placed before the full-text search for maximum speed. The MySQL optimiser may well choose to do this itself, but there is no harm making sure.

• The $mblist array is where we store the ID numbers of all the messageboards. This is passed into implode() to create a comma-separated list of messageboard ID numbers (e.g. 1, 2, 3) that is valid for use with the IN() query function.

Upgrade #4 is to have a "number of times read" counter for each individual message posted, which requires the addition of a field to the mbmsgs table. This field, we will call it ReadCount, should be of type INT and default to 0, so we will need to alter the table like this:

ALTER TABLE mbmsgs ADD ReadCount INT DEFAULT 0;

We need to make three changes in read.php to make the read counter work properly, and these are:

1. Edit the SELECT query to grab ReadCount from the database

2. Add a new query to update ReadCount to one higher than before

3. Display the read counter in the HTML somewhere.

With that in mind, here's the new code for read.php:

<?php
    if (!isset($_GET['Msg'])) exit;
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    $msgID = mysqli_real_escape_string($_GET['Msg']);
    
    $result = mysqli_query($db, "SELECT MBID, Poster, Email, Title, Message, DateSubmitted, ReadCount FROM mbmsgs WHERE ID = $msgID;");
    if (!$result) exit;
    if (!mysqli_num_rows($result)) exit;
    
    mysqli_query($db, "UPDATE mbmsgs SET ReadCount = ReadCount + 1 WHERE ID = $msgID;");
    extract(mysqli_fetch_array($db, $result), EXTR_PREFIX_ALL, 'msg');
    ++$msg_ReadCount;
    $msg_DateSubmitted = date("jS of F Y", $msg_DateSubmitted);
    
    echo "Posted by <a href=\"mailto:$msg_Email\">$msg_Poster</a> on $msg_DateSubmitted. Read $msg_ReadCount times.<br />";
    echo "$msg_Message<br /><br />";
    echo "<a href=\"post.php?Parent={$_GET['Msg']}&MBID=$msg_MBID\">Reply to this post</a><br />";
    echo "<a href=\"edit.php?Msg={$_GET['Msg']}&MBID=$msg_MBID\">Edit this post</a><br />";
    echo "<a href=\"delete.php?Msg={$_GET['Msg']}&MBID=$msg_MBID\">Delete this post</a>";
?>

The only line in there that might cause some confusion is ++$msg_ReadCount. The reason for this is because the default value of ReadCount is 0, which means when we SELECT it from the database we will get 0 returned. Of course, we then update the database to make it 1, but that would not have affected the $msg_ReadCount variable. As a result, we change the variable by hand. Everything else is either the same as before or has been changed as described already. Isn't this easy?

Update #6 (I am leaving #5 till the end, as it is the hardest) is to add a textual description for each messageboard, and to display that along with a thread and post count on index.php. As you can imagine, this requires a change in the mblist table to add a CHAR(255) field for the description as well as some code changes to index.php and create.php.

Alter the mblist table using this SQL:

ALTER TABLE mblist ADD Description CHAR(255);

The next step is to make the changes to index.php that allow thread and post counters, and we will also nominally make it display messageboard descriptions despite there not being any way to add these descriptions yet. Here is the new source code for index.php:

<?php
    $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
    $result = mysqli_query($db, "SELECT ID, Name, Description 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 />";
            extract(mysqli_fetch_array(mysqli_query($db, "SELECT Count(ID) AS NumThreads FROM mbmsgs WHERE MBID = $mb_ID AND Parent = 0;")), EXTR_PREFIX_ALL, "mb");
            extract(mysqli_fetch_array(mysqli_query($db, "SELECT Count(ID) AS NumPosts FROM mbmsgs WHERE MBID = $mb_ID;")), EXTR_PREFIX_ALL, "mb");
            echo "<sup>Threads: $mb_NumThreads, Posts: $mb_NumPosts</sup><br />";
            echo "$mb_Description<br /><br />";
        }
    }
?>
<br /><br /><a href="create.php">Create a messageboard</a><br />
<a href="search.php">Search messageboards</a>

With that code in action, you should create descriptions for your messageboards, like this:

UPDATE mblist SET Description = 'A great messageboard for beginners to ask question!' WHERE ID = 2;

Now, we're on to the final update in messageboard 1.1 - keep in mind these updates are relatively simple, designed to bring the messageboard up to scratch! This update is to give each messageboard their own look and feel by allowing creators to upload a CSS file at the time of creation, and have this file take effect on all parts of our messageboard.

To handle CSS files like this, we need to tag up each element on our pages using the CLASS element - this allows us to specify a CSS file that defines how each element class looks, and thereby define the entire style of the messageboard.

What you choose to add CLASS to is down to you - I am not about to reprint the source code for all the files here. However, here's one file done for you already, mbindex.php:

<?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 Name, Description FROM mblist WHERE ID = $mbID;");
    if (!$result) exit;
    if (!mysqli_num_rows($result)) exit;
    extract(mysqli_fetch_array($result), EXTR_PREFIX_ALL, 'mb');
?>
<html>
<head>
<title><?php echo $mb_Name; ?></title>
<link rel="stylesheet" href="base.css" type="text/css" />
</head>
<body>

<?php
    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 class=\"msgitem\"><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 class=\"msglist\">";
            while ($row = mysqli_fetch_assoc($result)) {
                showpost($row);
            }
            echo "</ul>";
        }
    }
    
    $mbID = mysqli_real_escape_string($_GET['MBID']);

    echo "<h1 class=\"title\">$mb_Name</h1>";
    echo "<p class=\"infotext\">$mb_Description</p>";
    $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 "<p class=\"notice\">This messageboard has no posts.</p>";
    } else {
        echo "<ul class=\"msglist\">";
        while ($row = mysqli_fetch_assoc($result)) {
            showpost($row);
        }
    } ?>     
<br /><br /><a href="post.php?MBID=<?php echo $_GET['MBID']; ?>">Post new message</a>
</body>
</html>

I have added CLASS to quite a few of those elements, which means you should be able to get quite a bit of customisation in there using a CSS file. Here is an example CSS file:

H1 {font: 22px Verdana; color: red}
LI.msgitem {font: 16px Verdana}
P.infotext {font: 12px Verdana; text-decoration: underline}
A {text-decoration: none}
A:hover {text-decoration: underline}

Of course, this only solves part of the problem - we need to allow people to upload their own CSS files. This is where the real work comes in! Ideally we would have an extra field in create.php that accepts a file to upload, which stores the name of that file in the database and loads it in each script file. Of course, we also need the script to handle people not entering a filename at all, and we also need to update other scripts to handle loading the custom stylesheet.

First things first, the database needs to be altered to accept a custom stylesheet. Now, have a think about what problems there may be when accepting custom file uploads. Although there are several potential problems, there are two key problems that need to be tackled immediately:

1. How do you handle file lengths over the size of the field allocated in the table?

2. What happens if two people upload a file called foobar.css?

The easy way to handle the first problem is to use a SHA1 hash to generate a consistent-length character field for our database, however that does not solve the second problem - two files with the same name will generate the same hash value. We can work around this by adding a random value onto the start of each filename before hashing it - this could be the current time of day, or, perhaps better so as not to have to worry about two people using the same filename at the exact same time (unlikely, I know), just to use a completely random value.

So, we need to alter the table to accept a 40-bit hash value - here's the command to use:

ALTER TABLE mblist ADD Style CHAR(40);

The next step is to rewrite create.php so that we have a new form element, handle it being inserted into the table, and also to insert a default value if no file is supplied. Naturally we need to check that the file upload went well before we create the new messageboard, so we end up with something like this:

<?php
    if (!empty($_POST['Name'])) {
        $db = mysqli_connect("localhost", "phpuser", "alm65z", "phpdb");
        $success = true;
        $num = mt_rand();
    
        if (!empty($_FILES['Style']['name'])) {
            $filename = "$num{$_FILES['Style']['name']}";
            $filename = sha1($filename);
            
            if (!move_uploaded_file($_FILES['Style']['tmp_name'], "c:/home/styles/$filename")) {
                $success = false;
            }
        } else {
            $filename = "basic.css";
        }
    
        if ($success) {
            $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, Style) VALUES ('$Name', '$Password', '$filtered', '$filename');");
            
            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 />";
            }
        } else {
            echo "There was an error, please try again.<br /><br />";
        }
    }
?>
<form method="post" enctype="multipart/form-data" 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 />
Stylesheet: <input type="File" name="Style" /><br />
<input type="submit" value="Create" />
</form>

<a href="index.php">Back</a>

About half of that file is new, so I am going to work through the new parts step by step to make sure the operation is quite clear:

• The $success variable is used to store whether the file upload was successful. It is true by default because we have yet to ascertain whether any file was uploaded at all. Also, we get a random number using mt_rand().

• We check whether there is a value in the filename uploaded - if there is, we add our random number to the filename and use sha1() to get our hash. We then attempt to move the file to the "styles" directory for later use. If this attempt fails, we set $success to false (upload failure), or we do nothing if it was OK because $success is already true.

• If there was no file uploaded, we set the filename to be basic.css, a default CSS file.

• If $success is false, there was a problem uploading the file and so prints an error and does not commit to the database.

• At the end of the form there is a HTML FILE element for the user to select their file.

As you can see, it is all fairly predictable stuff. The end result works smoothly, too - having the filename as a hashed value reduces the chance of filename collision down to almost nothing. You'll need to edit each of the files in your messageboard so that they automatically extract the name and stylesheet filename of the current messageboard, leaving the header of affected files something like this:

<title><?php echo $mb_Name; ?></title>
<link rel="stylesheet" href="<?php echo "styles/$mb_Style"; ?>" type="text/css" />

That completes the final update to make Messageboard v1.1 - it is still fairly basic, as messageboards go, but we now have an even firmer foundation on which to build other features. Now, obviously this whole book is not about making a messageboard, however I think you will agree with me that there is a lot of room to learn when writing a messageboard!

 

Copyright ©2015 Paul Hudson. Follow me: @twostraws.