db4free.net’s 5th birthday

To my shame I must admit, I missed it. It happened on June 29, 2005 when db4free.net was first available to the public. At that time it was running MySQL 5.0.7 beta. Quite a lot has happened since then, MySQL 5.0 made its way up to 5.0.91 and the current MySQL GA version is 5.1.49, which is also the version db4free.net is running as of today. The first phpMyAdmin version that db4free.net was offering to provide easy access to the user’s databases was 2.6.3. Today I updated phpMyAdmin to 3.3.5.

Statistics are not necessarily 100 % accurate, but here is the best I can come up with. Since its launch, db4free.net had 528,900 visits. The ratio registrations per visits is at about 22 %, so more than every fifth visit ends in signing up for a new database. Which means, that about between 110,000 and 120,000 database accounts (meaning database and user) have ever been created. There have been some cleanups since then to make resources available to people who actively use their databases. Which is why the current number of databases is much lower, at slightly above 13,400.

Today I must (also) admit that the code behind db4free.net’s web application was initially quite poor and it stayed poor for quite long. Why it was poor from my today’s point of view is probably due to my learning process in these 5 years. This year I did a lot of cleanup on the code behind db4free.net’s web application and removed a few bottlenecks which often made the website painfully slow. Now it’s in a fairly reasonable state again, and I have some ideas in the back of my head how to further improve it. So db4free.net is far from its end of life. Quite the opposite is the case, it’s time to give it a new boost so that the balance after its 10th anniversary is even more impressive than today’s 5 year balance.

Smarty 3 and __autoload()

When I first tried out Smarty 3 (which is available as Release Candidate 3 at the moment), which was that I updated an existing Smarty 2 installation, I ran into this error message:

Fatal error: Class ‘Smarty_Internal_Template’ not found in lib/Smarty/Smarty.class.php on line 444

I started to investigate what the problem may be and soon found out that libraries in Smarty/sysplugins/ were not loading. So I picked up my already existing __autoload() function and added the following code:

