Previous Page
Next Page

10.2. Building the Non-AJAX Version of the Sun Rise and Set Viewer

This use case starts by building a standard HTML version, which means we're using AJAX as an enhancement at the end of the development process. This is a common AJAX approach, and it follows the basic pattern of building a site, finding its problem areas, and then updating those problem areas with AJAX to improve the user experience. This approach can have some disadvantages, especially on complex multipage sites, because adding in AJAX might cause workflows to be changed, causing large parts of the site to be updated. However, it does work well if you focus on specific tasks that can be upgraded.

This example stores its data in a MySQL database, so we need to start by setting it up. If you're not used to using a database with PHP, don't worry; the AJAX changes are usable with any server-side language. PHP just provides the needed code to have a fully working example. First, you need to create a database named rise_set. This can be accomplished in an admin tool such as phpMyAdmin or by running create database rise_set;. Once you have the database created, you need to create the table in which to store the data. To do this, load the SQL that is in Listing 10-1.

Listing 10-1. Schema.sql

CREATE TABLE `rise_set` (
  `city` varchar(150) NOT NULL default '',
  `day` date NOT NULL default '0000-00-00',
  `rise` varchar(4) NOT NULL default '',
  `set` varchar(4) NOT NULL default '',
  PRIMARY KEY  (`city`,`day`)

After the table is set up, you're ready to import some sun rise and sun set data. The data has been gathered from the U.S. Naval Observatory Web site ( Data for two example cities has already been prepared and can be used by running the SQL located in data/phoenix.sql and data/sturgis.sql. The script that generated these SQL files (data/dataToSql.php) is also provided, and you can use it to prepare data for other cities. First, get a dataset for a city by filling out the form at the U.S. Naval Observatory Web site, and then copy the data into a text file. (You want just the data, not HTML tags). Then you can run the dataToSql.php script over the file and redirect its output to an SQL file, which can then be loaded into your database server. Here's an example command loading in data for London, England:

php DataToSql.php london.txt > london.sql
mysql -u root rise_set < london.sql

Now that we've done the prep work, let's look at the overall design of this site. It consists of three parts:

  • The SunRiseSet class grabs data from the database and performs any needed formatting.

  • The Graph.php page generates the graph using data from the SunRiseSet class.

  • The Standard.php page builds the interface and displays a month of data at a time in a table.

Each part will get more coverage in the following sections, but the important part is Standard.php because it creates the HTML user interface that you will enhance with AJAX.

10.2.1. SunRiseSet Class

The SunRiseSet class is a simple class that connects to the database and provides methods for the other pages to access the data that's been loaded into the database. Because all database access takes place through this class, you can easily update it to support a different back end. If you want to run the examples on your own server, you may need to update the database connection information; these variables are located on lines 47 of SunRiseSet.class.php.

The SunRiseSet class, shown in Listing 10-2, contains two setter methods that are used to configure the user-selectable components of the data access. The setMonth method allows you to set the month of data that will be returned by the monthsData method, and it is used by the table that is rendering code. The setCities method allows you to set the city to which the data will be limited. Along with these setters, five data access methods are provided: possibleCities(), monthList(), monthsData(), graphData(), and minRise(). More details about these methods are shown later as we walk through the code of the class. The class also contains a number of methods for querying the database; these internal methods are prefixed with an underscore (_) to show that they shouldn't be used outside of this class.

Listing 10-2. SunRiseSet.class.php

1  <?php
2  // manage requesting data from a mysql database
4  $dbHost = 'localhost';
5  $dbUser = 'root';
6  $dbPass = '';
7  $dbName = 'rise_set';
9  class SunRiseSet {
10    var $_conn;
11    var $month;
12    var $year = '2006';
13    var $cities = array();
14    var $citiesIn = '';
16    function setMonth($month) {
17      $this->month = $month;
18    }
20    function setCities($cities) {
21      $this->cities = $cities;
22      foreach($cities as $key => $city) {
23        $cities[$key] =
24                  "'".
25                  mysql_real_escape_string($city)
26                  . "'";
27      }
28      $this->citiesIn = implode(',',$cities);
29    }
31    function possibleCities() {
32      $sql = "select
33                      distinct city
34                      from rise_set
35                      order by city
36                  ";
37      return $this->_querySingleArray($sql);
38    }
40    function monthList() {
41      $ret = array();
42      for($i = 1; $i < 13; $i++) {
43        $ret[$i] = date('F',mktime(1,1,1,$i));
44      }
45      return $ret;
46  }

The class starts with some basic setup. Lines 47 contain the basic database connection parameters. $dbHost contains the DNS name or IP address of the MySql server, $dbUser contains the username with which to connect, $dbPass contains the password, and $dbName contains the name of the database. After that, we start the class definition (line 9). Inside the class, we define five properties (lines 1014), which the methods of the class use to hold settings and shared variables. $_conn holds the database connection resource and is set by the _connect method (line 95) and used by the _query method (line 112). $month holds the selected month and is set by the setMonth method on line 16; $year holds the current year and is simply set to 2006. $cities contains an array of all the selected cities; it is set by the setCities method on line 20. Note that $citiesIn also contains a list of selected cities, this time formatted to be used in an SQL in clause.

Lines 1646 contain the two setter methods and several basic data access methods. The method setMonth (lines 1618) takes a single parameter, the integer value of the selected month, and sets it to $this->month. The setCities method (lines 2029) performs a similar action, only this time, it takes an array of cities. It also builds the citiesIn method. This method takes an array and turns it into a quoted comma-separated string. If the array had two values, one for Phoenix and one for Sturgis, it would equal 'PHOENIX, ARIZONA','STURGIS,MICHIGAN'.

The possibleCities method (lines 3138) returns an array that contains an entry for each city in the database. This array is used to build the interface for selecting cities. The list is built from the query "select distinct city from rise_set", which returns one row for each city in the rise_set table. The helper function _querySingleArray is used on line 37 to return the values from this query. The monthList function (lines 4047) creates an associative array containing 12 months; the key of the array contains the values 112, with their matching values being generated by the PHP date function. You can see sample output from these functions by running the SunRiseSetDemo.php example page. Listing 10-3 continues the SunRiseSet class.

Listing 10-3. SunRiseSet.class.php Continued

48    function monthsData() {
49      $sql = "select *
50        from
51          rise_set
52        where
53          `day` between
54          '{$this->year}-{$this->month}-1' and
55          '{$this->year}-{$this->month}-31'
56          and city in($this->citiesIn)
57        order by
58          `day`, city";
60      $data = $this->_queryAll($sql);
62      $ret = array();
63      $i = 0;
64      $currentDay = $data[0]['day'];
65      foreach($data as $row) {
66        if ($row['day'] != $currentDay) {
67          $currentDay = $row['day'];
68           $i++;
69        }
70        $key = array_search($row['city'],
71                      $this->cities);
72        $ret[$i][$key] = $row;
73      }
74      return $ret;
75 }

Lines 4876 define the monthsData method. This method queries the database and returns an array of data containing one month of rise and set data for the selected cities. The cities are chosen using the values set by setCities, and the month is chosen by the value set by setMonth along with the $this->year property. The SQL query is built on lines 4958. This query is then executed using the _queryAll helper method (line 60). This returns a multidimensional array, with the first level being the row index and the second level being the values of the row in an associative array. We then loop through the results (lines 6574), which builds an array one row per day, with subentries under that row for each city. Listing 10-4 shows an excerpt of that output.

Listing 10-4. Sample Output of monthsData()

[0] => Array
            [0] => Array
                    [city] => PHOENIX, ARIZONA
                    [day] => 2006-01-01
                    [rise] => 0733
                    [set] => 1732


            [1] => Array
                    [city] => STURGIS, MICHIGAN
                    [day] => 2006-01-01
                    [rise] => 0809
                    [set] => 1722

Listing 10-5. SunRiseSet.class.php Continued

77 function graphData($city) {
78      $sql = "select * from rise_set
79      where city = '".mysql_real_escape_string($city)."'
80      order by `day` ";
82      return $this->_queryAll($sql);
83    }
85    function minRise() {
86      $sql = "select
87                  min(rise) mr
88                  from rise_set
89                  where city in($this->citiesIn)
90              ";
91      $data = $this->_queryAll($sql);
92      return $data[0]['mr'];
93 }

Lines 7793 define two functions that are used by Graph.php to load its data. The Image_Graph graphing engine loads its data based on the concept of data sets, so instead of grabbing a combined array, like the month data case, we return the data one city at a time. The graphData method on lines 7783 accomplishes this process by building a query limited to the passed-in city and then returning all its results using the _queryAll helper function.

The minRise method defined on lines 8593 is used by the graphing code to set the bottom value on its Y axis. Because this minimum SunRise value needs to take into account all the cities being displayed, the query looks for the minimum rise value where the city is among the currently selected cities in the $this->citiesIn list. A single time value is returned from the function.

Listing 10-6. SunRiseSet.class.php Continued

95     function _connect() {
96       if ($this->_conn) {
97         return true;
98       }
99       global $dbHost, $dbUser, $dbPass, $dbName;
101      $this->_conn = mysql_connect($dbHost,
102                          $dbUser,$dbPass);
104      if (!$this->_conn) {
105        die(mysql_error());
106      }
108      mysql_select_db($dbName,$this->_conn);
109    }
112    function _query($sql) {
113      $this->_connect();
114      $res = mysql_query($sql,$this->_conn);
115      if (!$res) {
116        die(mysql_error($this->_conn));
117      }
118      return $res;
119    }
121    function _queryAll($sql) {
122      $res = $this->_query($sql);
124      $ret = array();
125      while($row = mysql_fetch_assoc($res)) {
126        $ret[] = $row;
127      }
128      return $ret;
129    }
131    function _querySingleArray($sql) {
132      $res = $this->_query($sql);
134      $ret = array();
135      while($row = mysql_fetch_row($res)) {
136        $ret[] = $row[0];
137      }
138      return $ret;
139    }
140  }

The rest of the class (lines 95140) defines the database connectivity and utility methods. They provide a basic wrapper around the MySql database functions. The _connect method on lines 95109 connects to the database. If there is a problem, it stops script execution and shows an error message. The _query method (lines 112119) executes an SQL query and does basic error handling. Lines 121129 define the _queryAll method, which uses _query to execute an SQL query and then loops over its results, grabbing an associative array for each row. The _querySingleArray method (lines 131138) is similar to _queryAll. The only difference is that instead of returning an associative array for each row; it uses the value of only the first column for each row.

10.2.2. Graph.php

The graph on the viewer is generated using a PEAR library called Image_Graph ( To run the examples locally, you need to install the library, which can be accomplished by running pear install Image_Graphalpha. I won't walk through all the graphing code, because it's not necessary to understand it to understand how the example works. The graph is generated by the Graph.php script, which will be used as the URL for an image on the HTML page in which it's displayed. The graph takes the GET parameter of cities and uses it to select which cities will appear on the graph. The graphing period is one year, meaning the graph uses 365 points per city. This large number of points causes much of the slowdown in the graphing processes, but dynamic image generation is usually a slow area in any Web site, no matter how much data it's working with. This slow speed is due to the complexity of the image generation process.

In this example, the slow processing point is this graph, but this slow processing could be replaced by any number of other problems. On another site, you may have some database queries that are slow and impossible to speed up, or you might have some other visualization with lots of processing overhead. In many cases, page loads can be slow simply due to the large amounts of information and the formatting of the HTML that is being used. In other cases, the page performs fine, but the user experience isn't great due to the constant reload process that browsing through data causes.

10.2.3. Standard.php

Standard.php generates the HTML that makes up our sun rise and set viewer. This page contains a form that is used to change which cities are displayed and which month of data the table shows. It also links in the graph and generates the data table. This page contains a minimal amount of PHP code, most of it being foreach loops to build the options for the form or the data in the table. This separation of the bulk of the logic from the building of the HTML is important because it will make the creation of an AJAX version much easier. Listing 10-7 presents the code.

Listing 10-7. Standard.php

1  <?php
2  // non-AJAX version of a sun rise/set viewer
4  // include data class
5  require_once 'SunRiseSet.class.php';
6  $data = new SunRiseSet();
8  // defaults
9  $month = 1;
10 $cities = array('PHOENIX, ARIZONA');
12 // load options
13 if (isset($_GET['month'])) {
14    $month = (int)$_GET['month'];
15 }
17 if (isset($_GET['cities'])) {
18    $cities = (array)$_GET['cities'];
19 }
21 // Set the selected options on the data class
22 $data->setMonth($month);
23 $data->setCities($cities);
25 // build the graph query string
26 $graphOptions = '';
27 foreach($cities as $city) {
28    $graphOptions .= "cities[]=$city&";
29 }
31 $action = htmlentities($_SERVER['PHP_SELF']);
32 $months = $data->monthList();
33 ?>
34 <html>
35 <head>
36    <title>Sun Rise and Set Viewer</title>
37 </head>
38 <body>
39 <div style="float:left; width: 610px">
40 <img src="Graph.php?<?php echo $graphOptions; ?>"
41    width="600" height="400" alt="sun rise and set">

43 <form action="<?php echo $action; ?>">
44 <fieldset>
45    <legend>Cities</legend>
46    <?php foreach($data->possibleCities()
47      as $city) { ?>
48    <label><?php echo $city; ?>
49      <input type="checkbox" name="cities[]"
50      value="<?php echo $city; ?>"
51      <?php if(in_array($city,$cities)) {
52        echo "CHECKED"; } ?>
53    </label>
55    <?php } ?>
56  </fieldset>
57  <label>View Month:
58  <select name="month">
59  <?php foreach($months as $key => $m) { ?>
60    <option value="<?php echo $key;?>"
61    <?php if($key == $month) {
62      echo 'SELECTED'; } ?>>
63      <?php echo $m; ?></option>
64  <?php } ?>
65  </select>
66  </label>
67  <input type="submit" value="Update View">
68 </form>
69 </div>

The first 33 lines of Standard.php do the basic PHP setup. On line 5, we require the SunRiseSet class that gives us access to the data in the database. On line 6, we create an instance of it, and then we start the process of setting its defaults. First, we set some default values to use throughout the page if nothing is passed in by the form (lines 910); then we check for month and cities being passed in by the form and, if so, overwrite our default values with those from the form. On lines 2223, we use those values and set the month and cities on the SunRiseSet instance. To finish up the setup portion, we format the $cities variable so that it can be passed in a query string (lines 2629), set the URL from the form to which to submit (line 31), and put our list of months into the variable $months so that it can be used later on.

Lines 3468 add the graph and the form to the page. These elements float to the left, so as long as you have a wide enough screen, you'll be able to see the data table next to them on the right. The graph is added to the page on lines 4041. We add $graphOptions to its query string, which lets us set the cities the graph will display. On lines 4368, we build the form that provides the viewing options for this page. The page submits using GET to the $action variable, which we set to the current page on line 31.

The first element in the form is a fieldset that contains a checkbox for each possible city (lines 4456). We get the list of options using the possibleCities method (line 46) and loop over the array; in each iteration, a checkbox element is created (lines 4953). Line 48 outputs the name of the city to use as a label, and line 50 outputs $city again (this time as the value of the checkbox). Lines 5152 finish up the output for the checkboxes, checking whether the city is currently selected; if it is, it outputs CHECKED.

The next section of the form builds the selected drop-down element for picking which month to view. The element is started on line 58 and is named month. Lines 5964 contain a loop that produces the drop-down's option elements. The values from those options are pulled out of the $months variable that was set on line 32. Lines 6162 check to see if the current element is selected and if it is, outputs SELECTED. The form finishes up with a Submit button on line 67.

Listing 10-8. Standard.php Continued

70  <div>
71  <b><?php echo $months[$month]; ?></b>
72  <table cellpadding="2" cellspacing="0"
73    border="1">
74  <thead>
75    <tr>
76      <th rowspan="2">Day</th>
77    <?php foreach($cities as $city) { ?>
78      <th colspan="2"><?php echo $city; ?></th>
79    <?php } ?>
80    </tr>
81    <tr>
82    <?php foreach($cities as $city) { ?>
83      <th>Rise</th>
84      <th>Set</th>
85    <?php } ?>
86    </tr>
87  </thead>
88  <tbody>
89  <?php foreach($data->monthsData() as $row) { ?>
90    <tr>
91      <td><?php echo $row[0]['day']; ?></td>
92    <?php foreach($row as $city) { ?>
93      <td><?php echo $city['rise']; ?></td>
94      <td><?php echo $city['set']; ?></td>
95    <?php } ?>
96    </tr>
97  <?php } ?>
98  </tbody>
99  </table>
101 </div>
102 </body>
103 </html>

The page finishes by producing the data table. On line 71, a label for the table is created, showing the currently selected month. Then on line 72, the actual table starts; the table is given some basic formatting, some cell padding, and a border of 1 pixel. Then we move on to its dynamic aspects. First, we create the header for the table (lines 7487). The header has two rows: The first displays the name of each selected city, and the second subdivides each city column into a rise and set column. We create the list of cities in the foreach loop on lines 7779, looping over the $cities variable, which contains our currently selected cities, and outputting each one with a colspan of 2. We then loop over the same variable again (lines 8285); this time, two cells are outputted: one for rise and one for set.

The table is finished up by a loop on lines 8997 that generates the table body. We get the data for this loop from the monthsData method (line 89); if you look back at the explanation of monthsData shown in Listing 10-3, you'll remember that it contains a subarray for each city. Thus, we start off a row by outputting a cell for the date (line 91). We then read the date from the first city's data, knowing that all the dates are the same. Then we loop over the $cities arrays, printing out rise and set times (lines 9394) for each. This completes the page, giving us a graph, a form, and a data table.

Previous Page
Next Page