In the first post about this MOD I introduced the basic concept of a daily digest and described how the scheduled process worked. The other half of the system is the user interface that allows the user to mark which forums they want to subscribe to. I will cover that (and a bit more about the database design) today.

Digestable?

One of the things that I am always concerned about is performance. I don’t always have time to monitor my server. I also can’t tell when I will have large blocks of time available to do server maintenance. As a result, I always try to make whatever MODs I write as efficient as possible. This MOD was no exception.

For example, I looked over the forums on my board and decided that I was not going to waste time generating and sending a digest for the “Off Topic” area of the board. If people wanted to read jokes, then they can visit and read them like everyone else. So that meant that I had to set up a way to track which forums could be subscribed and which could not. The obvious place to store and maintain that information is right on the phpbb_forums table, so here’s what the table structure looks like after installing this MOD:

+----------------------+-----------------------+------+-----+---------+-------+
| Field                | Type                  | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| forum_id             | smallint(5) unsigned  |      | PRI | 0       |       |
| cat_id               | mediumint(8) unsigned |      | MUL | 0       |       |
| forum_name           | varchar(150)          | YES  |     | NULL    |       |
| forum_desc           | text                  | YES  |     | NULL    |       |
| forum_status         | tinyint(4)            |      |     | 0       |       |
| forum_order          | mediumint(8) unsigned |      | MUL | 1       |       |
| forum_posts          | mediumint(8) unsigned |      |     | 0       |       |
| forum_topics         | mediumint(8) unsigned |      |     | 0       |       |
| forum_last_post_id   | mediumint(8) unsigned |      | MUL | 0       |       |
| searchable           | tinyint(1) unsigned   | YES  |     | 1       |       |
| digestable           | tinyint(1) unsigned   | YES  |     | 1       |       |
| hideable             | tinyint(1) unsigned   | YES  |     | 1       |       |
| prune_next           | int(11)               | YES  |     | NULL    |       |
| prune_enable         | tinyint(1)            |      |     | 0       |       |
| auth_view            | tinyint(2)            |      |     | 0       |       |
...
| auth_download        | tinyint(2)            |      |     | 1       |       |
+----------------------+-----------------------+------+-----+---------+-------+

The new fields are searchable, digestable, and hideable. Each of these defaults to yes (a value of 1 is generally positive). When a new forum is set up I can set these flags with a checkbox. If a forum is not on the list of options to subscribe to then the digestable flag is set to zero. Here’s what my actual forum edit screen looks like (with some extra options thrown in as well)

In this particular case the Off Topic forum is allowed to be searched, it can be hidden, (more on both of these shortly), but it cannot be subscribed. That means that the user cannot mark this forum as part of his or her digest.

The “hideable” flag is designed to let me, as the board administrator, decide if a user can hide a forum on the index or not. There are certain forums that have to do with the board itself, meaning they contain administrator announcements or topics about feature requests and so on. I don’t want a user to be able to ignore those, so the “hideable” flag is set to zero.

The “searchable” flag is designed to give more flexibility to the user. They might want to read the Off Topic forum, but they never want to see topics or posts from that forum in search results. By having the “hide” option separate from the “search” option it provides more flexibility.

User Configuration Screen

By default the digest is an “opt-in” setting. Many users get irate with unsolicited email, myself included. If a user never visits the screen shown below, there are no rows stored in the table. Here is a somewhat truncated version of the option screen for the user:

There are three columns of checkboxes. The first is marked “Search”, the second is “Digest”, and the last is “Hide” as shown here. A checkbox is shown / not shown based on the forum settings. In this case, as I mentioned earlier, the Off Topic forum is not digestable, and the About BOB forum is not hideable. Both are searchable at the user’s option. If javascript is enabled then a user can click the checkbox in the header to mark / unmark all of the checkboxes in that column.

Here is the table behind this form:

+---------------+-----------------------+------+-----+---------+-------+
| Field         | Type                  | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| user_id       | mediumint(8)          |      | PRI | 0       |       |
| forum_id      | smallint(5) unsigned  |      | PRI | 0       |       |
| hide_status   | tinyint(1) unsigned   |      |     | 0       |       |
| digest_status | tinyint(1) unsigned   |      |     | 0       |       |
| search_status | tinyint(1) unsigned   |      |     | 0       |       |
+---------------+-----------------------+------+-----+---------+-------+

It’s very simple. The primary key is composed of both the user_id and the forum_id. The other three values are the settings made by the user. If a user never visits the screen, this table does not contain any rows that match their user_id. After all of the work that I put into this MOD, less than 10% of my users have looked at it even once.

Tuning the My Board MOD

The reason all of this exists was my user community wanted a way to get a nightly digest just like they had with our former mailing list. While I was creating the screen to set up the subscription, I came up with the idea to extend it and provide the options I’ve discussed here. I had to spend a lot of time optimizing this. Exclusion conditions like and forum_id NOT IN (1, 2, 3, 4, 5, 65, 78, 92) do not make MySQL very happy. As a result the forum exclusions are handled differently based on what the function is.

For example, on the index I don’t do a NOT IN query. I go get every forum the user is authorized to view. The standard code for the index then takes the list of forums and processes them in a loop. Here’s what the code might look like:

while( $row = $db->sql_fetchrow($result) )
{
	$forum_data[] = $row;
}

And here’s what it looks like after the My Board MOD is installed:

while( $row = $db->sql_fetchrow($result) )
{
	if (!(isset($hidden_forums[$row['forum_id']])))
	{
		$forum_data[] = $row;
	}
}

This does query some extra rows, but it avoids an expensive NOT IN query. The same logic is used in the jumpbox. Since the index is a very popular page, and since the jumpbox is built on nearly every page, having these queries be as efficient as they can be is important.

Unfortunately for search.php there is no easy way to ignore the forums after the search process, so I do use a NOT IN query there.

I have played with different ideas on how to cache the user options but so far I have not implemented any of them. That means that every page a user views results in a query to the table shown above. It’s really fast. I just checked, and the elapsed time for the query for me was 0.0008180 seconds. I don’t think that’s a big drain on server resources.

Conclusion

The My Board MOD has been very well received on my board, and by the clients who have seen it and requested that I install it for them. For clients I generally offer a version with only the searchable and hideable flags since I don’t want to have to support the perl script and all of the other background stuff required for the digest. But even the two options that are provided seem to be appreciated. If anyone can think of another forum-specific user option to include, I am certainly open for suggestions.