The latest version of Shashin (v2.6), which is my plugin for using Picasa with WordPress, adds support for unlisted Picasa albums. Supporting unlisted albums is possible because 1. Picasa does not actually require any authentication to view the photos in unlisted albums if you know their URLs (they simply don’t show you links to view unlisted albums at Picasa, or view their RSS feeds, unless you’ve been authorized), and 2. the Google ClientLogin API is available.

The ClientLogin API documentation has really good prose, but precious little code. There is a freely available PHP library (fron Zend) for interacting with the Google APIs, but I didn’t want to bloat Shashin by several megabytes just so I could use the library’s authentication method (and there are enough interdependencies in the library that I couldn’t easily extract the authentication components).

Shashin uses ClientLogin instead of AuthSub because Shashin has an option for automatic scheduled synchronizing of Picasa albums, so the username and password needs to be saved. I can’t expect Shashin users to type in their Picasa username and password every time Shashin syncs its album data with Picasa.

Below I’ll explain how authenticate to Google services using ClientLogin, and then show a couple examples of interactions with Google services.

Most of the time, if you need to create an HTML table to display data pulled from a database, it’s a fairly straightforward task: the data is organized in rows, and since HTML tables are generated in rows, it’s easy to loop through the rows of data and display them in succession. But sometimes you can have a situation where you want to display the data as columns, which is not something you can easily do in HTML. Here’s an example of how to approach this challenge.

Displayed below is an array of data from a project I’ve been working on. I’ll explain the color coding in a minute. The first element is a sub array of laboratory sample IDs, and the subsequent elements are subarrays indicating which laboratory tests should be run on which samples. (I’ll spare you an explanation of the database structure behind all this, but suffice it to say that pulling out the data with this organization was the most efficient solution).

Array[4] (
    0 => Array[6] (
        0 => 'Sample ID',
        1 => 193,
        2 => 194,
        3 => 195,
        4 => 196,
        5 => 197,
    ),
    1 => Array[4] (
        0 => 'Phospholipids',
        1 => 'Y',
        3 => 'Y',
        5 => 'Y',
    ),
    2 => Array[6] (
        0 => 'Apolipoprotein A-I',
        1 => 'Y',
        2 => 'Y',
        3 => 'Y',
        4 => 'Y',
        5 => 'Y',
    ),
    3 => Array[4] (
        0 => 'Gastrin',
        2 => 'Y',
        3 => 'Y',
        4 => 'Y',
    ),
)

How I ultimately what to display the data is like this:

To get from here to there, you need to visualize how you want to arrange the data elements. If you use the color coding I assigned to the array indexes above, you can picture the data in rows, like this:

0,0    1,0    2,0    3,0

0,1    1,1    2,1    3,1

etc.

To generate a display of the data in this manner, you need to loop through the arrays in an inside-out manner. You loop through the subarrays to set the rows, and then have a nested loop through the parent array to set the cells. Here’s the PHP code for it:

$content = "<table>\n";

// This loop controls the table rows
// $cols is the name of the parent array
for ($inner=0; $inner < count($cols[0]); $inner++) {
    $content .= "<tr>";

    // This loop generates the table cells
    for ($outer=0; $outer < count($cols); $outer++) {
        // this assumes you want the first row to be a header row
        $tag = ($inner == 0) ? "th" : "td";  
        $content .= "<$tag>{$cols[$outer][$inner]}</$tag>";  
    }

    $content .= "</tr>\n";
}

$content .= "</table>\n";

// then display or return $content...

The first subarray contains the Sample IDs, so I used it to set the maximum count for the outer loop that controls the HTML table rows (because I can be confident there will never be data beyond the last sample). If you don’t have an analogous subarray, you’ll need to check to see which subarray is longest, and use that to control the count.

Often when you’re generating an HTML table on the fly, you need to be able to display an arbitrary number of columns and rows. This typically comes up when displaying data from a database, where you can get any number of records (rows) back. Fortunately handling a variable number of rows is easy in HTML, since all you have to do is keep adding table row tags until you run out of records. The harder, and less common, case is handling a variable number of columns. An example of where this situation comes up is my Shashin plugin, as Shashin users can decide how many columns they want in the display of their photos. Another example is a form where you need multiple rows and columns for neatly laying out a variable number of checkboxes.

