Peter Robins, his website

Setting up this site in Symfony

Phase 5: changing the flights pages to Google Maps with JSON/AJAX

Setting up the flights data with one page per airport created an over-large number of pages and was in general very inefficient so, as I was considering replacing the SVG map with a Google Map anyway, I decided to go the whole hog and incorporate all the route pages into that. Google Maps is of course largely about Javascript, which is not the subject of this tutorial, but the map still needs to get the data from the server, and the simplest way to do that is for the requests to the server to generate files in JSON format. I suppose you could just have PHP scripts which bypass Symfony completely, but here's how I did this using Symfony actions.

I set the map up so that initially it shows the main air/ports, i.e. those in England I track in the DB. People can then click on a marker, and this displays the routes from that air/port. There are thus three calls to the server, the initial display and lists for the appropriate airport and seaport, and hence three Symfony actions.

Routing
The intro and coverage pages remain as before, but the airportlist, routelist and ferrylist disappear, and are replaced by: 
ports_json:
  url:   /flights/ports2json.
  param: { module: flights, action: ports2json }

routes_json:
  url:   /flights/flights2json/:from.
  param: { module: flights, action: flights2json }

ferries_json:
  url:   /flights/ferries2json/:from.
  param: { module: flights, action: ferries2json }
Note that the urls end with '.', i.e. there is no .html suffix
Actions
As we have seen, Symfony actions are designed to output data elements that can be used by the presentation templates. For this they use the ORM, Propel, to generate objects for the query result set. However, in these new actions, what we want is simply an array of the result of the SQL query in JSON format; having it generate objects is a complete waste of time, as they will not be used. So in this case we bypass Propel and the model altogether and just have the action perform the query and convert the output to JSON format. For example, the new ferries list looks like: 
  public function executeFerries2json()
  {
    $from = $this->getRequestParameter('from');
    $con = Propel::getConnection('flights');
    $stmt = $con->prepare("SELECT 'ferries' as datatype, toport as port, latitude, longitude, ferries.co as co, ferrycos.name as coname, web, comments FROM ferries, ports, ferrycos WHERE toport = ports.name and ferries.co = ferrycos.co and fromport = ? union SELECT 'ferries' as datatype, fromport as port, latitude, longitude, ferries.co as co, ferrycos.name as coname, web, comments FROM ferries, ports, ferrycos WHERE fromport = ports.name and ferries.co = ferrycos.co and toport = ? ORDER BY port, coname ASC");
    $stmt->bindValue(1, $from);
    $stmt->bindValue(2, $from);
    $stmt->execute();
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $rows[] = $row;
    }
    $this->getResponse()->setContent("loadFerries(".json_encode($rows).")");
    return sfView::NONE;
  }
Here, we use Propel's connection logic, but then use one of PDO's prepared statements to execute the query. The result is then JSON-encoded, wrapped in the call of the loadFerries() Javascript method, and put directly in the Content property of the Response object. As there is no view involved, we return 'sfView::NONE'. So, the Javascript routine in the client calls, for example, '/flights/ferries2json/Dover' on the server, which gets a list of the appropriate to-ports from the database. These are returned to the client as a Javascript array, wrapped in loadFerries(). The client runs loadFerries(), which creates the markers and puts them in the appropriate places on the map.
Database
To be able to use the air/port info on the map, I had to add latitude and longitude to each row in the airports and ports tables. So strictly speaking the model has changed. However, as the existing actions are not affected and the JSON actions do not use it, there is no real need to regenerate it.

April 2008, updated April 2010