WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] [Plugin: SimpleMap] Creating XML from SQL results (2 posts)

  1. designodyssey
    Member
    Posted 4 years ago #

    Generic question for anyone, even if you don't use this great plugin. I just decided I wanted to use the post/postmeta tables to get some added functionality only available to posts instead of the default tables with the plugin.

    I've got a query that works in PHPMyAdmin, but it doesn't seem to be creating the necessary XML for the mapping process through Google.

    Author's SQL

    $query = sprintf("SELECT name, address, address2, city, state, zip, country, lat, lng, phone, fax, url, description, category, tags, special, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM $table".$category_text."HAVING distance < '%s' 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));

    My alternate that pulls from posts/postmeta and works in PHPMyAdmin when I replace the variables with numbers

    $query = "SELECT wp_1_posts.post_title, wp_1_posts.ID,
    ( 3959 * acos(
    cos( radians( $center_lat ) ) *
    cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
    cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6   ) ) ) - radians( $center_lng ) ) +
    sin( radians( $center_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 < 50
    ORDER BY distance".$limittext;

    The author's code below, creates and XML document later read by javascript to output markers on a Google map. For each line, I commented out her "stripslashes . . ."and replaced with a call to "get_post_custom_values()" function to try and retrieve the same data during the while loop.

    No luck with my solution. I think the query's solid since it's tested in PHPMyAdmin, so I think it's the XML creation. Please help.
    If inclined the author's code is in create-xml.php of the SimpleMap plugin in the repository.

    header("Content-type: text/xml");
    
    // Iterate through the rows, adding XML nodes for each
    while ($row = mysql_fetch_assoc($result)) {
    
    $node = $dom->createElement("marker", nl2br(stripslashes($row['post_title'])));
    $newnode = $parnode->appendChild($node);
    
    $newnode->setAttribute("name", $row['post_title']);  //stripslashes($row['name']));
    
    $newnode->setAttribute("address", get_post_custom_values('address', $row['ID'])); //stripslashes($row['address']));
    
    $newnode->setAttribute("address2", get_post_custom_values('address2', $row['ID'])); //stripslashes($row['address2']));
    
    [. . . for additional nodes]
    
    }
    echo $dom->saveXML();
  2. designodyssey
    Member
    Posted 4 years ago #

    Solved it with an elegant sql:

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

Topic Closed

This topic has been closed to new replies.

About this Topic