I’ve seen a number of strange and tortured solutions to this problem over the years, so I thought I’d share my solution, which is really simple (I’m sure many others have figured this out as well, but I haven’t seen it posted anywhere). What’s often missing in other solutions I’ve seen is properly closing the final table row when the number of cells in it is less than the preceding rows. Here’s some generic PHP code for it:

// desired number of columns - this can be any number
$cols = 3;

$output = "<table>\n";

$cell_count = 1;

for ($i = 0; $i < count($your_array); $i++) {
    if ($cell_count == 1) {
        $output .= "<tr>\n";
    }

    $output .= "<td>your cell content</td>\n";
    $cell_count++;

    // end the row if we've generated the expected number of columns
    // or if we're at the end of the array
    if ($cell_count > $cols || $i == (count($your_array) - 1)) {
        $output .= "</tr>\n";
        $cell_count = 1;
    }
}

$output .= "</table>\n";

And just for fun here’s the same thing in Smarty:

<table>
<{assign var="cell_count" value=1}>
<{foreach key=key item=item from=$your_array name=yourloop}>
    <{if $cell_count == 1}>
        <tr>
    <{/if}>
    <td>your cell content</td>
    <{math assign="cell_count" equation="$cell_count + 1"}>

    <{if $cell_count > 3 || $smarty.foreach.yourloop.last}>
        </tr>
        <{assign var="cell_count" value=1}>
    <{/if}>
<{/foreach}>
</table>

If you’re not familiar with PHP sessions and how to use session variables, getting up to speed isn’t easy. What makes it difficult to learn is that it’s hard to make sense of the online resources. That includes php.net, which has a ton of little pink and yellow boxes on its pages about sessions, with caveats about important changes between PHP versions. The “right way” to use sessions has changed with successive releases of PHP, and many of the old ways are now either more trouble than they’re worth, or simply may not work at all anymore. Throw in the changes with PHP classes (if you want to store objects in sessions), and the multiple possible ways your server can be configured for handling sessions, and it gets even more confusing. So any tutorial you read that’s more than a couple years old may lead you astray.

I’m not going to try covering all the possible variations, but here’s what works for PHP 4.3.9 with a default session configuration (see the Runtime Configuration section of that page if you want to see the details), using MySQL and Apache. I’m fairly certain this all works in PHP 5 as well, but I haven’t tested it.

• session_start: you need to call session_start() on every page (you can save yourself from repetitive coding by putting this and other page startup code in an include file). Note that by default it stays with the session id that’s automatically set when the user first starts his session – you don’t need to pass in the session id (the php.net documentation could be clearer on that point).
• Requiring your class files: for any objects stored in session variables that you use in a page, you will need to call require_once() on their class files before your call to session_start(). This is necessary for PHP to know how to map the data in the session variable to the class (another good candidate for reuse in an include file).
• session_register: you don’t need to use session_register() anymore. Just use the $_SESSION array to store your variables. I found a lot of online discussions from a few years ago about session_register being essential when putting objects in sessions – that doesn’t apply anymore.
• serialize: if you just want to store your objects in sessions (not in files or database tables), you don’t need to serialize() them yourself, and you don’t have to worry about losing the object type or access to its methods.
• mysql_connect: at first I tried putting a call to mysql_connect() in a startSession method of a database class I created, thinking I’d only need to call it once for the user’s session. That doesn’t work: the connection is lost after the http response for the page is complete. Trying to store the connection in a session variable does not magically persist it for the user. mysql_pconnect() is not the answer either, for reasons outlined in the php.net Persistent Databaase Connection page. The answer is to simply make connections as needed for each page – old connections will be reused if they’re available, so this doesn’t necessarily lead to an unnecessary proliferation of connections. You can even call mysql_connect() repeatedly on a page and it will by default re-use the connection that was initially opened on the page. This is nice if, like me, you’ve written a database class and you have a generic query method in it: you can call mysql_connect() in your query method, and not worry about how many times it’s being called by a particular page.

I should point out all of the foregoing is for garden variety purposes: managing connections for high traffic sites, security, scalability, and dealing with users who don’t accept cookies, are all beyond the scope of this post.

So, in my code I have no calls to session_register() or serialize() (as they’re not needed for storing objects in session variables), and in my database class’s query method, I call mysql_connect(). The main things to remember are requiring your class files before calling session_start(), and doing so on every page.

