Yes, and it outputted it incorrectly as well. It returned the same list as I was getting through Ditto, meaning that the problem must lie in the query, which currently looks like this:
/**
* Get a list of our stores, sorted by distance to this postcode
*
*/
function get_stores_list($postcode) {
//If it's a UK postcode then format correctly
$postcode = $this->checkPostcode($postcode);
$latlng = $this->get_lat_long($postcode);
if(!$latlng) { //Unrecognised postcode
return false;
}
$latitude = $latlng['lat'];
$longitude = $latlng['lng'];
print_r($latlng); //for debugging
$parents='418';
global $modx;
$query = "SELECT distinct
sc.pagetitle,
sc.content,
sc.id,
(((acos(sin((51.4926591*pi()/180)) * sin((lat_tvcv.value*pi()/180))
+cos((51.4926591*pi()/180)) * cos((lat_tvcv.value*pi()/180)) * cos(((-0.0542387- lng_tvcv.value)
*pi()/180))))*180/pi())*60*1.1515)
as distance
FROM (`modx_site_content` AS sc,
`modx_site_tmplvars` AS lat_tv ,
`modx_site_tmplvars` AS lng_tv)
LEFT JOIN `modx_site_tmplvar_contentvalues` AS lat_tvcv
ON lat_tvcv.contentid = sc.id AND lat_tvcv.tmplvarid = lat_tv.id
LEFT JOIN `modx_site_tmplvar_contentvalues` AS lng_tvcv
ON lng_tvcv.contentid = sc.id AND lng_tvcv.tmplvarid = lng_tv.id
WHERE sc.`parent` IN (".$parents.") AND sc.`published`= '1' AND sc.`deleted`= '0'
AND lat_tv.name = 'latitude'
AND lng_tv.name = 'longitude'
ORDER BY distance ASC
LIMIT 0,50
";
$rs=$modx->db->query($query);
$stores=$modx->db->makeArray($rs);
print_r($stores); //for debugging
return $stores;
}
The lat and lang values returned from Google geocoding do change, based on the input, but the list of stores does not. I think it may have something to do with the custom_db class. Below is the whole script in its entirity so that you can see what I mean.
<?php
/*==============================================================================
Application: Easy Postcode Finder
Author: Leon Chevalier
Version: V1.0
Date: 16th December 2007
URL: http://aciddrop.com/2007/12/17/quick-easy-and-free-nearest-store-postcodezip-finder/
------------------------------------------------------------------------------*/
/**
* The curl class
*/
class curl {
/**
* Constructor
*/
function curl() {
}
function init_curl($ch,$url,$postfields=null,$follow=null,$cookie=null,$referer=null) {
// Set url
curl_setopt($ch, CURLOPT_URL, $url);
// Enable Post
if($postfields) {
curl_setopt ($ch, CURLOPT_POST, 1);
curl_setopt ($ch, CURLOPT_POSTFIELDS, $postfields);
}
if($follow) {
curl_setopt ($ch, CURLOPT_FOLLOWLOCATION, 1 );
}
if($referer) {
curl_setopt($ch, CURLOPT_REFERER, $referer);
}
//Enable SSL
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)');
//Return results as string
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
return $ch;
} // end function
/*
Grabs a page
*/
function get_page($options) {
//Set options
foreach($options AS $key=>$value) {
$$key = $value;
}
$ch = curl_init();
$ch = $this->init_curl($ch,$url,$postfields,$follow,$cookie);
$page = curl_exec($ch);
curl_close($ch);
return $page;
}
} // end class
/**
* A simple wrapper for db functions
*
*/
class db_custom {
/**
* Constructor
*
* A simple wrapper for database functions
*
*/
function db_custom() {
// database configuration
$host = "myhost";
$user = "user";
$pass = "pass";
$db = "mydb";
// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
}
###############################################
## Updates an array of fields and values
## and return the resulting ID
###############################################
function quick_update($database,$fields,$values,$wherearray) {
//Variables
$num_array = count($values);
//Format NULL
$values = str_replace("'NULL'","NULL",$values);
//Write query
$query = "UPDATE `$database` SET ";
foreach ($fields AS $key=>$value) {
$count++;
$query .= " $fields[$key] = '$values[$key]'";
if ($count <> $num_array) { $query .= ","; }
}
//Create where
foreach ($wherearray AS $key=>$value) {
$counterv++;
$query_chk .= "$key = '" . trim($value) . "'";
if ($counterv != count($wherearray)) {
$query_chk .= " AND ";
}
}
$query .= " WHERE $query_chk";
$query = str_replace("'`","",$query);
$query = str_replace("`'","",$query);
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$rows = mysql_affected_rows();
return $rows;
}
######################################################
# Execute row
# runs query and gets row back
######################################################
function executeRow($query__ER,$type=null) {
$result__ER = mysql_query($query__ER) or die ("Error in query: $query__ER. " . mysql_error());
if($result__ER != 1) {
if($type!="array") {
$row__ER = mysql_fetch_object($result__ER);
} else {
$row__ER = mysql_fetch_assoc($result__ER);
}
return $row__ER;
}
}
######################################################
# Connects to the database and returns the
# results in an array
######################################################
function executeQuery($query,$func=null,$type="") {
//Get the table name from the query
preg_match("/SELECT(.*)FROM( )([A-z_]+)/i",$query,$matches);
$table_name = $matches[3];
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$rows = mysql_num_rows($result);
$columns = mysql_fetch_assoc($result);
if ($rows > 0) { // Only proceed if we have a result
mysql_data_seek($result,0);
while ($row=mysql_fetch_array($result)) {
foreach ($columns As $key=>$value) {
//Run any extra functions that have been sent over
if(is_array($func)) {
foreach ($func AS $Fkey=>$Fvalue) {
$row[$key] = $this->$Fvalue($row[$key],$key,$table_name);
} // end FE
} // end IF
if($type == "object") {
//echo $key . " " . $row[$key] . "\n";
$tmp->$key = $row[$key];
} else {
$tmp[$key] = $row[$key];
}
}// end for each
$results[] = $tmp;
unset($tmp);
} //end while
$final_result['result'] = $results;
$final_result['rows'] = mysql_num_rows($result);
} else {
$final_result['rows'] = 0;
}// end if
return $final_result;
} // end function
}
/**
* A postcode finder class
*
*/
class postcode_finder {
/**
* Constructor
*
*/
function postcode_finder($array) {
if(is_array($array)) {
foreach($array AS $key=>$value) {
$this->$key = $value;
}
}
//DO stuff
}
/**
* Setup database tables and insert lat longs
*
*/
function setup() {
// $this->create_store_table();
// $this->insert_example_stores();
$this->update_lat_lngs();
}
/**
/**
* Update lat lngs
*
*/
function update_lat_lngs() {
//Get the list of stores
$query = "SELECT * FROM markers
WHERE `lat` = ''
LIMIT 0,10";
$stores = $this->db->executeQuery($query);
$stores = $stores['result'];
//Run through stores and get lat / lng
foreach($stores AS $store) {
$latlng = $this->get_lat_long($store['postcode']);
//Update store with its lat lng
$this->db->quick_update("markers",
array('lat','lng'),
array($latlng['lat'],$latlng['lng']),
array('id'=>$store['id'])
);
}
}
/**
* Returns a lat / long of a given postcode
*
*/
function get_lat_long($postcode,$domain=null) {
if(!$domain) {
$domain = "co.uk";
}
$url = "http://maps.google.co.uk/maps/geo?q=" . urlencode($postcode) . "&output=json&key=ABQIAAAAdN8m5F_H4c8QnCWINywTCRQismpbGqJLdUil0k2vBhWw9rjFrhQVLNLfqmHZNGHxY1tlASp_Wx06bQ";
$json = $this->curl->get_page(array("url"=>$url));
$store_data = json_decode(str_replace(""","\"",htmlentities($json))); //Take care of accents
$lng = $store_data->Placemark[0]->Point->coordinates[0];
$lat = $store_data->Placemark[0]->Point->coordinates[1];
//Return
if($lng && $lat) {
return array('lat'=>$lat,
'lng'=>$lng
);
} else {
return false;
}
}
/**
* Get a list of our stores, sorted by distance to this postcode
*
*/
function get_stores_list($postcode) {
//If it's a UK postcode then format correctly
$postcode = $this->checkPostcode($postcode);
$latlng = $this->get_lat_long($postcode);
if(!$latlng) { //Unrecognised postcode
return false;
}
$latitude = $latlng['lat'];
$longitude = $latlng['lng'];
print_r($latlng);
$parents='418';
global $modx;
$query = "SELECT distinct
sc.pagetitle,
sc.content,
sc.id,
(((acos(sin((51.4926591*pi()/180)) * sin((lat_tvcv.value*pi()/180))
+cos((51.4926591*pi()/180)) * cos((lat_tvcv.value*pi()/180)) * cos(((-0.0542387- lng_tvcv.value)
*pi()/180))))*180/pi())*60*1.1515)
as distance
FROM (`modx_site_content` AS sc,
`modx_site_tmplvars` AS lat_tv ,
`modx_site_tmplvars` AS lng_tv)
LEFT JOIN `modx_site_tmplvar_contentvalues` AS lat_tvcv
ON lat_tvcv.contentid = sc.id AND lat_tvcv.tmplvarid = lat_tv.id
LEFT JOIN `modx_site_tmplvar_contentvalues` AS lng_tvcv
ON lng_tvcv.contentid = sc.id AND lng_tvcv.tmplvarid = lng_tv.id
WHERE sc.`parent` IN (".$parents.") AND sc.`published`= '1' AND sc.`deleted`= '0'
AND lat_tv.name = 'latitude'
AND lng_tv.name = 'longitude'
ORDER BY distance ASC
LIMIT 0,50
";
$rs=$modx->db->query($query);
$stores=$modx->db->makeArray($rs);
print_r($stores);
return $stores;
}
/**
* Checks whether supplied postcode is a valid UK postcode
*/
function checkPostcode($toCheck) {
$orig = $toCheck;
// Permitted letters depend upon their position in the postcode.
$alpha1 = "[abcdefghijklmnoprstuwyz]"; // Character 1
$alpha2 = "[abcdefghklmnopqrstuvwxy]"; // Character 2
$alpha3 = "[abcdefghjkstuw]"; // Character 3
$alpha4 = "[abehmnprvwxy]"; // Character 4
$alpha5 = "[abdefghjlnpqrstuwxyz]"; // Character 5
// Expression for postcodes: AN NAA, ANN NAA, AAN NAA, and AANN NAA
$pcexp[0] = '^('.$alpha1.'{1}'.$alpha2.'{0,1}[0-9]{1,2})([0-9]{1}'.$alpha5.'{2})$';
// Expression for postcodes: ANA NAA
$pcexp[1] = '^('.$alpha1.'{1}[0-9]{1}'.$alpha3.'{1})([0-9]{1}'.$alpha5.'{2})$';
// Expression for postcodes: AANA NAA
$pcexp[2] = '^('.$alpha1.'{1}'.$alpha2.'[0-9]{1}'.$alpha4.')([0-9]{1}'.$alpha5.'{2})$';
// Exception for the special postcode GIR 0AA
$pcexp[3] = '^(gir)(0aa)$';
// Standard BFPO numbers
$pcexp[4] = '^(bfpo)([0-9]{1,4})$';
// c/o BFPO numbers
$pcexp[5] = '^(bfpo)(c\/o[0-9]{1,3})$';
// Load up the string to check, converting into lowercase and removing spaces
$postcode = strtolower($toCheck);
$postcode = str_replace (' ', '', $postcode);
// Assume we are not going to find a valid postcode
$valid = false;
// Check the string against the six types of postcodes
foreach ($pcexp as $regexp) {
if (ereg($regexp,$postcode, $matches)) {
// Load new postcode back into the form element
$toCheck = strtoupper ($matches[1] . ' ' . $matches [2]);
// Take account of the special BFPO c/o format
$toCheck = ereg_replace ('C\/O', 'c/o ', $toCheck);
// Remember that we have found that the code is valid and break from loop
$valid = true;
break;
}
}
// Return with the reformatted valid postcode in uppercase if the postcode was
// valid
if ($valid){
return $toCheck;
} else {
$this->non_standard_postcode = true;
return $orig;
};
}
}
//If we have a post
if($_GET['postcode']) {
//Start database class
$db = new db_custom();
$curl = new curl();
$finder = new postcode_finder(array('db'=>$db,
'curl'=>$curl));
$stores = $finder->get_stores_list($_GET['postcode']);
}
if($_GET['fill']) {
$db = new db_custom();
$curl = new curl();
$finder = new postcode_finder(array('db'=>$db,
'curl'=>$curl));
$finder->setup();
}
?>
<form action="" method="GET">
<fieldset>
<legend>Enter your postcode or ZIP</legend>
<input type="text" name="postcode" value="<?= $_GET['postcode'] ?>">
<input type="submit" value="GO!">
</fieldset>
</form>
<? if ($_GET['fill']) { ?>
<h2 style="background-color:padding:2px">Database filled</h2>
<? } ?>
<? if ($_GET['postcode']) { ?>
<? if ($stores) { ?>
<h2 style="background-color:padding:2px">The following centres were found near to you</h2>
<?
$docids=array();
foreach($stores AS $store) {
$modx->setPlaceholder($store['id'].'.distance',number_format($store['distance'],2));
$docids[]=$store['id'];
}
$params=array();
$params['documents']=implode(',',$docids);
$params['tpl']='listtpl';
$params['extenders']='customsort';
return $modx->runSnippet('Ditto',$params);
} else { ?>
<h2 style="background-color:padding:2px">That postcode was not recognised</h2>
<? } ?>
<? } ?>
</div>
</div>
</body>
</html>
?>
I would love to be able to get rid of the custom_db class altogether in favour of the modx DBAPI. I can’t help but think that there must be a simple way to adapt this script completely for modx.
What do you think?
