We help IT Professionals succeed at work.

Best Way to to Execute functions and write to a database within a while loop

Ron1959
Ron1959 asked
on
I want to use the Google Geocode system to look up Lat/Long coordinates on addresses which are in a MySQL database and write them back to the same database.

I am looking for suggestions about the best approach.

The code I'm using to lookup and write back the coordinates is in two php files, one called by the other.  Since they must go to Google's server to do the lookup, there is some delay.

I have tried calling those php files from within the while loop used to step through the database, but it only writes the coordinates to the first record.  Perhaps I need to pause the while loop and make it wait for a return from the Google lookup?

<table>
  <?php do { ?> 
  <tr>
    <?php echo $row_rsContacts_List['First_Name']; ?></td>
    <?php echo $row_rsContacts_List['Last_Name']; ?></td>
    <?php echo $row_rsContacts_List['Address1']; ?></td>
    <?php echo $row_rsContacts_List['Address2']; ?></td>
    <?php echo $row_rsContacts_List['City']; ?></td>
    <?php echo $row_rsContacts_List['State']; ?></td>
    <?php echo $row_rsContacts_List['Zip']; ?></td>

    <?php  // Set address values to session variables to be used by the 
		   // files that cdo the Google Geocode lookup
				$_SESSION['MAP_Geocode'] = "Y";
				$_SESSION['MAP_Street'] = $row_rsContacts_List['Address1'];
				$_SESSION['MAP_City'] = $row_rsContacts_List['City'];
				$_SESSION['MAP_State'] = $row_rsContacts_List['State'];
				$_SESSION['MAP_Zip'] = $row_rsContacts_List['Zip'];
		// Open self-closing popup window load the files that do the Google Geocode lookup
		echo "<script type='text/javascript'>
			WindowName = window.open('../mapping/geocode.php', 'Geocode', 'width=400,height=200,scrollbars=yes');
			WindowName.moveTo(400,200);
			WindowName.focus();
			</script>";				
	?>
    <?php $rownum ++; ?>
  </tr>
  <?php } while ($row_rsContacts_List = mysql_fetch_assoc($rsContacts_List)); ?>
</table>

Open in new window


Solutions would be appreciated!


Comment
Watch Question

View Solutions Only

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
this is because in your loop, you overwrite the session data with the next rows data before the other page has actually processed.

so, you better "include" that other page's function, and use the function from there instead of opening a popup window.
Ron1959

Author

Commented:
The other pages are fairly involved and would be difficult to include.

Is there a good way to pause execution of the while loop until the other page completes?
Derokorian

Commented:
http://www.php.net/sleep

sleep(1000);

Tho I would have to agree with angellll about including the other page within the loop.
Derokorian
Commented:
the problem is your server probably has its own output buffering, so this echo might not open the popup until after you've run through the loop in its entirety. It would be better to not rely on the output to do initiate an action, but rather take the action before output if that makes sense.
Ron1959

Author

Commented:
Ok - I'll play with these ideas a little and get back to you.

Thanks!
Ray Paseur
Most Valuable Expert 2011
Top Expert 2016
Commented:
I want to use the Google Geocode system to look up Lat/Long coordinates on addresses which are in a MySQL database and write them back to the same database.

Be careful of the terms of service regarding the Google Geocoder.  I believe that you are required to use the data you retrieve with a free-to-the-public Google map.  See these links to be sure you are in compliance:
http://code.google.com/apis/maps/documentation/geocoding/
http://code.google.com/apis/maps/terms.html

Maybe I am missing something, but I do not see any role for sessions or self-closing windows or Javascript in this process.  It seems like what you want is an updated data base with the lat/lon pairs stored in the same rows as the addresses.

I would probably do something like this:  Select the address information.  Iterate over the results set with while().  With each row, call a class like the one in the code snippet.  It will work synchronously with your script.  When you have the data back from the call to the geocoder class, update the row.  The while() loop will access every row that came back from the query.

You can test this sample script on my server, here:
www.laprbass.com/RAY_class_SimpleGeoCoder.php

Here is a really good book that may help you get a better understanding of PHP (It will be MUCH BETTER than reading Dreamweaver code, which is some of the worst PHP code ever written!)
http://www.sitepoint.com/books/phpmysql4/

Best of luck with your project, ~Ray
<?php // RAY_class_SimpleGeoCoder.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO CALL THE YAHOO! AND GOOGLE GEOCODERS


