Title: sql query
Last modified: October 25, 2023

---

# sql query

 *  Resolved [creativ3y3](https://wordpress.org/support/users/creativ3y3/)
 * (@creativ3y3)
 * [2 years, 7 months ago](https://wordpress.org/support/topic/sql-query-9/)
 * I am trying to find the nearest shop within a custom post type using Lat / Long
   that lives in the custom post meta. I can manage to get it to work, but with 
   some missing posts. In particular some of the data doesn’t exist on all posts
   such as “address2”. If address 2 is missing it fails to bring back all the records
   within the 50mile criteria. How can i get it to bring back all the data even 
   if “address2” is missing for one of the entries. Here is where it is at:
 *     ```wp-block-code
       function my_nearest_shop() {
   
           global $wpdb;
           $distance = "50 miles";
           $lat =  "37.831313606716186";
           $lng = "-122.038259135056737";
   
           $earth_radius = 3959;
   
       	$sql = $wpdb->prepare( "
       		SELECT DISTINCT
       			p.ID,
       			p.post_title,
       			latitude.meta_value as locLat,
       			longitude.meta_value as locLong,
                   address.meta_value as address,
                   address2.meta_value as address2,
                   city.meta_value as city,
                   state.meta_value as state,
                   zip.meta_value as zip,
                   country.meta_value as country,
                   phone.meta_value as phone,
       			( %d * 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 $wpdb->posts p
       		LEFT JOIN $wpdb->postmeta latitude ON p.ID = latitude.post_id
       		LEFT JOIN $wpdb->postmeta longitude ON p.ID = longitude.post_id
               LEFT JOIN $wpdb->postmeta address ON p.ID = address.post_id
               LEFT JOIN $wpdb->postmeta address2 ON p.ID = address2.post_id
               LEFT JOIN $wpdb->postmeta city ON p.ID = city.post_id
               LEFT JOIN $wpdb->postmeta state ON p.ID = state.post_id
       		LEFT JOIN $wpdb->postmeta zip ON p.ID = zip.post_id
               LEFT JOIN $wpdb->postmeta country ON p.ID = country.post_id
               LEFT JOIN $wpdb->postmeta phone ON p.ID = phone.post_id
               WHERE 1 = 1
       		AND p.post_type = 'wp_shops'
       		AND p.post_status = 'publish'
       		AND latitude.meta_key = 'wpshops_lat'
       		AND longitude.meta_key = 'wpshops_lng'
               AND address.meta_key = 'wpshops_address'
               AND address2.meta_key = 'wpshops_address2'
               AND city.meta_key = 'wpshops_city'
               AND state.meta_key = 'wpshops_state'
               AND zip.meta_key = 'wpshops_zip'
               AND country.meta_key = 'wpshops_country'
               AND phone.meta_key = 'wpshops_phone'
       		HAVING distance < %s
       		ORDER BY distance ASC",
       		$earth_radius,
       		$lat,
       		$lng,
       		$lat,
       		$distance
       );
   
       $nearbyLocations = $wpdb->get_results( $sql );
   
       if ( $nearbyLocations ) {
       return $nearbyLocations;
       }
       else{
           return "no results";
       }
       ```
   
 * 
   Any help appreciated.

Viewing 3 replies - 1 through 3 (of 3 total)

 *  [corrinarusso](https://wordpress.org/support/users/corrinarusso/)
 * (@corrinarusso)
 * [2 years, 7 months ago](https://wordpress.org/support/topic/sql-query-9/#post-17150410)
 * You can use a subquery, like this:
 * [https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html](https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html)
 * or you can use an IF EXISTS like this:
 * [https://stackoverflow.com/questions/27627825/select-if-exists-then-else-on-mysql](https://stackoverflow.com/questions/27627825/select-if-exists-then-else-on-mysql)
 * or change the address2 to not null like this:
 * [https://www.geeksforgeeks.org/sql-query-to-exclude-null-values/](https://www.geeksforgeeks.org/sql-query-to-exclude-null-values/)
 * This is not really a WP question. You’ll get better results in an sql support
   forum.
 *  Moderator [bcworkz](https://wordpress.org/support/users/bcworkz/)
 * (@bcworkz)
 * [2 years, 7 months ago](https://wordpress.org/support/topic/sql-query-9/#post-17153912)
 * I don’t think you would want such an extensive WHERE clause. If your goal is 
   to get all posts within a certain distance of a point based on lat & long meta
   values, what does it matter what the address is as long as the lat & long are
   correct?
 * Seems to me all that matters in a WHERE clause is post_type, post_status, and
   distance. I don’t think you should be concerned with the other criteria you have
   in WHERE.
 *  Thread Starter [creativ3y3](https://wordpress.org/support/users/creativ3y3/)
 * (@creativ3y3)
 * [2 years, 7 months ago](https://wordpress.org/support/topic/sql-query-9/#post-17154938)
 * Hi, it turned out the issue was indeed with the WHERE clause. I got it working
   when I moved the AND address2…. into the ON clause. Using an endpoint, I wanted
   compare the lat long info with that provided by a mobile app then to bring back
   the correct shop information back to present it inside the app.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘sql query’ is closed to new replies.

 * In: [Developing with WordPress](https://wordpress.org/support/forum/wp-advanced/)
 * 3 replies
 * 3 participants
 * Last reply from: [creativ3y3](https://wordpress.org/support/users/creativ3y3/)
 * Last activity: [2 years, 7 months ago](https://wordpress.org/support/topic/sql-query-9/#post-17154938)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics