January 2009 • Async.fi

Visitor Locator, Take Two

January 6, 2009The new version that I hacked together stores number of visits per country and shows the totals when a user clicks a countrys' marker. Visits are stored in an SQLite database, which, as you may know, makes things very easy as there is no server to look after etc. I was thinking of using Berkeley DB, because in an app like this, all that SQL is simply unnecessary sugar, but was lazy in the end (as usual). Update: Added country flags in place of the same default icon for every country (see: Custom Icons section in Google Maps API). Update 2: Added tooltip-like functionality, which shows country details in a transient window (label) instead of the default info window. See GxMarker for additional info. Continuing here from where last nights' script ended. This is just the PHP side of things; Google Maps API examples can be found elsewhere. First we open an SQLite database and create a table for our visitor data if table does not exist:

try {
        $db = new PDO('sqlite:' . $_SERVER['DOCUMENT_ROOT'] . '/../db/visitor-locator.sqlite3');
} catch(PDOException $exception) {
        die($exception->getMessage());
}

$stmt = $db->query('SELECT name FROM sqlite_master WHERE type = 'table'');
$result = $stmt->fetchAll();
if(sizeof($result) == 0) {
        $db->beginTransaction();
        $db->exec('CREATE TABLE visits (country TEXT, visits INTEGER, lat TEXT, lng TEXT);');
        $db->commit();
}

Next, check if the country is already in the table and if it is, increment the 'visits' field:

$stmt = $db->query('SELECT country, visits FROM visits WHERE country = '' . $countryname . ''');
$result = $stmt->fetch();

if($result['country']) {
        $db->beginTransaction();
        $stmt = $db->prepare('UPDATE visits SET visits=:visits, lat=:lat, lng=:lng WHERE country=:country');
        $stmt->bindParam(':country', $countryname, PDO::PARAM_STR);
        $visits = $result['visits'] + 1;
        $stmt->bindParam(':visits', $visits, PDO::PARAM_INT);
        $stmt->bindParam(':lat', $lat, PDO::PARAM_STR);
        $stmt->bindParam(':lng', $lng, PDO::PARAM_STR);
        $stmt->execute();
        $db->commit();
}

If country was not in the table, create a row for it:

else {
        $db->beginTransaction();
        $stmt = $db->prepare('INSERT INTO visits (country, visits, lat, lng) VALUES (:country, :visits, :lat, :lng)');
        $stmt->bindParam(':country', $countryname, PDO::PARAM_STR);
        $visits = 1;
        $stmt->bindParam(':visits', $visits, PDO::PARAM_INT);
        $stmt->bindParam(':lat', $lat, PDO::PARAM_STR);
        $stmt->bindParam(':lng', $lng, PDO::PARAM_STR);
        $stmt->execute();
        $db->commit();
}

And lastly, fetch all rows and form a Javascript array for our client-side script to use:

$result = $db->query('SELECT country, visits, lat, lng FROM visits');

echo "<script type=\"text/javascript\">\n";
echo "//<![CDATA[\n";
echo "var tbl_country = []; var tbl_visits = []; var tbl_lat = []; var tbl_lng = []; var count = 0;\n";
foreach($result->fetchAll() as $row) {
        echo 'tbl_country[count] = \'' . $row['country'] . '\'; ';
        echo 'tbl_visits[count] = \'' . $row['visits'] . '\'; ';
        echo 'tbl_lat[count] = \'' . $row['lat'] . '\'; ';
        echo 'tbl_lng[count] = \'' . $row['lng'] . '\';';
        echo " count++;\n";
}
echo "//]]>\n";
echo "</script>\n";

Tagged with: google-maps-api hostip-info php

Categorised as: snippet

URL Fetch API, MiniDom (Google App Engine)

January 4, 2009

Fetching stuff with the URL Fetch API is simple (especially if one has faith that the source is there and it will deliver inside GAE time limits):

from google.appengine.api import urlfetch
from xml.dom import minidom

def parse(url):
  r = urlfetch.fetch(url)
  if r.status_code == 200:
    return minidom.parseString(r.content)

As is accessing the resulting DOM with MiniDom. Here the source is an Atom feed:

import time

dom = parse(URL)
for entry in dom.getElementsByTagName('entry'):
  try:
    published = entry.getElementsByTagName('published')[0].firstChild.data
    published = time.strftime('%a, %d %b', time.strptime(published, '%Y-%m-%dT%H:%M:%SZ'))
  except IndexError, ValueError:
    pass
  …

Tagged with:

Categorised as: snippet