// A NOMINAL DATA CLEAN-UP FUNCTION
function get_clean_text_string($s)
{
    return preg_replace('/[^A-Z 0-9_\.,\-#]/i', NULL, $s);
}


// A FREEFORM LOCATION STATEMENT IS OPTIONAL (BUT NOT USED HERE)
$location = $a = $c = $s = $z = NULL;

// THESE ARE THE GET-FORM VARIABLES
$vars = array( 'a', 'c', 's', 'z' );

// FILTER THE ARGUMENTS
if (!empty($_GET))
{
    foreach ($_GET as $k => $v)
    {
        if (!in_array($k, $vars)) continue;
        $$k = get_clean_text_string($v);
    }
    if ($a != NULL) { $location .= $a . ' '; }
    if ($c != NULL) { $location .= $c . ' '; }
    if ($s != NULL) { $location .= $s . ' '; }
    if ($z != NULL) { $location .= $z . ' '; }
    $location = trim($location);
}

// PREPARE THE FORM
$form = <<<ENDFORM
<doctype html>
<head>
<title>$a $c $s $z Yahoo/Google Geocoder Query</title>
</head>
<body>
<form method="get">
Addr: <input type="text" name="a" autocomplete="off" value="$a" />
City: <input type="text" name="c" autocomplete="off" value="$c" />
ST:   <input type="text" name="s" autocomplete="off" value="$s" size="2" />
Zip:  <input type="text" name="z" autocomplete="off" value="$z" size="8" />
<input type="submit" value="Go" />
</form>
</body>
</html>
ENDFORM;

if (!$location)
{
    echo $form;
    die();
}


// PREPARE THE GEOCODER
$y_demo          = new SimpleGeoCoder;
$y_demo->address = $a;
$y_demo->city    = $c;
$y_demo->state   = $s;
$y_demo->zip     = $z;

// TEST THE YAHOO! GEOCODER
$y_demo->geocodeYahoo();
echo "<pre>";
echo PHP_EOL;
echo "YAHOO! DATA FOR <strong>$location</strong>";
echo PHP_EOL;
print_r($y_demo);
echo "</pre>";



// PREPARE THE GEOCODER
$g_demo           = new SimpleGeoCoder;
$g_demo->address  = $a;
$g_demo->city     = $c;
$g_demo->state    = $s;
$g_demo->zip      = $z;

// TEST THE GOOGLE GEOCODER
$g_demo->geocodeGoogle();
echo "<pre>";
echo PHP_EOL;
echo "GOOGLE DATA FOR <strong>$location</strong>";
echo PHP_EOL;
print_r($g_demo);
echo "</pre>";


// ALL DONE
echo $form;
die();



// SIMPLE GEOCODER CLASS
class SimpleGeoCoder
{

    // DECLARE THE INPUT DATA
    public $location; // USE THIS FOR A FREEFORM QUERY, OR USE THESE PARTS
    public $address;
    public $city;
    public $state;
    public $zip;

    // DECLARE THE OUTPUT DATA
    public $latitude;
    public $longitude;
    public $precision;
    public $warning;
    public $geocoder;

    // DECLARE THE CONSTRUCTOR
    public function __construct()
    {
        $this->latitude  = 0.0;
        $this->longitude = 0.0;
        $this->precision = 0;   // HIGHER IS BETTER, BUT Y AND G USE DIFFERENT SCALES
        $this->warning   = '';
        $this->geocoder  = '';
    }