function __autoload() {
  // other stuff that had been here before

  if (substr($classname, 0, 15) == "Smarty_Internal") {
    $classname = "Smarty/sysplugins/" . strtolower($classname);
    require_once $classname . ".php";

With this addition, Smarty 3 worked perfectly, but I still kept wondering why I had to tell my autoload function explicitly to load the Smarty_Internal libraries. I found it quite ugly that I had to add it myself.

So I asked in the Smarty forum and here is why: Smarty registers its own autoload function, using the spl_autoload_register() function. Having my own __autoload() function breaks this, unless I register my own function with spl_autoload_register() as well. So the solution looks like this:

function autoload() {
  // my old stuff, but no more loading of Smarty libraries


Very simple solution, if you just get the right hint. So maybe if somebody stumbles upon the same error message, this may be of help to resolve this nicely and quickly.

If you are interested in seeing my post in the Smarty forum, it can be found at http://www.smarty.net/forums/viewtopic.php?p=65975.

How to detect the user’s preferred language – smarter than Google

Sometimes I find Google annoying. Don’t get me wrong, I am not one of the many Google haters out there. They have done amazing things and especially since they have released WebM as Open Source and royalty free video format, they have earned a bonus in my attitude towards them. And even though Google Chrome is not my personal favorite, I have to admit that Chrome has brought a great momentum into the development of web browsers (and therefor the WWW as a whole) which users of any browser benefit from. Last but not least, I use a lot of Google’s services and they do their job quite well. So all in all, Google’s record from my point of view is pretty good.

But one thing really sucks. I use an English operating system. I use an English Firefox (locale en-US). All the language preferences in the Google profiles of my accounts are set to English. Nevertheless, I often get pages from Google delivered in German by default. How they do that is quite obvious, or lets say, there is only one explanation how they may do this: they identify my home country based on my IP address. There are services that provide the data and they are easy to implement, sometimes even for free, like MaxMind‘s GeoLite Country database. Also I am using these services and I believe there is nothing wrong in doing so, except for a few things – and one of them is to identify the assumed web page visitor’s language preferences based on his or her home country.

Thankfully, I am only a lightweight victim of this practice, because I speak the language which I get served based on their guesses. But how happy would a Japanese tourist on vacation in Austria be to get his pages delivered in German, only because he uses an Austrian IP address? There are many reasons why a person located in one country may not understand the language that is spoken there. I only have to drive about 20 kilometers to get into such a country (the Czech Republic). So to assume that a person’s preferred language is the language spoken by the majority of a country (what about minorities or countries that have more than one language?) is very wrong.

What are better solutions? As I mentioned, I use an English operating system and an English browser. Almost every browser sends a user agent string. My currently most frequently sent user agent string for example is “Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US; rv: Gecko/20100713 Firefox/3.6.7”. Every web server that receives a request from a user agent (usually a browser) can read this user agent string and process it accordingly.

What can we read in mine, amongst other information? en-US. So the locale tells a web server that it’s a browser (a Firefox 3.6.7) in American English. Now guess what my preferred language will be? Is it likely that I use a browser in a language which I don’t want? Is it likely that a tourist in a different country uses a browser in a language that he or she doesn’t want? It’s certainly possible (like if there is a terminal in the hotel room or an Internet Café), but nowadays most people have their laptops, notebooks, smart phones etc. And their browser language is still their preferred one, regardless where they are currently located. So the browser language serves as a much better criterion to assume a web site visitor’s preferred language.

Alright, no big rocket science so far, and to be honest, there is no big rocket science to come. At one point, when I was annoyed about Google’s failure to identify correctly which language to deliver me, I wondered, how hard would it be to implement the (in my humble opinion) better solution, using the web browser’s locale. The answer is far from being rocket science. The solution can be as easy as this:

First, get yourself a recent copy of the Zend Framework and install it so you make its libraries accessible to your code. You can add an __autoload() function, something like this:

function __autoload($classname) {
    if (substr($classname, 0, 4) == "Zend") {
        $classname = str_replace("_", "/", $classname);
        require_once $classname . '.php';

or even easier, use the Zend Framework AutoLoader:

require_once 'Zend/Loader/Autoloader.php';


Everything we need to identify the browser language and locale can be found inside Zend_Locale.

The constructor of the Zend_Locale class allows to set the desired language or locale. In addition to that, there are three predefined constants named Zend_Locale::BROWSER, Zend_Locale::ENVIRONMENT and Zend_Locale::FRAMEWORK. According to the Zend_Locale introduction manual page they do the following:

Zend_Locale::FRAMEWORK – “When Zend Framework has a standardized way of specifying component defaults (planned, but not yet available), then using this constant during instantiation will give preference to choosing a locale based on these defaults. If no matching locale can be found, then preference is given to ENVIRONMENT and lastly BROWSER.”

Zend_Locale::ENVIRONMENT – “PHP publishes the host server’s locale via the PHP internal function setlocale(). If no matching locale can be found, then preference is given to FRAMEWORK and lastly BROWSER.”

Zend_Locale::BROWSER – “The user’s Web browser provides information with each request, which is published by PHP in the global variable HTTP_ACCEPT_LANGUAGE. if no matching locale can be found, then preference is given to ENVIRONMENT and lastly FRAMEWORK.”

And this is what we want!

Lets get to the code:

$zend_locale = new Zend_Locale(Zend_Locale::BROWSER);

// returns en for English, de for German etc.
$browser_language = $zend_locale->getLanguage();

// returns en-US for American English, en-GB for British English etc.
$browser_locale = $zend_locale->toString();

Three lines of code to get all the information we need. And how to make a choice which language to serve based on that? Lets assume we have a site in English, French and German. French browsers should get French, German browsers should get German and everybody else should get English:

$site_language = "en";
switch ($browser_language) {
    case "de" :
        $site_language = "de";
    case "fr" :
        $site_language = "fr";

And that’s it. That’s all Google would have to do to make me happy. I assume, their IP address based identification code requires more lines, and is still a poorer solution in my opinion. There are additional benefits to this solution. Want to display date and time according to the locale? Here is what to do:

$zend_date = Zend_Date::now();

$date_time = $zend_date->get(Zend_Date::DATETIME_FULL, $zend_locale);

print $date_time;

My default browser now shows me “Friday, July 23, 2010 2:14:58 AM Europe/Vienna”. If I fire up a German browser, the same page delivers me “Freitag, 23. Juli 2010 02:17:11 Europe/Vienna” and there may be answers like “vendredi 23 juillet 2010 02:17:56 Europe/Vienna” (French), “viernes 23 de julio de 2010 02:18:29 Europe/Vienna” (Spanish), “pátek, 23. července 2010 02:18:53 Europe/Vienna” (Czech) or “2010年7月23日金曜日2時19分17秒 Europe/Vienna” (Japanese) as well. It’s pretty cool how the Zend Framework can make such tasks very simple ;).

Where is a solution like mine being used in the wild? I don’t know where as of the time of this writing, but I know where very soon. The MySQL website will soon launch new localized content, delivered to people who we think really want to see these languages. We try very hard not to annoy people by enforcing a language upon them which they would not like to choose.

And remember: to make good guesses is good. The best solution however is to provide choice! If you make assumptions and even if the assumptions are well-founded, always leave people the choice to select what they want. There may be situations which you are not thinking of and because of some unusual circumstances, somebody may still want to choose differently than you would think.

db4free.net blog has moved and what’s new

It was about time to switch to something new.

I finally have my new blog launched at www.mpopp.net which is something that I had planned for quite a while. This new blog is a replacement for my old blog at db4free.blogspot.com where people will find news and posts about db4free.net and MySQL. But since db4free.blogspot.com used to be specific to db4free.net and MySQL (implied by its name), it was not a good place to blog about various different topics as well, which I found increasingly limiting. So I decided for a new blog, run by WordPress, which will allow me to categorize my blog posts to target people who are interested in specific topics.

Some of them will be:

… so far, not much new and most posts which go into one category will go into the other as well.

Further categories that I’m thinking of are:

If you had my old blog bookmarked or had linked to it on your blog or site, please add or update the bookmark or link to my new blog. And if you have not done so far, why not do so now?

Welcome to my new home!