The Next Phase

In my previous post, I described how to get PHP working with LightTPD on a Windows machine. But that’s not the end of the story, since what I really wanted was to get my local WordPress installation working, and the missing piece to the puzzle is to make sure that PHP is enabled for MySQL use.

To do that, simply edit the following in the php.ini file:

• Load MySQL extensions from the correct directory: extension_dir = “c:\php\ext”
• Remove the comment tag for: extension=php_mysql.dl”

A simple post that’s really supposed to be appended to the previous post, but I had some problems trying to get the get the code tag to work in it, so I prefer not to touch it again.

Anyway, my WordPress is in the htdocs directory right now, waiting for me to start some serious templating.

This blog is in need of a makeover, and so I am trying to keep the procrastination habit at bay, practice some GTD and get started on creating a new template for it. First of all, I need a local development environment for PHP on Windows, since I am a Windows user. Previously, I would have installed IIS on Windows and used it as the server for PHP, but it’s always a chore trying to find my Windows CD.

Fortunately, I learnt about LightTPD recently in my work at Wego and how I can use it to replace IIS as the server for PHP. It’s easy installation and setup within minutes. Great! Let’s get started!

1. Download and Install PHP
Download PHP from PHP website. I chose the zip package and simply unzipped it into my directory of choice: Almighty C drive! So now, it’s residing at “C:\php”.

2. Download and Install LightTPD
Download a Win32 version of LightTPD from the WLMP Project; I chose the Setup Wizard (.exe) version.

Double-click on the downloaded executable to start the short installation process, during which you would specify the destination of the install. I installed mine at “C:\Program Files\LightTPD”.

3. Start and Test LightTPD
To start LightTPD, go to the LightTPD directory (i.e. “C:\Program Files\LightTPD”), find the file “TestMode.bat” and double-click on it. A console window would open and indicate that the server has been started.

To test that LightTPD is working at this moment, point your browser to http://localhost. You should see a “LightTPD Test Page”.

That was easy right? Now we just need to edit some configurations to make PHP work with LightTPD.

4. Editing LightTPD Configurations
Edit the file lighttpd-inc.conf (i.e. “C:\Program Files\LightTPD\conf\lighttpd-inc.conf”) with the following:

• Remove the comment tag for “mod-cgi” (Line 20)
• Add this line (assuming that PHP was installed on C drive):
  • cgi.assign = ( “.php” => “C:/php/php-cgi.exe” )

5. Editing PHP Configurations
In your PHP directory, rename the file php.ini-recommended to php.ini and edit php.ini with the following:

• short_open_tag = On
• display_errors = On
• doc_root = “C:\Progra~1\LightTPD\htdocs”

6. Test PHP with LightTPD
To test that LightTPD now works with PHP, create a file with the following contents:

<?php phpinfo(); ?>

Place the file in your htdocs directory (i.e. “C:\Program Files\LightTPD\htdocs”), open up your browser and browse to the page. Walaa! You should be able to see the standard phpinfo() messages.

There you have it! A development environment for PHP on Windows in 6 simple steps. Now, to get started on the new template..

For my current project, a small part of it requires that a web form be built that would request for a date range input from the user. On submit, the form would send a select statement, together with the date range input as a condition, to the underlying database, retrieve a set of results and display it. For this purpose, naturally I am using the BETWEEN .. AND function as discussed previously.

Recall that if you would like to return results for the month of March, you would need to have a condition like:

WHERE created_at BETWEEN "2008-03-01" AND "2008-04-01"

in which created_at is a datetime value.

However, the date range “2008-03-01″ to “2008-04-01″ is not a practical input to the web form. Intuitively, if this date range were to be entered as a form input, the user would have intended that the form returned all results between March 1st to April 1st inclusive. Instead, to return only the results between March 1st and March 31st, a more intuitive input would have been “2008-03-01″ to “2008-03-31″.

To overcome this, we have two solutions:

First Solution
Programatically, increase the latter date by a day before constructing your WHERE clause in your select query:

Modify "2008-03-01" to "2008-03-31" to become "2008-03-01" to "2008-04-01"

However, this is not an elegant way to solve the issue as extraneous code is required to massage the input in order to yield the correct set of results.

Second (and Better) Solution
Use the DATE() function to extract the date part of the datetime value, created_at, in the WHERE conditional clause:

WHERE DATE(created_at) BETWEEN "2008-03-01" AND "2008-03-31"

In this case, you are effectively only comparing the date part of the created_at value to the date range, and timestamp is completely omitted from the comparison.

Simple, intuitive, and yields the expected set of results.

For those who are on Windows, but yet wish to install Ruby Thin Server on your PC, here’s a short how-to.

Ruby Thin Server requires the following dependencies:

1. Rack
2. EventMachine

Unfortunately, the latest version of EventMachine (0.10.0 at the time of this post) is not a Win32 binary release, so it’s a hassle trying to get it installed on Windows. Instead, your best choice would be to download an earlier version of EventMachine 0.8.1 from here, which is a Win32 binary release.