    // DECLARE THE YAHOO! VERSION OF THE WORKHORSE
    public function geocodeYahoo()
    {
        $this->geocoder = 'Yahoo!';
        $yahooUrl       = "http://where.yahooapis.com/geocode?gflags=Q&appid=";
        if (defined("YAHOO_API"))
        {
            $yahooUrl .= YAHOO_API;
        }

        // YOU CAN ASK FOR A FREEFORM QUERY
        if ($this->location != '')
        {
            $yahooUrl .= "&location=" . urlencode($this->location);
        }

        // YOU CAN ASK FOR INDIVIDUAL PIECES OF AN ADDRESS
        else
        {
            $loc = urlencode
            ( trim($this->address)
            . ', '
            . trim($this->city)
            . ', '
            . trim($this->state)
            . ' '
            . trim($this->zip)
            )
            ;
            $yahooUrl .= "&location=" . $loc;
        }

        // EXECUTE YAHOO GEOCODER QUERY SEE http://developer.yahoo.com/geo/placefinder/guide/requests.html
        // NOTE - USE ERROR SUPPRESSION OR IT WILL BARK OUT THE YAHOO API KEY - ON FAILURE RETURNS HTTP 400 BAD REQUEST
        if ($yfp = @fopen($yahooUrl, 'r'))
        {
            $yahooResponse = '';
            while (!feof($yfp)) { $yahooResponse .= fgets($yfp); }
            fclose($yfp);
        }
        else
        {
            return FALSE;
        }

        // EXAMINE THE RESULT
        if ($yahooResponse != '') // NOT EMPTY, WE GOT DATA
        {
            $ydata    = new SimpleXMLElement($yahooResponse);
            // echo "<pre>";var_dump($ydata);

            // CHECK FOR ANY ERROR MESSAGE, IF NONE, EXTRACT THE DATA POINTS
            $y_err    = (string)$ydata->Error;
            if ($y_err)
            {
                $this->warning   = $ydata->ErrorMessage;
                return FALSE;
            }
            else
            {
                $this->precision = (string)$ydata->Result->quality;
                $this->latitude  = (string)$ydata->Result->latitude;
                $this->longitude = (string)$ydata->Result->longitude;
                $this->address   = (string)$ydata->Result->line1;
                $this->city      = (string)$ydata->Result->city;
                $this->state     = (string)$ydata->Result->statecode;
                $this->zip       = (string)$ydata->Result->postal;

                $this->location
                = (string)$ydata->Result->line1
                . ', '
                . (string)$ydata->Result->line2
                . ' '
                . (string)$ydata->Result->line3
                ;
            }
        }

        // NO RESULT - SOMETHING IS SICK AT YAHOO
        else
        {
            return FALSE;
        }
        return TRUE;
    } // END function geocodeYahoo



    // DECLARE THE GOOGLE VERSION OF THE WORKHORSE
    public function geocodeGoogle()
    {
        $this->geocoder = 'Google';
        $googleUrl      = "http://maps.google.com/maps/geo?output=csv";
        $googleUrl      = "http://maps.googleapis.com/maps/api/geocode/xml?sensor=false";
        if (defined("GOOGLE_API"))
        {
            $googleUrl .= "&key=" . GOOGLE_API;
        }

        // YOU CAN ASK FOR A FREEFORM QUERY
        if ($this->location != '')
        {
            $googleUrl .= "&address=" . urlencode(trim($this->location));
        }

        // YOU CAN ASK FOR INDIVIDUAL PIECES OF AN ADDRESS
        else
        {
            $loc = urlencode
            ( trim($this->address)
            . ', '
            . trim($this->city)
            . ', '
            . trim($this->state)
            . ' '
            . trim($this->zip)
            )
            ;
            $googleUrl .= "&address=$loc";
        }

        // EXECUTE GOOGLE GEOCODER QUERY SEE http://code.google.com/apis/maps/documentation/geocoding/
        if ($gfp = @fopen($googleUrl, 'r'))
        {
            $googleResponse = '';
            while (!feof($gfp)) { $googleResponse .= fgets($gfp); }
            fclose($gfp);
        }
        else
        {
            return FALSE;
        }

        // EXTRACT THE DATA
        $gdata    = new SimpleXMLElement($googleResponse);
        // echo "<pre>";var_dump($gdata);

        if ($gdata->status != 'OK') return FALSE;

        // GEOCODE ACCURACY - ZIP CODE = 5, HIGHER NUMBERS ARE BETTER
        $this->location  = (string)$gdata->result->formatted_address;
        $this->precision = (string)$gdata->result->type;
        $this->latitude  = (string)$gdata->result->geometry->location->lat;
        $this->longitude = (string)$gdata->result->geometry->location->lng;
        return TRUE;
    } // END function geocodeGoogle

} // END class SimpleGeocoder

Open in new window

Ron1959

Author

Commented:
The answers are good so I'm adding points.
Ron1959

Author

Commented:
Thank you, all.  The answers each supplied pieces of the puzzle that now makes my understanding of the problem fairly complete.

I ended up simplifying my previous page for faster execution and included it in the calling page and executed it as a function.  Works like a charm.

Ray, you gave me more than I needed right now, but valuable details I may use later.  Thanks for all the detail.

This Experts Exchange is cool stuff!   Worth every penny of the subscription!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.