WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Different query multiple custom fields from postmeta question (6 posts)

  1. designodyssey
    Member
    Posted 4 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 4 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 4 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 4 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 3 years 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 3 years 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.

About this Topic