Loading Multiple Locations - MySQL Examples¶

You can load data directly from an existing MySQL data source that contains your location data. The basic premise is to connect to the other MySQL server and "map" the data fields from the tables on that server to the Store Locator Plus tables.

If you would like assistance with getting data bulk loaded to your system please contact us for a consulting quote.

Remote MySQL Server¶

This is a command line program. We put this in the ./wp-content/plugins/data_loaders/ directory as load_data_from_ap.php and run it from the command line of the server with php ./load_data_from_ap.php.

<?php
/****************************************************************************
 ** file: load_data_from_ap.php
 **
 ** Load the data direct from the ap website into the store locator.
 ** Run this from the command line & enter the password with a -p option.
 **
 ** Usage:
 ** php ./load_data_from_ap_ap.php -p MyCoolRemoteDBPassword!
 **
 ** NOTE: THIS FILE IS NOT TO BE MERGED INTO THE PUBLIC RELEASE!!
 ***************************************************************************/

// Where does WordPress live for this install?
$WPROOT     = '/www/docroot/wordpress';

// Where are we reading our source data from? (defaults)
$SRC_DBHOST = 'notlocalhost.your-db-server-address.com';
$SRC_DBUSER = 'remote-db-username';
$SRC_DBNAME = 'remote-db-dbname';
$SRC_DBPASS = '';
$GM_APIKEY  = 'YOUR GOOGLE MAPS API KEY GOES HERE';
$GM_HOST    = 'maps.google.com';

// Read in the source data password from the command line args.
//
while(count($argv) > 0) {
    $arg = array_shift($argv);
    switch($arg) {
        case '-d':
            $SRC_DBNAME = array_shift($argv);
            break;
        case '-h':
            $SRC_DBHOST = array_shift($argv);
            break;
        case '-p':
            $SRC_DBPASS = array_shift($argv);
            break;
        case '-u':
            $SRC_DBUSER = array_shift($argv);
            break;
    }
}

// Set our WP environment variables so we can interace with the data
// 
require($WPROOT.'/wp-config.php');

// Connect To the Source Database
// 
$source_connection = mysql_connect($SRC_DBHOST, $SRC_DBUSER, $SRC_DBPASS, true);
if (!$source_connection) {
    die('Could not connect to '.$SRC_DBHOST.' as '.$SRC_DBUSER."\n");
}
$source_data = mysql_select_db($SRC_DBNAME,$source_connection);

// Connect To the Destination Database
//
$dest_connection = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD, true);
if (!$dest_connection) {
    die('Could not connect to '.DB_HOST.' as '.DB_USER."\n");
}
$dest_data = mysql_select_db(DB_NAME,$dest_connection);

// Loop through the source data and write to the destination
// 
$result = mysql_query('SELECT * from zipcode_example',$source_connection);
while ( $row = mysql_fetch_assoc($result) ) {

    // Get GEOCODE locations
    //
    $address =  $row['street'].', '.
                $row['city'].', '.$row['state'].' '.$row['zip'].' '.
                $row['country'];
    $global_position = get_geocode($address);
    $lat = $global_position[0];
    $long = $global_position[1];

    // Got 0,0 - go to next address
    //
    if (preg_match('/^E/',$lat)) {
        print "$address ($lat) [FAILED]\n";
        next;
    }

    // Create INSERT statement
    //  
    $in_sql = sprintf("INSERT INTO wp_papell_store_locator " .
                "(sl_store,sl_address,sl_city,sl_state,sl_zip,sl_country,sl_url".
                ",sl_latitude, sl_longitude) ".
                "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s')",
                mysql_real_escape_string($row['park_name']),
                mysql_real_escape_string($row['street']),
                mysql_real_escape_string($row['city']),
                mysql_real_escape_string($row['state']),
                mysql_real_escape_string($row['zip']),
                mysql_real_escape_string($row['country']),
                mysql_real_escape_string($row['web_site']),
                $lat,$long
                );

    // Success/Failure Messages
    print $row['park_name'] . ' at ' . $row['zip'];
    if (mysql_query($in_sql,$dest_connection)) {
        print '[OK]'."\n";
    } else {
        print '[FAILED]'."\n";
        print $in_sql;
        print "\n\n";
    }
}
mysql_free_result($result);

exit(0);

/**************************************
 ** function: get_geocode
 ** return the lat/long for a given address via Google Maps API
 **/
function get_geocode($address='') {
     if ($address == '') { 
         return array('Enull',0);
     }

     global $GM_APIKEY,$GM_HOST;
     $base_url = "http://$GM_HOST/maps/geo?output=csv&key=$GM_APIKEY".
                "&q=" . urlencode($address);

    if (extension_loaded("curl") && function_exists("curl_init")) {
            $cURL = curl_init();
            curl_setopt($cURL, CURLOPT_URL, $base_url);
            curl_setopt($cURL, CURLOPT_RETURNTRANSFER, 1);
            $csv = curl_exec($cURL);
            curl_close($cURL);  
    } else {
         $csv = file_get_contents($base_url) or die("url not loading");
    }

    $csvSplit = split(',', $csv);

    // successful geocode
    if (strcmp($csvSplit[0], "200") == 0) {
      $geocode_pending = false;
      return array($csvSplit[2],$csvSplit[3]);

    // sent geocodes too fast
    } else if (strcmp($csvSplit[0], "620") == 0) {
        print "$address - requests are too quick. [FAILED]\n";
        return array('E'.$csvSplit[0],0);

    // Invalid Address
    } else if (strcmp($csvSplit[0], "602") == 0) {
        print "$address - invalid address. [FAILED]\n";
        return array('E'.$csvSplit[0],0);

    // failure to geocode
    } else {
        print "$address - unknown error code ($csv[0]). [FAILED]\n";
        print "\n$base_url\n";
        return array('E'.$csvSplit[0],0);
    }    
 }