If you need to make use of an external program from within a PHP script, then this essay is for you. My example script is for managing an sftp connection (using OpenSSH), but the principals can be applied to any interaction that requires communication between your script and an external process.

One of the Penn Medical School’s business partners recently stopped allowing ftp connections to their servers for retrieving data files. They required us to switch to sftp (secure ftp). Those providing services for transferring sensitive data files over the internet have been steadily moving from ftp to sftp over the past couple of years, and from what I can see, the pace is accelerating. This poses a programming challenge if you have scripts that automate your ftp needs, as they’ll need to be re-written for sftp. This is not a trivial undertaking, especially if you’re programming in PHP. You can’t just swap out your PHP ftp function calls with sftp equivalents. Actually, you can, but you probably don’t want to, as you would have to upgrade to PHP 5 (adoption of which has been very slow across the PHP community) and you would have to install the PECL/ssh2 library, which – as noted on php.net – currently has no stable version.

So we had to roll our own sftp solution, which required using PHP’s program execution functions. The php.net documentation is good on this topic, but much of it is fully comprehensible only if you already know what you’re doing (this isn’t a criticism – it’s a documentation site after all, not a tutorial site). This annotated sample script will help you get started if you’re new to PHP’s program execution functions.

#!/usr/local/bin/php
<?php

$keyPath = 'path/to/your/ssh_key';
$login = 'your_username';
$server = 'your_sftp_server';
$connectionString = "Connecting to $server...\n";

$childPipes = array(
    0 => array("pipe", "r"), // stdin is a pipe that the child will read from
    1 => array("pipe", "w"), // stdout is a pipe that the child will write to
    2 => array("pipe", "w"), // stderr is a pipe that the child will write to
);

# turning off password authentication will avoid getting a password prompt if
# the key fails for any reason
$connection = proc_open(
    "sftp -oPasswordAuthentication=no -oIdentityFile={$keyPath} {$login}@{$server}",
    $childPipes, $parentPipes);

if ($connection === FALSE) {
    print "Cannot connect to $server.\n";
    exit;
}

PHP’s proc_open is a fork by another name. The $childPipes array is for setting up the communication channels from the child process perspective, and proc_open will set $parentPipes to a corresponding set of communication channels from the parent process perspective. Looking at the definition of $childPipes, the logic may seem backwards at first, but it’s not. For example, the parent process will write to the child’s stdin (element 0), which means the child process is reading that channel.

In the user contributed notes on the php.net proc_open page, most folks write out stderr to a file. But for our sftp script we need to see what’s coming through on stderr, so we’re not directing it to a file.

For establishing the connection, we turn off password authentication, which means we won’t get a password prompt if the key authentication fails. This is important, since the script cannot see or respond to such a prompt (the prompt goes directly to the terminal, so you can’t see it on stdin or stdout; you could see it if you want to do TTY buffering, but let’s not go there…).

# The "connecting..." message is written to stderr. Make sure there's nothing
# besides that in stderr before continuing.
$error = readError($parentPipes, TRUE);
sleep(3);
$error .= readError($parentPipes);

if ($error != $connectionString) {
    fclose($parentPipes[0]);
    fclose($parentPipes[1]);
    fclose($parentPipes[2]);
    $closeStatus = proc_close($connection);
    print $error;
    print "proc_close return value: $closeStatus\n";
    exit;
}

I don’t know if this is typical, but the sftp server we’re connecting to returns the “connecting…” welcome message on stderr (we’re reading stderr with a custom function named readError, which we’ll get to below). Having this message on stderr is problematic, since it’s not really an error message. An actual connection error, such as having a bad key, will come through on stderr after the “connecting…” message. This means we first look for the “connecting…” string (the TRUE argument to readError turns blocking on, so we’ll wait for it to appear – more on this below in the readError function), and then we have no choice but to sleep for a few seconds, to see if anything else comes through on stderr. And finally, to see if there was anything in stderr besides the “connecting…” message, we have no choice but to analyze the string 🙁 . This is an ugly solution, but dealing with stderr is difficult, since you never know when an error may or may not appear.

If we detect an error, we close the pipes before closing the connection. This is important for avoiding the possibility of a deadlock.

# gets us past the first "sftp>" prompt
$output = readOut($parentPipes);

