Code Notebook: Visitor Locator, Take Two

The 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";

2009-01-06

Visitor Locator, Take Two