Sad truth of the matter is that I need to be able to access Microsoft SQL from PHP.  The old cross-platform PHP mssql functions are depreciated and will not authenticate with SQL Server 2005 or 2008–now to access MSSQL stuff, you need to use Microsoft’s SQL Server 2005 Driver for PHP .  Which is only available for Windows.

I do all my primary development on a Mac, but now I have a Windows dev server for PHP apps that interface with Microsoft SQL.  This new machine runs Vista, and I’m using all the latest versions–(Apache 2.2 and PHP 5.2.6).

I’ve installed PHP on many Windows systems, and usually it is dead simple:

• Install Apache
• Download PHP, Zip Version
• Unzip php zip file into c:\php
• Copy php.ini-dist to php.ini
• Modify the $PATH Environment Variables to include “c:\php”.  Reboot for changes to take effect
• Append httpd.conf with

  LoadModule php5_module "c:/php/php5apache2.dll"
  AddType application/x-httpd-php .php
  PHPIniDir "C:/php"

• Restart Apache

Only this time I was getting an error when I restarted Apache.  Checking the error.log I noticed this error.

Cannot load C:/php/php5apache2.dll into server: The specified module could not be found.

Strange error.  php5apache2.dll does exist and in that location.  I double and triple-checked.  It is there.

Eventually I discovered that if you’re using Apache 2.2 you need to use the php5apache2_2.dll not php5apache2.dll. This is not in PHP’s install.txt documentation, nor does the Apache error log steer you in the right direction.  It should say something like “unable to load module” not “module not found”.  You’re just supposed to know.  Nice.

So to recap, do everything the same except instead of putting this in your httpd.conf:

LoadModule php5_module "c:/php/php5apache2.dll"

use:

LoadModule php5_module "c:/php/php5apache2_2.dll"

So if you’re getting that error message, there is your solution.

Been working on some new ways of displaying various metrics. I’ve used JpGraph and PHP/SWF Charts in the past, but thought that I might invstigate some newcomers. While Sparkline PHP and Chart API / WebFX for DHTML both were interesting, I wasn’t quite able to get the libraries to do exactly what I was going for.

Google, on the other hand was able to make pretty good mix of design, customizability, flexibility and ease-of-use in their dynamic image-based Chart API.

There is one pretty big difference between Google’s Chart API and most of the other tools on the market: it won’t take raw data and automatically scale the Y-Axis to fit your data and provide labels. It instead works on a relative scale, and it is up to the developer to encode the data relatively. The library is there to help you draw your data, not actually manipulate it.

To get an idea of what I’m talking about, imagine you’re trying to build a line graph of Google’s stock price for the last five days: 689.96, 669.23, 673.35, 677.37, 689.89. Google’s Chart API has no idea what to do with these numbers–it wants to know what those numbers are relative to each other, on a scale of 0-100 (using text encoding, probably the best of the supported encoding methods).

At first glance, I suppose you could simply divide by ten in order to get a set of numbers that fits within the 0.0-100.0 space: 68.9, 66.9, 67.3, 67.7, 68.9. Problem is because the changes are relatively small between the points, you’ll end up with essentially a straight line like the graph on the right.

Not a very interesting or useful.

What you really need is something that can scale the graph so you can actual see the detail of the information. Because the stocks prices range from 669.23 to 689.96, perhaps it would make sense to make the scale from 660 to 700, which will provide adequate detail.

Scaling and adjusting the numbers relatively between 660 and 700 is a bit harder, but never fear as I wrote a little PHP function that can do it for us:

1
2
3
4
5
6
7
8
9
10
11
12

function googleChartTextEncoding($Values,$Min,$Max) {
    $EncodedValues = array();
    for ($i=0;$i<sizeof($Values);$i++) {
        $EncodedValues[] = round((($Values[$i] - $Min)/ ($Max - $Min)) * 100,1);
    }
    return(implode(",",$EncodedValues));
}
 
$Min = 660;
$Max = 700;
$Values = array(689.96, 669.23, 673.35, 677.37, 689.89);
$Data = googleChartTextEncoding($Values,$Min,$Max);

This function returns values that means nothing to us as humans, but means the world to Google’s Chart API: 74.9, 23.1, 33.4, 43.4, 74.7. When we use these new scaled values, we get a graph that accurately represents the changes over the last week.

The problem is, although we’ve got the graph looking right, Google’s API also doesn’t know what metrics we’ve scaled the data on (660 to 700), so it can’t provide us with meaningful labels for the Y-Axis. Luckily I’ve got another function that can scale the labels as well:

1
2
3
4
5
6
7
8
9

function googleChartLabels($Values,$Ticks=4,$Min,$Max){
    $Spread = $Max - $Min;
    $Ticks = $Ticks-1;
    $Interval = $Spread / $Ticks;
    for ($t=0;$t<=$Ticks;$t++) {
        $Labels[] = round($Min + ($Interval * $t));
    }
    return(implode("|",$Labels));
}

When we put it all together:

1
2
3
4
5
6
7
8

$Min = 660;
$Max = 700;
$Ticks = 4;
$Values = array(689.96, 669.23, 673.35, 677.37, 689.89);
$Data = googleChartTextEncoding($Values,$Min,$Max);
$Labels = googleChartLabels($Values,$Ticks,$Min,$Max);?>
 
<img src="http://chart.apis.google.com/chart?cht=lc&amp;chs=200x100&amp;chd=t:%3C?=$Data?%3E&amp;chxt=x,y&amp;chxl=0:%7C12/14%7C12/15%7C12/16%7C12/17%7C12/18%7C1:%7C%3C?=$YLabels?%3E" />

We get a nice-looking graph that is accurate and usable.

I suspect that Google will eventually extend the API to do all this for you, so you can just give it raw numbers and it will automatically scale the numbers and figure out the axis labels for you, much the way they eventually added GeoCoding to the Google Maps API. Until then this is a good stop-gap solution.