Sending Form Input to a PHP Script

It's not difficult to imagine scenarios in which the data to be mapped are just a subset of a larger database.  In fact, Lesson 7 demonstrated a county viewer app that allowed the user to view the counties within a selected state.  And you may have created an app of that sort for the Lesson 7 assignment.  However, the logic used in the county viewer example -- looping through all the records in the shapefile, but only adding overlays for the records associated with the correct state -- is a bit clunky.

Using a relational database provides a much more efficient solution to the problem.  PHP scripts can be written to accept input parameters and these input parameters (perhaps obtained from a form) could then be used to generate SQL Select statements that retrieve the subset of data needed to fill the user's request.

Let's return to the baseball stats database to illustrate this point. I've written a script called get_stats.php that expects to receive the name of a player as an input. To see the output of this script, click on the links below:

http://phpdb.aset.psu.edu/phpjed124/get_stats.php?first=Barry&last=Bonds
http://phpdb.aset.psu.edu/phpjed124/get_stats.php?first=Babe&last=Ruth

We saw this same kind of syntax at the beginning of the course when discussing Google Maps URLs. Note the following:
- The name of the script is followed by a question mark, then by a set of parameters and their values.
- Parameter names appear on the left side of the equals sign; their associated values appear on the right.
- Parameter/value pairs are separated from one another by the ampersand character.

When writing a script that accepts inputs such as get_stats.php, the input values are available through a global array called $_REQUEST. Getting an individual value from the array involves specifying the parameter name in brackets. For example:

$first = $_REQUEST["first"];

$last = $_REQUEST["last"];

These values can then be plugged into an SQL statement that retrieves the appropriate records. For example:
$sql = "SELECT PLAYERS.FIRST_NAME, PLAYERS.LAST_NAME, STATS.YEAR, STATS.HR, STATS.RBI
FROM PLAYERS INNER JOIN STATS ON PLAYERS.PLAYER_ID = STATS.PLAYER_ID
WHERE PLAYERS.FIRST_NAME='$first' AND PLAYERS.LAST_NAME='$last';";

Put into a mapping mashup context, the basic steps for constructing an application that allows the user to select a subset of data to map are as follows:

1. Build an HTML form containing the desired elements.
2. Write a PHP script that is prepared to accept input values entered through the form and retrieve the appropriate data from the database.
3. Using JavaScript and the DOM, obtain the user's form inputs.
4. Programmatically construct the URL of the PHP script that will retrieve the correct data by appending parameter/value pairs to the name of the script.
5. Insert that URL into a jQuery ajax() statement.
6. Map the data as covered earlier in the course, perhaps using different symbols for different categories of data.

Let's look at a simple mapping example that illustrates this process. First, here is a slightly modified version of the get_cities.php script that was discussed earlier (with modified code in bold):

https://phpdb.aset.psu.edu/phpjed124/get_cities.php

<?php

$city = $_REQUEST["city"];

$link = mysqli_connect("instructdb3.ait.psu.edu","inst_4","password","jed124");

$sql = "SELECT name, population, x, y FROM candidate_cities ";

if ($city) {
    $sql .= "WHERE name = '" .$city. "' ";
} 

$sql .= "ORDER BY name;";
                 
$result = mysqli_query($link,$sql);
if ($result != 0) {
    header("Content-type: text/xml");
    echo '<shapes>';
				
    $num_results = mysqli_num_rows($result);
    for ($i=0;$i<$num_results;$i++) {
        $row = mysqli_fetch_array($result);
	$name = $row['name'];
	$pop = $row['population'];
        $x = $row['x'];
        $y = $row['y'];
					
        echo '<pt name="' .$name. '" population="' .$pop. '" x="' .$x. '" y="' .$y. '" />';
     }
				
     echo '</shapes>';
} else {
     echo 'Problem with query!';
}
					
mysqli_close($link);
?>

The difference in this version of the script is that it's prepared to accept the name of a city if a city parameter is included in the URL. If a city parameter is included (as in https://phpdb.aset.psu.edu/phpjed124/get_cities.php?city=Nittanytown), then the original SQL statement is changed to include a WHERE clause that limits the results to just the specified city. If a city parameter is not included in the URL, then the script will retrieve the data for all the cities, just as before.

Next, have a look at this page that enables the user to select a city from a drop-down list to map:
https://phpdb.aset.psu.edu/phpjed124/candidate_cities_db.html

Here is a list of the important points to note while examining this page's source code:

• The page is set up similar to the shapefile-based example from Lesson 7, with the key being that the "Add to Map" button has its onclick attribute set to a function named process().
• The process() function gets the user's selected city, and then passes it to another function that handles the display of desired features (here that other function being called load() rather than render()).
• The load() function is defined to accept the name of a city to map. If an empty string is supplied for this parameter, then all of the cities will be retrieved and mapped.

Before leaving the topic of forms, there is one more concept that some of you may find useful for your own projects: how to dynamically populate a drop-down list with data from a database. We'll see how that can be done in the next part of the lesson.