Even though it’s an earlier version, I have not encountered any problems with it. Of course, I am using Windows only for development purposes, and not for production purposes.

Install each dependency by following the steps below before you install Thin.

1. Install Rack.

c:\> gem install rack

2. Download EventMachine 0.8.1 from here. Install EventMachine locally.

c:\> gem install eventmachine -l

3. Install Ruby Thin Server, but ignore dependencies.

c:\> gem install thin --ignore-dependencies

This should install Ruby Thin Server successfully on your Windows machine.

Update from Google Groups:
Thin has been updated to install EventMachine 0.8.1 if you are on Windows. But you can always rely on the steps above if something fails.

Lately, I have been very much involved in the developmental aspects of MySQL, and searching by date or timestamp on a very large set of data is by far the most common task that I am required to do. Due to this, I was able to refresh my knowledge on MySQL and able to pick up some tips and tricks on working with date or timestamp in MySQL.

One of the helpful functions I found, was BETWEEN..AND… Previously, if I was extracting data between two dates, e.g. all records that occurred in the month of January, I would code my query like this:

SELECT * FROM table_name
WHERE created_at >= "2008-01-01" AND created_at <= "2008-02-01;

However, the “greater than” and “lesser than” comparison can actually be replaced by a single function, BETWEEN..AND..:

SELECT * FROM table_name
WHERE created_at BETWEEN "2008-01-01" AND "2008-02-01;

Do note that the first date to be specified in the query MUST be earlier than the second date, else the query would simply return an empty set. Hope you won’t repeat the same mistake as I did.. I had to spend like 15 minutes looking at the query before I discovered the gruesome error.

In addition, if you look at the documentation, it states that:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

Why then did I use “between ‘2008-01-01′ and ‘2008-02-01′” to extract all information that occurred within the month of January, and not “between ‘2008-01-01′ and ‘2008-01-31′”?

This is because, if the timestamp is not stated (i.e. only a date is used), MySQL automatically assumes a timestamp of 00:00:00 for the date. Hence, if I had used “between ‘2008-01-01′ and ‘2008-01-31′”, MySQL would have changed that to “between ‘2008-01-01 00:00:00′ and ‘2008-01-31 00:00:00′”, which clearly indicates that any record with a date of 2008-01-31 and timestamp greater than 00:00:00 would be excluded from the query. This is different from what we want, which is all the records in the month of January, 31st January inclusive.

Try this to verify that MySQL sets a timestamp of 00:00:00 for a date:

mysql> SELECT CURRENT_DATE();
 
+----------------+
| CURRENT_DATE() |
+----------------+
| 2008-03-17     |
+----------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT TIMESTAMP(CURRENT_DATE());
 
+---------------------------+
| TIMESTAMP(CURRENT_DATE()) |
+---------------------------+
| 2008-03-17 00:00:00       |
+---------------------------+
1 row IN SET (0.00 sec)

Hopefully, this post has helped you to better understand how to use BETWEEN..AND.. to query your databases. In my subsequent posts, I will look at other useful MySQL functions.

Are you having problems upgrading your WordPress to version 2.3.3?

After following through the extended steps of upgrading WordPress, I encountered the following error on trying to access /wp-admin/upgrade.php:

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, webmaster@winstonyw.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Searching Google and the WordPress forums gave me an answer. Simply add the following two lines to the .htaccess file which should be residing in the blog’s root directory:

php_value memory_limit 32M
php_flag zend.ze1_compatibility_mode Off


Then, I was able to execute /wp-admin/upgrade.php successfully.However, for some reason, WordPress would overwrite .htaccess during the upgrade procedure, and so the two lines would be missing again. I had to add the two lines back to the .htaccess file before my blog is able to work.

Now I am back in business, after forsaking this blog for more than a year!

Editing the Sizes of the Split-Windows

Suppose you wish to edit the sizes of the split-windows for Gmail and Reader Integrator, this is possible and easy to accomplish. Follow these steps to customize the Gmail or Reader window sizes to your preference:

1. In Firefox, open Tools > Greasemonkey > Manage..
2. Click on “Gmail + Reader Integrator” in the left panel
3. Click “Edit” button
4. Choose a text editor, preferably with “Lines Number” display
5. Go to Line 54 and 55
6. Edit the sizes (in px)

Quick Tip 1:
In a 1280 x 1024 resolution, the height of an email row in Inbox is approximately 22px. Therefore, the default for “GMAIL_SPLIT_HEIGHT” is set as 220px which displays about 10 email rows in split-window view.

Editing the Size of the Reader Window (Gmail Collapsed )

By default, on collapsing the Gmail window while the Reader is open, the Reader would be resized to fill the height of a screen. Suppose you wish to further extend the height of the Reader when the Gmail window is collapsed, do the following:

1. Search for function resizeReaderFrame, or Line 665
2. Change “window.innerHeight - readerEmbed.offsetTop” to a large value

Quick Tip 2:
You can open|close the Reader window by clicking on the Reader link in the LHS navi. That link is like a “toggle” for the visibility of the Reader window.

Feel free to drop me a message regarding other preferred customizations?

Lastly, the response towards the script has been overwhelming. Thank you!

Update on 11-11-2006:
Previously, if your default start-page in “Settings” was “Home”, it was redirected to “All”. This has now been modified such that no redirection occurs, and “Home” is displayed.

Thanks to David for pointing this out. Download the updated script.

Update on 09-11-2006:
There is a programming bug, specifically a variable name error on Line 304 in the script. The variable name for the Google Reader shared items URL should be READER_BROADCAST_URL and NOT READER_SHARED_URL. Sorry for the mistake! Download the updated script, again.

Update on 06-11-2006:
I removed the Reader view when “Contacts” link is clicked as it “damages” the interface. Download the updated script. No other changes to the rest of the script logic.

The short story..I wrote a Greasemonkey script that integrates Gmail with Google Reader.

Features

• Spilt-window view of Gmail and Reader on a single page
• Links to collapse|expand either Gmail or Reader
• Integrated Reader uses start-page as specified in Reader’s “Settings”
• Labels selector
• Key ‘v’ to open Reader links in a new Window
• Automatic resize of Gmail and Reader views

Requirements

• Firefox
• Greasemonkey Extension (Official Site)

Installation Instructions

1. Install Greasemonkey Extension
2. Click on THE SCRIPT (gmailreaderintegrator.user.js)
3. Click “Install”
4. Open Gmail and ENJOY!

Screenshots

The not-so-short story..

Recently when Google reintroduced Google Reader with a new look, new features and improved usability, they marketed it as the “Inbox for the Web”. Immediately, the word “inbox” associates the Reader to emails, and Chris Wetherell - Google Reader Engineer, explained that using the Reader to obtain the latest updates of your favourite websites is similar to reading your emails. You just wait for the updates to be sent to your inbox.

Within the same day, the Reader created a huge buzz and I gave it a try. Previously, I wasn’t accustomed to the not-so-intuitive interface of the historical version and so chose to use Rojo as my RSS aggregator instead. But after exploring the new Reader, I was excited about the changes which Google introduced and have started using it as my default RSS reader.

Then, I found this Greasemonkey script by Mihai Parparita, from a post in Lifehacker, which embeds the Reader into Gmail. This makes perfect sense! They both are inboxes and ideally, they should belong on the same page. Moreover, I use Gmail frequently at work and the interface integration is an amazing productivity hack.

However, I felt that it wasn’t adequate for my use, and that my wish-functionalities were similar to comments left on Mihai’s Blog and Lifehacker. Therefore, I decided to write my own script to integrate Gmail and Google Reader; even though I have never written a Greasmonkey script before.

By examining Mihai’s code, I was able to learn and understand how a Greasemonkey script is written with Javascript. Then, I took about 1.5 weeks to complete coding my version of the Gmail and Reader Integrator, with a list of features that brought more functionalities to the integration. Importantly, I should bring to attention that I reused certain functions written by Mihai for convenience, and I acknowledged these functions clearly in my script. Thank you Mihai for the inspiration and wonderful code.

Lastly, please do report any bugs encountered or feedback any suggestions for possible improvements. I hope that you would find my Gmail and Reader Integrator script useful, as much as I do.

This is ironic and ridiculous: I created this blog about 4 months ago, putting in 2 weeks worth of effort into making it pretty and functional with some of the no-longer-so-popular wordpress plugins. Then, I made about 4 posts and went.. **POOF**

By now, you know the usual excuses I come up with, so let’s not go into that anymore. Instead, I shall provide the latest update and insight to the current state of my beautiful and illustrious existence.

I am happily married with 2 kids. Period.

Difficult to believe? Obviously, IT’S NOT TRUE! Did that catch your attention?

But seriously, the real things are boooooring.

Basically, I spend each day working very hard, learning as much as possible, so that I can function properly as a Database Administrator. In case you are wondering, that’s my specialisation as an IT Specialist. In simple layman terms which I have always failed to comprehend until I started doing my work, a Database Administrator (DBA) provides administration and MAINTAINENCE to databaseS. “Maintainence” is a humongous word. Trust me.

This week, I am enrolled in an Oracle course in Suntec, and I am exempted of work obligations since I am not required to report to work. However, I believe there would be work that is awaiting my return. My return as a worker with perceived greater knowledge and value. Expectations, expectations.

As for my existence outside of work, sometimes it’s peaceful, sometimes it’s wacky. Soon, I hope, it’s going to get exciting. Ssshhh..

My commencement was held on 12th July 2006, 10am to 12pm at UCC.I had a short photo-session due to several reasons:

1. I dislike taking photos.
2. I realised I look hideous in the photos because of my weariness.
3. I have only a few friends, and it gets boring after a while.
4. I had to go back to my workplace to make a sales call presentation.

Anyway, I’ll attach 2 photos here and maybe I’ll post the rest up in Flickr..

Edit: Finally… I can display Thumbnails of the photos..

Finally.. I AM A GRADUATE!! WOOO!!