Title: [Plugin: SimpleMap] Creating XML from SQL results
Last modified: August 19, 2016

---

# [Plugin: SimpleMap] Creating XML from SQL results

 *  Resolved [designodyssey](https://wordpress.org/support/users/designodyssey/)
 * (@designodyssey)
 * [16 years, 3 months ago](https://wordpress.org/support/topic/plugin-simplemap-creating-xml-from-sql-results/)
 * 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();
       ```
   

Viewing 1 replies (of 1 total)

 *  Thread Starter [designodyssey](https://wordpress.org/support/users/designodyssey/)
 * (@designodyssey)
 * [16 years, 3 months ago](https://wordpress.org/support/topic/plugin-simplemap-creating-xml-from-sql-results/#post-1323246)
 * 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));
       ```
   

Viewing 1 replies (of 1 total)

The topic ‘[Plugin: SimpleMap] Creating XML from SQL results’ is closed to new replies.

## Tags

 * [ajax](https://wordpress.org/support/topic-tag/ajax/)
 * [custom fields](https://wordpress.org/support/topic-tag/custom-fields/)
 * [xml](https://wordpress.org/support/topic-tag/xml/)

 * 1 reply
 * 1 participant
 * Last reply from: [designodyssey](https://wordpress.org/support/users/designodyssey/)
 * Last activity: [16 years, 3 months ago](https://wordpress.org/support/topic/plugin-simplemap-creating-xml-from-sql-results/#post-1323246)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics