EvoViz: HTML checkboxes, Javascript and PHP

Tuesday 2 December 2014

HTML checkboxes, Javascript and PHP

So you want to select one or more HTML checkboxes and then use Javascript call a PHP program that returns data from a database usingan SQL query that OR's the checked items?

In this example, data on London Tube Stations in one or more zones is extracted from a MySQL database when any of the check boxes for zones is toggled.

HTML

First we need to write the HTML to display a check box for each zone. When the user checks, or unchecks, the getstations() Javascript function fires. This program posts an HTTP get request to stations.php, which returns information on stations. The results are written to an HTML div. For brevity only three zones are coded.

 <html>  
      <head>  
      <title>Checkbox Example</title>  
      <script type="text/javascript" src="stations.js"></script>  
      <script type="text/javascript" src="utils.js"></script>  
      </head>  
      <body>  
           <h1>Display Stations in Zones</h1>  
           <div>  
                <input type="checkbox" name="zone" value="1" onclick="getstations();"> Zone 1<br>  
                <input type="checkbox" name="zone" value="2" onclick="getstations();"> Zone 2<br>  
                <input type="checkbox" name="zone" value="3" onclick="getstations();"> Zone 3<br>  
                </br>  
           </div>  
           <h2> Stations</h2>  
           <div id="stations">  
           </div>  
      </body>  
 </html>

PHP

We shall now write a PHP program that returns an XML file containing data on the stations. Data is encoded as attributes within station tags.

 <?PHP  
   // Make a MySQL Connection  
   // EDIT FOR YOUR USER AND PASSWORD  
   $connection = mysql_connect("localhost", "root", "");  
   if (!$connection) {  
    die('Not connected : ' . mysql_error());  
   }  
   // Use the kyr_db DB  
   $db_selected = mysql_select_db("kyr_db");  
   if (!$db_selected) {  
    die ('Can\'t use db : ' . mysql_error());  
   }  
   // get the zone HTTP parameter  
   $zone_list = $_GET["zones"];  
   // Explode comma separated list of zones  
   $zones = explode(",",$zone_list);  
   // Build SQL Query  
   // The query needs an OR operator inserted for the second and subsequent clauses.  
   $query = "SELECT * FROM kyr_london_tube_stations WHERE";  
   $or = 0;  
   foreach ($zones as $zone) {  
           if ($or == 0) {  
                $query = $query . " zone = " . $zone;  
                $or = 1;  
           } else {  
           $query = $query . " OR zone = " . $zone;  
           }       
   }  
   // Retrieve data from the stations table  
   $result = mysql_query($query);  
   if (!$result) {  
    die('Invalid query: ' . mysql_error());  
   }  
   // Set the content type, then write the XML file  
  header ("Content-Type:text/xml");  
   // Open shapes tag  
   echo '<stations>';  
   // Loop through results extracting values   
   while ($row = mysql_fetch_array($result)) {  
           $name = htmlentities($row['NAME']);  
           $zone = htmlentities($row['ZONE']);;  
           echo '<station name="' . $name . '" zone="' . $zone . '"/>';   
   }  
   // Close shapes tag  
  echo '</stations>';  
   // Close database connection  
   mysql_close($connection);  
 ?>

Javascript

Finally, need to write the getstations() Javascript function that will make an AJAX request to the MySQL database. This function calls the downloadUrl() function in utils.js to undertake the XmlHttpRequest.

 function getstations() {  
       var zones = document.getElementsByName("zone");  
      var zone_ids = [];  
      var text = "";  
      // Clear text in div  
      document.getElementById("stations").innerHTML = text  
      for (var i = 0; i < zones.length; i++) {  
           if (zones[i].checked) {  
                zone_ids.push(zones[i].value);  
           }  
      }   
      if (zone_ids.length > 0) {  
           var ids_list = zone_ids.toString();  
           url = "stations.php?zones=" + ids_list;  
           downloadUrl(url, function(data) {  
                var rows = data.documentElement.getElementsByTagName("station");  
                if (rows.length > 0) {  
                     for (var i = 0; i < rows.length; i++) {  
                          text = text + rows[i].getAttribute("name") + " " + rows[i].getAttribute("zone") + "</br>";  
                     }  
                }  
                // Write text to div  
                document.getElementById("stations").innerHTML = text;  
           });  
      }  
 }