Some MySQL Jiggery-Pokery for a Store Finder

145 Posts

digitalime Reply #1, 16 years, 11 months ago

Hello all,

I am currently working on a storefinder which returns the nearest store to you based on your posetcode. The problem is that I have a custom table for all the latitude and longitude points, and I would much prefer to have them as template variables (with each store represented by a document in modx).

Currently my "markers" table looks like this:

ID DocId Address lat lng
1 105 Some add 12.125 -3.215
1 109 Another add 28.525 -34215

And so on.

My SQL query to get the nearest store is:

$query = "SELECT *,
						(((acos(sin((".$latitude."*pi()/180)) * sin((`lat`*pi()/180))
						+cos((".$latitude."*pi()/180)) * cos((`lat`*pi()/180)) 
						* cos(((".$longitude."- `lng`)*pi()/180))))*180/pi())*60*1.1515) 
						as distance 
							FROM `markers`
							ORDER BY distance ASC
							LIMIT 0,50
					";
			$stores = $this->db->executeQuery($query);
			$stores = $stores['result'];			
			
			return $stores;

Is there a way to rework this code a bit so that it manages to reference the modx_site_tmplvar_contentvalues table instead, taking into account the differing structure.

Thanks for any help.

5,888 Posts

Bruno17 Reply #2, 16 years, 11 months ago

Didn’t test it, maybe this can do it:

SELECT distinct 
sc.pagetitle , 
sc.content , 
(((acos(sin((".$latitude."*pi()/180)) * sin((lat_tvcv.`value`*pi()/180))
+cos((".$latitude."*pi()/180)) * cos((lat_tvcv.`value`*pi()/180)) 
* cos(((".$longitude."- 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 (".$parent.") AND sc.`published`= '1' AND sc.`deleted`= '0'  
AND lat_tv.name = 'lat' 
AND lng_tv.name = 'lng' 
ORDER BY distance ASC
LIMIT 0,50

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #3, 16 years, 11 months ago

I get a parse error

Parse error: syntax error, unexpected T_LNUMBER in /var/www/web/manager/includes/document.parser.class.inc.php(770) : eval()'d code on line 309

In the code, line 309 refers to the line

WHERE sc.`parent` IN (".$parent.") AND sc.`published`= "1" AND sc.`deleted`= "0"

5,888 Posts

Bruno17 Reply #4, 16 years, 11 months ago

Quote from: danielw at Nov 10, 2009, 07:52 AM

WHERE sc.`parent` IN (".$parent.") AND sc.`published`= "1" AND sc.`deleted`= "0"

try this:

WHERE sc.`parent` IN (".$parent.") AND sc.`published`= '1' AND sc.`deleted`= '0'

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #5, 16 years, 11 months ago

OK. I think we are getting closer. I now get a different error:

Error in query: SELECT distinct sc.pagetitle , sc.content , (((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 () 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 . You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND sc.`published`= '1' AND sc.`deleted`= '0' AND lat_tv.name = 'latitude' ' at line 16

Note that in the TV table the names of the TVs are "latitude" and "longitude". I don’t know if I have to specify that in the query other than in

lat_tv.name = 'latitude'

and

lng_tv.name = 'longitude'

5,888 Posts

Bruno17 Reply #6, 16 years, 11 months ago

and without these backticks?

(((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)

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #7, 16 years, 11 months ago

Same error...but this time on line 14.

5,888 Posts

Bruno17 Reply #8, 16 years, 11 months ago

Hmm.. did a quick test with this code. Seems to work, I used two tvs named bild and head for my test.
can you post your code again ,what you have now?

SELECT distinct 
sc.pagetitle , 
sc.content ,
sc.id,
(((acos(sin((360*pi()/180)) * sin((lat_tvcv.`value`*pi()/180))
+cos((360*pi()/180)) * cos((lat_tvcv.`value`*pi()/180)) 
* cos(((30- 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.`id` IN (44,48) AND sc.`published`= 1 AND sc.`deleted`= 0  
AND lat_tv.name = 'bild' 
AND lng_tv.name = 'head' 
ORDER BY distance ASC
LIMIT 0,50

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #9, 16 years, 11 months ago

This is the complete section as it stands now:

	* 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);
		
			$query = "SELECT distinct 
sc.pagetitle , 
sc.content , 
(((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 (".$parent.") 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
					";
			$stores = $this->db->executeQuery($query);
			$stores = $stores['result'];			
			
			return $stores;
	
	
	}

Also, I am usingh a custom function to connect to the DB. I would love to be able to do this using $modx->db->query and I have been looking though the DBAPI at length, but I have not managed to make it work. Any ideas.

PS. Really appreicate your help.

5,888 Posts

Bruno17 Reply #10, 16 years, 11 months ago

$parent is not defined in your code. you have to define with

$parent='yourfolderwhereyouhaveyourstores';

If you run your code as a modx-snippet the db-connection is made by modx and you can use

$rs=$modx->db->query($query);
$stores=$modx->db->makeArray($rs);

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!