WordPress.org

1. designodyssey
   Member
   Posted 2 years ago #

   Goal:
   I am trying to convert a store locator plugin from it's own table to using the posts/postmeta tables. Using JOINs, I believe I can filter posts (locations) based on distance. My problem is my recordset would only contain those meta_values added in the join. I need to retrieve all the relevant meta_values for a location (e.g. address, phone, state, city).

   How can I get a recordset to while ($row = mysql_fetch_assoc($result)) that has all the relevant key/values without 20 JOINS? If I did 20 JOINS wouldn't it degrade performance?

   Current code:
   

   $query = sprintf("
   SELECT * , ( 3959 * acos( cos( radians('%s') ) * cos(
   radians( latitude.meta_value ) ) * cos( radians(
   longitude.meta_value ) - radians('%s') ) + sin(
   radians('%s') ) * sin( radians( latitude.meta_value ) ) ) ) AS distance
   
   FROM $tf_posts
   LEFT JOIN $tf_postmeta AS latitude ON(
   $tf_posts.ID = latitude.post_id
   AND latitude.meta_key = 'lat'
   			)
   LEFT JOIN $tf_postmeta AS longitude ON(
   $tf_posts.ID = longitude.post_id
   AND longitude.meta_key = 'lng'
   			)
   WHERE $tf_posts.post_status = 'publish'
   HAVING distance < '%s'
   ORDER BY distance";

   Yes, I've looked at http://wordpress.org/support/topic/237079?replies=6
   http://wordpress.org/support/topic/161154?replies=10 and others.

2. designodyssey
   Member
   Posted 2 years ago #

   Haven't solved it yet, but wanted to help someone help me.

   Could I use something like the following in my while loop

   $address = get_post_custom_values('address', $row['ID']);
   $zip = get_post_custom_values('zip', $row['ID']);

   Found that idea at the link below, but because the plugin is so complex, I wanted some feedback before I try it.
   http://codex.wordpress.org/Function_Reference/get_post_custom_values

3. designodyssey
   Member
   Posted 2 years ago #

   I sorta solved it. The following query will get rows with the post_title, post_id and distance. However, I'm stuck trying to use a while loop and get_post_custom_values($key, $post_id); to try and get custom values for each key.

   SELECT wp_1_posts.post_title, wp_1_posts.ID,
   ( 3959 * acos(
   cos( radians( $lat ) ) *
   cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
   cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) - radians( $lng ) ) +
   sin( radians( $lat ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )
    ) ) AS distance
   FROM wp_1_posts
   LEFT JOIN wp_1_postmeta AS latitude ON ( wp_1_posts.ID = latitude.post_id
   AND latitude.meta_key = 'lat' )
   LEFT JOIN wp_1_postmeta AS longitude ON ( wp_1_posts.ID = longitude.post_id
   AND longitude.meta_key = 'lng' )
   WHERE wp_1_posts.post_status = 'publish'
   HAVING distance < $radius
   ORDER BY distance";

4. designodyssey
   Member
   Posted 2 years ago #

   solved it with an inelegant SQL. I'll worry about optimization later. Hope this helps someone.

   $query = sprintf("SELECT wp_1_posts.post_title, wp_1_posts.ID, a1.meta_value as address,
   			c.meta_value as city,
   			s.meta_value as state,
   			z.meta_value as zip,
   			latitude.meta_value as lat,
   			longitude.meta_value as lng,
   
   			( 3959 * acos(
   			cos( radians( '%s' ) ) *
   			cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
   			cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) - radians( '%s' ) ) +
   			sin( radians( '%s' ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )
   			 ) ) AS distance
   			FROM wp_1_postmeta as latitude, wp_1_postmeta as longitude,
   			wp_1_postmeta as a1, wp_1_postmeta as c, wp_1_postmeta as s, wp_1_postmeta as z, wp_1_posts
   
   WHERE 	(wp_1_posts.ID = latitude.post_id
   			AND latitude.meta_key = 'lat' )
   AND 	(wp_1_posts.ID = longitude.post_id
   			AND longitude.meta_key = 'lng' )
   AND 	(a1.post_id = latitude.post_id AND a1.meta_key = 'address')
   
   AND 	(c.post_id = latitude.post_id AND c.meta_key = 'city')
   
   AND 	(s.post_id = latitude.post_id AND s.meta_key = 'state')
   
   AND 	(z.post_id = latitude.post_id AND z.meta_key = 'zip')
   			HAVING distance < $radius
   			ORDER BY distance".$limittext,
   
   			mysql_real_escape_string($center_lat),
   			mysql_real_escape_string($center_lng),
   			mysql_real_escape_string($center_lat),
   			mysql_real_escape_string($radius));

5. daevisioninc
   Member
   Posted 1 year ago #

   Thank you for the help! This was exactly what i need , you saved my life. Also thanks for completing this thread with your solution , would have been a nightmare with out it.

6. thaDRAGON
   Member
   Posted 1 year ago #

   I'm trying to implement this but no luck :(

   Can't get it to calculate distances.

   Here's the tutorial for Creating a Store Locator with PHP, MySQL & Google Maps:
   http://code.google.com/apis/maps/articles/phpsqlsearch_v3.html

   but instead of having a different table, i'm using wp custom fields to contain the data.

   I'm hoping someone could show me how to do it.

Topic Closed

This topic has been closed to new replies.