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";
Categorised as: snippet