Louis Kessler’s Behold Blog

I have now finished off everything I wanted to do with the Behold Blog and the Behold User Forum. They are integrated nicely and now have all the features I want. With their use of WordPress and bbPress and a solid PHP/mySQL base and large opensource user community supporting them, they should do me quite well into the foreseeable future.

If I am to be criticized of anything, it is that I have overcustomized the Blog and Forum to my liking. It is just a failing of mine. But it is one that tends to result in a good final product, once I do get there.

Back in my earlier What Have I Done to this Blog post, I listed 15 unique features I had put in the Blog and 7 other design considerations.

Of those I’ve removed:

• 4. The custom field where you live. Now I detect the IP address instead and generate the country and flag of where you posted from automatically.
• 10. The weather widget was very “expensive” in terms of slowing things down. But it was a frill to have it on my blog. I added a weather icon to my personal home page at lkessler.com instead.
• 12. The AJAX comment editor. I played around with all sorts of editing tools since then, including the TinyMCE editor to try to get entries done the same way for both Blog comments and Forum posts. I almost had TinyMCE working, including adding nice little smilies, but I found little problems with the results of the edit, with integration with my style, and with the icons they include. My conclusion was to just allow a simple text box entry as is default in both WordPress and bbPress and leave it at that.
• 14. I upgraded both WordPress to 2.5 and bbPress to 0.9 and that eliminated most of the hacks I had to add to get them to integrate. That was nice.

In addition to these, I’ve got a whole new host of interesting features that I’m very pleased with:

1. Complete integration of the Blog and Forum. Single registration and sign-in for them both.
2. Custom date/time display, that lists post time as time elapsed if less than a week old, and gives the date otherwise.
3. The search is available from both the Blog and the Forum. It is integrated and searches both the titles and contents of all Blog entries, Blog comments and Forum Posts. You can search for multiple words or include phrases in quotes. No words are ignored since it no longer uses the mySQL word index search.
4. Once on the search results page, it lists excerpts from the found items with the text you searched for highlighted in yellow. You can then subselect the search into only Blog entreis, Blog comments and Forum posts if you want.
5. The Forum main page shows the Four Forums as well as the most recent 30 topics.
6. I’ve added four cute icons to represent the four Forums and I use them in many places.
7. You can click on the headings of the topic list and sort by that item. Click again on the same one to sort in reverse order.
8. Entries by me are highlighted in light blue. If you are logged in, then entries by you are highlighted in light orange. This is done throughout the Blog comments and Forum Topics and Posts.
9. Blog Comments and Forum Posts have been made to look the same. For each, the user name, user id, location and flag, date joined, number of blog comments and forum posts, and date posted are shown. The user name will hyperlink to the user’s website if one was added to their profile. The number of comments and posts links to the search tool which will list them. I debated whether to add Avatars or Gravatars (pictures that represent you) but in the end I decided not to.
10. There is a userlist available (if you are logged in) so you can see all the other registered users, their country, date joined, and number of comments and posts. Each column is sortable and you can click on their number of posts to get the list of them.
11. The RSS feeds for both the Blog and Forum should all work well now. I found many bugs in the code from WordPress and bbPress that I fixed. This was not debugged that well by them.
12. It is worthwhile to register and login. If you are logged in you can post, you get access to the user list, your posts get highlighted in light orange, and you get access to another menu on the Forum page that allows you to list only topics that you started, or only topics that you posted to.

Wow! I only realize how much work that was after I write it out like that.

But hopefully I’ve built the foundation of a Behold community that people will feel at home at, find easy to use and encourage discussion.

I’ve had to figure out 100 different puzzles to get the Blog and Forum going. An example is figuring out how PHP determines the time in different timezones.

It bothered me to see that both WordPress and bbPress display times only in terms of either Greenwich Mean Time or in the local time where the Server is located. In my case, my server in in North American Eastern Time, but I am located in Central Time. All the times displayed appeared to me to be one hour off my local time.

I thought PHP should be able to detect the timezone of the user, but after some research I realized it can’t. The reason is it has no access to the user’s timezone, since the user’s local time is not a browser variable that is sent to the webserver. Javascript can get it, but I really didn’t want to add any Javascript to my pages unless it was absolutely necessary.

