Louis Kessler’s Behold Blog

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.

The Behold User Forum is now open again. I’ve not implemented everything into it yet, but it is quite functional and should now allow public conversations about Behold again.

It feels great to finally get it up. Now instead of me developing it in the dark, it can be used while I put the final sets of features into it.

I’m very happy with its look and the integration I’ve done between this Blog and the Forum. Over the next couple of days, I’ll blog here about some of its features and what’s still left to do with it.

In the meantime, feel free now to post to the Forum. (Use the Forum tab at the top of this page to get there)