TW Blog :: Entries for PHP

Excel To MySQL Via PHP ODBC And COM

Also relates to MySQL and COM

As a keen statistician with a fascination for weather variation over time (in particular in relation to equilibrium tidal variations) I have acquired comprehensive weather data over the past few years from my digital weather station. All the archive pressure and temperature readings are tucked away in Excel spreadsheets - this year I have actually moved over to Open Office as my spreadsheet of choice. I finally decided it was time to move some of this data into a MySQL database on my server, where I could then manipulate the data for display on the web.

To start with I have decided to keep things simple, storing average daily pressure and temperature for graphical display through the GD library. In part for experimentation, I had a look last weekend at the options for achieving the transfer using the PHP Command Shell.

Firstly I tried using ODBC by creating a System DSN for the Microsoft Excel Driver, and SQL to extract the data. The inbuilt ODBC PHP functions are fairly intuitive and the script was quite simple to build. However the solution was too proprietary since I had to first use MS Query to establish the field names for the required columns and synchronising the query itself:

define("COL_DATE", "F2");
define("COL_AVG_PRESSURE", "F27");
define("COL_AVG_TEMP", "F30");

[..snip..]

$s_query = "SELECT `" . $arr_month . "$`." . COL_DATE
         . ", `" . $arr_month . "$`." . COL_AVG_TEMP . " "
         . ", `" . $arr_month . "$`." . COL_AVG_PRESSURE . " "
         . "FROM `" . $arr_month . "$` "
         . "ORDER BY `" . $arr_month . "$`." . COL_DATE;

Instead I decided to have a look at the DCOM abilities of PHP. Unfortunately documentation is sparse in the PHP Manual and across the Web generally, with only one clear example of accessing Excel that I could find. So I decided to trial and error a bit to see how much of the Excel Object Model is recognised. Based in part on the example mentioned above this was the best solution I could get to run:

$xls = new COM("Excel.sheet") or die("Did not connect");
print "Application name:{$xls->Application->value}\n";
print "Loaded Version: {$xls->Application->version}\n";
foreach($years as $year)
{
  $workbook = "C:\weather\_" . $year . ".xls";  
  $wkb = $xls->Application->Workbooks->Open($workbook) 
         or die("Failed to Open Workbook"); 
  $xls->Application->Visible = 1;
  foreach($sheets as $sheet)
  {
    $month = (array_search($sheet, $sheets) + 1);
    $ws = $wkb->Worksheets($sheet);
    $ws->activate;
    for ($day = 1, $i = First_Cell; 
         $i <= Last_Cell; $i++, $day++)
    {
      if (checkdate($month, $day, $year))
      {
        $arr_day[0] = $year . "-" 
                    . $month . "-" . $day;
        $cell = $ws->Cells($i, AD);
        $cell->activate;
        $arr_day[1] = (! empty($cell->value)) 
                    ? sprintf("%01.1f", $cell->value) 
                    : NULL;  
        $cell = $ws->Cells($i, AA);
        $cell->activate;
        $arr_day[2] = (! empty($cell->value)) 
                    ? round($cell->value) 
                    : NULL;          
        $values[] = $arr_day;
      }      
    }
  }
  $xls->Application->ActiveWorkbook->Close("False");
}
$xls->Release();
unset($xls);

This algorithm quite literally steps through each required cell in turn, by cycling through the monthly worksheets and yearly files (note the constants AA and AD define the indexes of the required columns). It is however quite inefficient, and a faster method would be to extract values as range objects using a call like $xls->Application->Range('Jan!$AA$6:$AA$36).Value, but the PHP COM library does not seem to accept this. Still, the above code works and could be manipulated and applied to future data. However, I will probably look to use Python in the future, with a broader implementation of COM via the Win32Com module and its integration with Open Office. At least I now know that MS Office documents can be manipulated by PHP.

Posted on Feb 12, 2004 at 21:26:53. [Comments for Excel To MySQL Via PHP ODBC And COM- 10]

Selecting Next And Previous

Also relates to MySQL

Starting to get things sorted with the new look business site now, so time to catch up on some blogging. I am going to start off by looking at the common scenario of creating next and previous links for a data result set.

A typical application of the next and previous links is in a News Archive. These links offer intrinsic usability benefits presenting the user with less steps to view articles (eg they do not have to jump back to an index to find the link for the next article). However, selecting neighbouring items from a MySQL database is not that simple.

The obvious solution is to select the item with a unique key one less/more than the current record. But this has two drawbacks. Firstly it requires integers as unique keys, which is not always the case. It also assumes that there is a continuous sequence of unique keys with no breaks. This is rarely the case in a content management system, where some items might be deleted.

For this example I am going to assume that the unique key is not integer based, and instead focus on the timestamp at which each entry was added. So rather than adding or subtracting directly from a unique key, all that is required are a couple of comparative selection queries, having firstly established the timestamp for the current record.

SELECT @now := added 
       FROM table 
       WHERE uid = {x};
SELECT @prev := uid 
       FROM table 
       WHERE added < @now 
       ORDER by added DESC 
       LIMIT 0, 1;
SELECT @next := uid 
       FROM table 
       WHERE added > @now 
       ORDER by added ASC 
       LIMIT 0, 1;

This in itself is one solution, if, rather than creating SQL variables, the required data was just extracted directly in the second and third statements. But this will require two separate calls to the database abstraction layer from the API, with two sets of result data to manipulate. The code can be made more manageable at each layer if this is reduced to a single call. Having acquired the unique ID of the next and previous item, it is merely a case of collecting the data.

SELECT IF(uid = @prev,'p','n') as position, uid, title 
       FROM table 
       WHERE (uid = @prev) OR (uid = @next);

In this statement I have created an additional result field position which will be set to p or n for the previous and next records respectively. This is because I generally use PEAR::DB as my database abstraction with a set of custom functions. In this scenario, the first three statements can be executed in the standard way, $dbh->query($query), and the final statement called via $dbh->getAssoc($query). This will return an associative array of a maximum two items with keys of either n or p and the corresponding data.

Of course there is always more than one way of doing things, and I feel the logic of this alternative approach quite elegant, however, with limited permissions on my current servers, I can only test this on my local machine.

SELECT @row := 0;
DROP TABLE IF EXISTS temptable;
CREATE TEMPORARY TABLE temptable 
       SELECT @row := @row + 1 as rownum, table.* FROM table
       ORDER BY added DESC;
SELECT @current := rownum 
       FROM temptable 
       WHERE uid = {x};
SELECT IF(rownum < @current, 'n', 'p') as position, uid, title
       FROM temptable 
       WHERE (rownum = @current - 1) OR (rownum = @current + 1)

The code utilises the CREATE TEMPORARY TABLE statement to create a replica of the data that is being examined, and adding an additional field to the temporary table to act as a unique integer identifier (rownum). This resolves the problem outlined initially regarding the datatype of unique indexes, and sequence gaps.

There are no doubt copious other solutions to this problem. Of course an obvious choice is to do the work on the API layer. For example, the entire dataset could be collected and then PHP's array functions used to manipulate the dataset and extract the required information. However, I have found in the past this can get quite kludgey! Doing the work at the Database layer itself does not (appear to) harm the performance of the database, and helps keep all layers in the application that bit purer.

Posted on Jan 30, 2004 at 00:45:22. [Comments for Selecting Next And Previous- 4]