What many Forums do is to allow a person to enter their timezone in their profile. That would work, but the way I’ve set things up, there is currently no profile information in my Blog/Forum to set up after you’ve registered. It would require another step, and a simple one at that, but it’s something that just doesn’t feel necessary to me if it can be done automatically.

An automatic example, and something I did include, is to use the visitor’s IP address, which is passed to the server and available to PHP, to look up the country and a little country flag. The database for this is available free from ip2nation and there is a nice plugin for WordPress available to make it easy to do this.

So if there were an ip-to-timezone database to do the same, it would be easy. But unfortunately that beast is only available commercially and it doesn’t come cheap. We’re talking $649 for their least expensive database that includes timezone.

One feasible option would be when a person registers, and/or when they login, to use the JavaScript function to save their timezone with their user information.

The other option is not to use the timezone at all. What bbPress did was quite interesting. Instead of listing the Date and Time as most forums do, they list how old the post is, e.g. 3 months old or 6 days old or 25 seconds old. In some ways that is very nice and will always work no matter what timezone the visitor is in. However, seeing “1 year old” doesn’t differentiate a post that might be anywhere from 1 year to 1 year and 364 years ago. Also relating those long ago periods into a timeframe context (i.e. when was that?) is not easy for most people, myself included.

WordPress and bbPress don’t use the same style. WordPress lists dates. bbPress lists time-ago. That’s not a difference I wanted in my integrated site.

So in the end, I didn’t decide to use Javascript, nor have a user option, nor use the IP address. My compromise is to list the “how long ago” up to 7 days ago, and when longer than that to show the date. This will be common to both my Blog and Forum. (I will ignore the minor problem that the date may be one day off because of the timezone difference, which was always a problem anyway.)

So this is an example of one of the interesting puzzles that have been taking my time. This one was about 8 hours of work over 4 days. But it was fun and I learned a lot about all sorts of web technologies.

———————

Hopefully the web-puzzles are just about over, and I’ll be back to Behold again. I know I’ve been saying this for the last six months, and Behold has been delayed as a result, but bear with me just a bit longer. My old Behold site is 8 years old and needed this rejuvination. The new site (beholdgenealogy.com) that I am finishing will have new technology that should be good hopefully for another 8 years. I’ll soon be able to concentrate on Behold with less interruptions than I would have otherwise. Thanks for your patience.

I’m just finishing up the last few changes to the Forum and Blog and they should be incorporated into the live versions very soon.

Over the past few weeks, my customization (maybe I’m overdoing the customization a bit, but there’s no use in going back now) has led me to write some new Forum pages. In particular, to develop my Search and Userlist pages, I’ve needed to delve into the world of mySQL databases and the SQL (Structured Query Language) that is used to access data from them.

I’ve worked with many different databases in the past, but have never previously had the opportunity with any of them to use more than simple requests, similar to “SELECT * FROM table1 WHERE id = 10″. But that’s all different now, as I’ve had to research and test and try all sorts of various and complex requests to get things to work. Here’s one example:

$userlist = $bbdb->get_results(”SELECT id, user_login, user_nicename, user_url, user_registered, SUBSTRING_INDEX(display_name, ‘/’, 1) as country, SUBSTRING_INDEX(display_name, ‘/’, -1) as user_ip, (SELECT COUNT(post_id) FROM bb_posts WHERE id = poster_id AND post_status = 0) as numposts, (SELECT post_time FROM bb_posts WHERE id = poster_id AND post_status = 0 GROUP BY post_id DESC LIMIT 1) AS post_time, (SELECT COUNT(comment_id) FROM wp_comments WHERE id = user_id AND comment_approved = 1) as numcomments, (SELECT MAX(comment_date_gmt) FROM wp_comments WHERE id = user_id AND comment_approved = 1) as maxcomment FROM wp_users WHERE user_status = 0 GROUP BY id ORDER BY numposts + numcomments DESC”);

Even this one is relatively simple, since it doesn’t even get into the complexity involved in joining databases (and that’s another matter with LEFT joins, RIGHT joins, INNER joins, OUTER joins, CROSS joins, STRAIGHT join - you name it!). And the mySQL documentation is excellent. They even tell you how to best optimize your requests to be as fast as possible.

My conclusion … Well, I am extremely impressed by the SQL language and its abilities. Once you get used to it, it is very nice to work with.

Sun just recently purchased mySQL for a billion dollars. I think they got a good deal.