After logging in, we’ll get an “sftp>” prompt on stdout. We’ll read from stdout to get past this prompt, using the custom function readOut (which is defined below).

# Get the directory listing and print it
writeIn($parentPipes, "ls -l");
$output .= readOut($parentPipes);
$error = readError($parentPipes);

if (strlen($error)) {
    fclose($parentPipes[0]);
    fclose($parentPipes[1]);
    fclose($parentPipes[2]);
    $closeStatus = proc_close($connection);
    print $error;
    print "proc_close return value: $closeStatus\n";
    exit;
}

print $output;

# close the sftp connection
writeIn($parentPipes, "quit");
fclose($parentPipes[0]);
fclose($parentPipes[1]);
fclose($parentPipes[2]);
$closeStatus = proc_close($connection);

if ($closeStatus != 0) {
    print "proc_close return value: $closeStatus\n";
}

This code just demonstrates getting a directory listing (using the custom function writeIn), printing it, and then closing the connection. You can use this as a template for any sftp commands you want to run.

function readOut($pipes, $end = 'sftp> ', $length = 1024) {
    stream_set_blocking($pipes[1], FALSE);

    while (!feof($pipes[1])) {
        $buffer = fgets($pipes[1], $length);
        $returnValue .= $buffer;

        if (substr_count($buffer, $end) > 0) {
            $pipes[1] = "" ;
            break;
        }
    }

    return $returnValue;
}

readOut loops over the stdout pipe until it sees an “sftp>” prompt, which is how we know that the server has finished writing to stdout. Note that we’ve turned off stream_set_blocking. This lets us define our own controls for reading from the stdout stream. In this case, we want readOut to return when the server has finished responding to a command. The best marker for that is the appearance of the “sftp>” after it finishes processing a command, so we set the while loop to break when it sees the prompt.

function readError($pipes, $blocking = FALSE, $length = 1024) {
    stream_set_blocking($pipes[2], $blocking);

    while (!feof($pipes[2])) {
        $buffer = fgets($pipes[2], $length);
        $returnValue .= $buffer;

        if ((!strlen($buffer) && $blocking === FALSE)
          || ($blocking === TRUE && substr_count($buffer, "\n") > 0)) {
            $pipes[2] = "" ;
            break;
        }
    }

    return $returnValue;
}

function writeIn($pipes, $string) {
    fwrite($pipes[0], $string . "\n");
}
?>

Reading from stderr is more complicated than reading from stdout, because 1. there is no equivalent to the “sftp>” prompt to let us know when the server is done writing to stdout, and 2. at any given time, there may or may not be an error. For most places in the script, we solve this problem by:

1. Calling readError after calling readOut. This is based on the supposition – which has proved reliable – that the server will finish writing to stderr by the time it has finished writing to stdout.
2. Setting stream_set_blocking to false. If we set it to true, the script would wait indefinitely for something to appear on stderr, and most of the time there will be nothing there.

The one situation when this approach doesn’t work is when we first log in, since the server writes to stderr before writing to stdout (as described above, it sends that “connecting…” message on stderr). In this case we turn blocking on, since we know the message is coming.

So far this script has been used with only one sftp server, so you may need to make some adjustments to make it work with your server (particularly with how it reads stderr when logging in). Also, I’d be interested in hearing from anyone who has a more elegant solution to handling the initial connection.

Stepping back from the specifics of sftp, the key thing to take away from this is that you will need to acquire a detailed knowledge of the behaviors of your external process so that your script can interact with it reliably. In particular, you need to test your handling of all the different kinds of errors the external process might throw at your script.

My “Solving the Unicode Puzzle” article was just published in the May 2005 issue of php|architect. Although the magazine is subscription only, they chose my article as this month’s free sample. Aside from introducing a few typos, it looks like they didn’t do any editing, so the published version is almost exactly the same as my original.

Remember that long post on UTF-8 from a few weeks ago – the one that gave you a sudden urge to take nap? I ran it by the editors of PHP Architect, and they’ve commissioned an article. I’m in the process of pumping it up to the required 4,000 words. I’ll be sending it off to them in a couple days. Commissioning the article only obliges them to pay me a small (very small) sum for it – it doesn’t mean they’ll necessarily publish it. But hopefully they will! I’ll let you know as soon as I find out – probably in a few weeks.