Dynamic Map and Calendar Capability with PHP and MSSQL
Using a database as a single form of storage allows for content to be updated dynamically and efficiently. Here at the GIS center, we utilize a Microsoft SQL database to store events and the information tied to the events.
My first project was to simply add events to a database using a PHP form. From there, I created two additional features, a calendar and a map using the google maps api. With both the calendar and the map reading from the database, all we have to do is add an event through the form and it is instantly loaded on the map and the calendar.
Form Technology
The form utilizes basic HTML and CSS. Javascript and JQuery are introduced on for pre-processing on the client side and the form is run through a final processing and addition to the database with PHP.
When entering a date in the form, we use a JQuery date picker plugin. Essentially, a tiny calendar that allows the user to select a date. This helps to eliminate inconsistent date formats so everything remains consistent in the database. To make an input box recognize a click, you need to use a JQuery selector and add the datepicker() method.
Once submitted, the event is geocoded through the Google Maps API, then added to the database. With the date geocoded successfully, we can now add the event to the map on load. The dates are also able to load dynamically in the calendar because they are updated in the database also. Since geocoding is a common event, we wrote a function that accepts an address, city and state, sends it to the Google Maps server, which returns our address geocoded. The geocoding script from http://code.google.com/apis/maps/articles/phpsqlgeocode.html
function geoCode($address, $city, $state){
define(“MAPS_HOST”, “maps.google.com”);
define(“KEY”, “YOURKEYHERE”);
$combinedAddress=$address.’,’.$city.’,’.$state;
$delay=0;
$base_url=”http://”.MAPS_HOST.”/maps/geo?output=xml”.”&key=”.KEY;
$request_url=$base_url.”&q=”.urlencode($combinedAddress);
$xml=simplexml_load_file($request_url) or die(“URL Not Loading!”);
$status=$xml->Response->Status->code;
$coordinateArray;
if(strcmp($status, “200″)==0){
$geocode_pending=”false”;
$coordinates=$xml->Response->Placemark->Point->coordinates;
$coordinatesSplit = explode(“,” , $coordinates);
// Format: Longitude, Latitude, Altitude
$lat = $coordinatesSplit[1];
$lng = $coordinatesSplit[0];
$coordinateArray[0]=$lat;
$coordinateArray[1]=$lng;
} else if (strcmp($status, “620″) == 0) {
// sent geocodes too fast
$delay += 100000;
} else {
// failure to geocode
$geocode_pending = false;
echo “Address ” . $combinedAddress . ” failed to geocoded. “;
echo “Received status ” . $status . “\n”;
}
return $coordinateArray;
}
Map Technology
The map utilizes a simple PHP/MSSQL query and the Google Maps API. The events are loaded through the database query, stored in a javascript array, then placed on the map using the coordinates saved in the database. This all happens on page load. Since PHP is run server side, we have the php script export the javascript array. Since javascript is client side, once we have it exported we can manipulate the javascript array on the fly and load just what we need on the map when we need it. In future plans, once we get more events is to just load the array of coordinates along with an ID number and do an AJAX call to the server to load the description and other information from the server only when needed. This will save on memory.
<?php
include(“functions.php”);
$conn = connectDB();
//Query grabs all of the events in the events table
$eventsQuery = “SELECT event_name, latitude, longitude, event_description, start_date, start_time, end_date, end_time, address, city, state, zip, cost, url, video FROM events”;
//Using the ODBC connector, we grab all the information from the query
$eventsResult = odbc_exec($conn, $eventsQuery);
$counter = 0;
//While there is information to be grabbed, we pull it down and echo out the javascript array so the client can manipulate it.
while($events = odbc_fetch_array($eventsResult)){
$description = odbc_result($eventsResult,4);
echo ‘eventData['.$counter.']= new Array();’;
echo ‘eventData['.$counter.']["events"]=”‘.odbc_result($eventsResult,1).’”;’;
echo ‘eventData['.$counter.']["latitude"]=”‘.odbc_result($eventsResult,2).’”;’;
echo ‘eventData['.$counter.']["longitude"]=”‘.odbc_result($eventsResult,3).’”;’;
echo ‘eventData['.$counter.']["description"]=”‘.$description.’”;’;
echo ‘eventData['.$counter.']["start_date"]=”‘.odbc_result($eventsResult,5).’”;’;
echo ‘eventData['.$counter.']["end_date"]=”‘.odbc_result($eventsResult,6).’”;’;
echo ‘eventData['.$counter.']["start_time"]=”‘.odbc_result($eventsResult,7).’”;’;
echo ‘eventData['.$counter.']["end_time"]=”‘.odbc_result($eventsResult,8).’”;’;
echo ‘eventData['.$counter.']["address"]=”‘.odbc_result($eventsResult,9).’”;’;
echo ‘eventData['.$counter.']["city"]=”‘.odbc_result($eventsResult,10).’”;’;
echo ‘eventData['.$counter.']["state"]=”‘.odbc_result($eventsResult,11).’”;’;
echo ‘eventData['.$counter.']["zip"]=”‘.odbc_result($eventsResult, 12).’”;’;
echo ‘eventData['.$counter.']["cost"]=”‘.odbc_result($eventsResult, 13).’”;’;
echo ‘eventData['.$counter.']["url"]=”‘.odbc_result($eventsResult, 14).’”;’;
echo ‘eventData['.$counter.']["gMapsLatLong"] = new google.maps.LatLng(‘.odbc_result($eventsResult,2).’, ‘.odbc_result($eventsResult,3).’);’;
/*Event Marker Array Generation*/
//Checks to see if the event is video capable, if not, then it prints out a different symbol
if(odbc_result($eventsResult, 15) == “yes”){
echo ‘eventData['.$counter.']["gMapsMarker"] = new google.maps.Marker({position: eventData['.$counter.']["gMapsLatLong"], map:map, icon: \’http://google-maps-icons.googlecode.com/files/video.png\’});’;
}else{
echo ‘eventData['.$counter.']["gMapsMarker"] = new google.maps.Marker({position: eventData['.$counter.']["gMapsLatLong"], map:map, icon: \’school.png\’});’;
}
$counter++;
}
echo ‘}’;
?>
Calendar Technology
Using a PHP/MSSQL query, we pull the events from the table in the database. They are then loaded into a javascript array which is read by the JQuery Full Calendar plugin and loaded into a calendar view. When the user hovers over the event, the description pops up using another JQuery QTip plugin.
Similar to the map, we have PHP read from database, load all of the events and echo them to the javascript array.
<script type=”text/javascript”>
$(document).ready(function() {
$(‘#calendar’).fullCalendar({
events: [
<?php
include("functions.php");
$conn=connectDB();
$eventQuery="SELECT event_id, event_name, event_description, start_date, end_date, url FROM events";
$eventResult=odbc_exec($conn,$eventQuery);
$eventArray="";
while($event=odbc_fetch_row($eventResult)){
$eventArray.= '{
title : \''.str_replace("'","\'",odbc_result($eventResult,2)).'\',
start : \''.str_replace("'","\'",odbc_result($eventResult,4)).'\',
end : \''.str_replace("'","\'",odbc_result($eventResult,5)).'\',
url : \''.str_replace("'","\'",odbc_result($eventResult,6)).'\',
description: \''.str_replace("'", "\'",odbc_result($eventResult,3)).'\',
},';
}
$eventArray=substr($eventArray,0,strlen($eventArray)-1);
$eventArray.='],’;
echo $eventArray;
?>
eventMouseover: function(event){
if(event.url){
//alert (event.description);
$(“.fc-event-inner”).qtip({
content: event.description,
position: {
corner: {
target: “topRight”,
tooltip: “topMiddle”
}
}
});
return false;
}
}
})
});
</script>
The current solution works for the time being, however, we are in the process of developing an API that will load via AJAX so there isn’t the initial overhead of loading all of the events.
