Richmond Sunlight provides tools for keeping track of what's going on in the Virginia General Assembly.

One feature of the website is tracking bills of interest by associating them with a free user account.  In addition to reviewing the status of the bills through their website, their Photosynthesis service and API makes it possible to gather information about specific bills in an automated fashion.  Details of Photosynthesis service and the API are documented on their website

I recently helped out on a small project using the API to pull and display information on specific bills.  The API is json and the website runs php, so the result was a nice little example of how to retrieve data in json format and display it in php.  I created this write up in hopes that it might help someone looking to do something similar.

 Each Photosynthesis account is assigned a unique ID that can be used to access the account.  The ID is shown as part of the URL listed at the bottom of the Photosynthesis account.  An example account URL is shown below.

To retrieve data about the bills tracked through a specific account, simply retrieve the json data by substituting the account ID in the XXXXX in the URI shown below:

To produce some like this

Entering this URI into a browser returns a json file in text format that represents the bills being tracked through the associated user account.

To do something useful with the data, it needs to be decoded.  PHP's json_decode() function provides an easy way convert json data into a php array. The example code below retrieves the list of bills for the account and prints the data associated with each one.

// get bills
$jsonUrl = "http://api.richmondsunlight.com/1.0/photosynthesis/2cgp0.json";
$json = file_get_contents($jsonUrl,0,null,null);
$bills = json_decode($json,true);
$billCount = count($bills);
for ($i = 0; $i < $billCount; $i++) 
{  
  // [number] => HB2197 [year] => 2011 [notes] => [url] => http://www.richmondsunlight.com/bill/2011/hb2197/ 
  echo "number: " . $bills[$i]['number'] . "<br/>\n";
  echo "year: " . $bills[$i]['year'] . "<br/>\n";
  echo "url: " . $bills[$i]['url'] . "<br/>\n";
  echo "notes: " . $bills[$i]['notes'] . "<br/>\n";
}

A second call to the API can be used to retrieve more details on each of the bills by using the year and bill number as part of the URI.  For example to retrieve the details on bill hb2197 from 2011, the json request would be

The PHP to handle this looks like

// get bills
$jsonUrl = "http://api.richmondsunlight.com/1.0/photosynthesis/2cgp0.json";
$json = file_get_contents($jsonUrl,0,null,null);
$bills = json_decode($json,true);

$billCount = count($bills);
for ($i = 0; $i < $billCount; $i++) 
{  
  // to get more info on the bill
  // http://api.richmondsunlight.com/1.0/bill/YEAR/BILLNUMBER.json

  $jsonUrl = "http://api.richmondsunlight.com/1.0/bill/".$bills[$i]['year']."/".strtolower($bills[$i]['number']).".json";
  $json = @file_get_contents($jsonUrl,0,null,null);
  if ($json)
  {
    $bill = json_decode($json,true);
    if (!is_null($bill))
    {     
      echo "number: "  . $bills[$i]['number'] . "<br/>\n";
      echo "year: "    . $bills[$i]['year'] . "<br/>\n";
      echo "url: "     . $bills[$i]['url'] . "<br/>\n";
      echo "chamber: " . $bills[$i]['current_chamber'] . "<br/>\n";
      echo "status: "  . $bills[$i]['status'] . "<br/>\n";
      echo "date: "    . $bills[$i]['date_introduced'] . "<br/>\n";
      echo "outcome: " . $bill['outcome'] . "<br/>\n";
      echo "title: "   . $bill['title'] . "<br/>\n";
      echo "summary: " . $bill['summary'] . "<br/>\n";
      echo "text: "    . $bill['text'] . "<br/>\n";
      echo "patron: "  . $bill['patron']['name'] . " (" . $bill['patron']['id'] . ")<br/>\n";
      echo "notes: "   . $bills[$i]['notes'] . "<br/>\n";      
      $tagCount = count($bill['tags']);
      for ($tagIndex = 0; $tagIndex < $tagCount; $tagIndex++) 
      {
        echo "tag: " . $bill['tags'][$tagIndex]['tag'] . "<br/>\n";
      }
    }
  }
}

Now that we have all the data associated with the bills, it is straightforward to display them as desired.  A final issue that we need to address concerns performance.  Richmond Sunshine is very clear that their server infrastructure is limited and they will remedy abuse by blacklisting the offender.  Therefore, we must implement a caching strategy.  A very simple one is to store the data in a local database that we refresh periodically.

The following code checks a timestamp stored in a one row table called cache.  If the timestamp has not be refreshed in the last hour, it sets a flag to refresh the data and reset the timestamp. 

$refresh = 0;
if ($refresh == 0)
{
  $result = mysql_query("SELECT * FROM cache WHERE last_refresh >= date_sub( now( ) , INTERVAL 1 HOUR ) ");
  if (!$result)
  {
    die('Error checking cache: ' . mysql_error());
  }
  if (mysql_num_rows($result) == 0)
  {
    $refresh = 1;
  }
}  

if ($refresh)
{
  // Refresh the data here
  
  if (!mysql_query("DELETE FROM cache;") || !mysql_query("INSERT INTO cache (last_refresh) VALUES (NOW());"))
  {
    die('Error updated timestamp: ' . mysql_error());
  }
  echo "Data Refreshed<br/>\n";
}

With the cache timer logic in place, all that is left is to create a database table to store the bill data.  When it is time to refresh the data, all the rows will be deleted from the table and repopulated with fresh data from the api. In the meantime, all requests for the bill data are pulled from the local database.  Of course, since the data is only being refreshed hourly, it would be possible to cache the final output, but we are not going there in this article.

To see a complete version of this example in action, click here.

The example code is available for download below.

To use it, create a mysql database and run the sql script against it.  Create a user and give the user read/write access to the database.  Update the following in the php script to match the database setup:

Finally, set Photosynthesis id to pull data from the appropriate account.