Support » Plugins » Different query multiple custom fields from postmeta question

Viewing 5 replies - 1 through 5 (of 5 total)
  • 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

    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";

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

    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.

    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.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Different query multiple custom fields from postmeta question’ is closed to new replies.