What I’m trying to achieve is a store directory listing nearby stores, ordered by distance. I’ve managed to grab all the stores within the specified radius and display them, and also calculated the distance, but the ordering is causing me major headaches!
Here’s the code:
function mbdmaster324_show_stores_in_radius_outside_city_shortcode() {
ob_start();
$latitude = get_field('location_latitude', $post_id->ID);
if (!isset($latitude)) $latitude = '';
$longitude = get_field('location_longitude', $post_id->ID);
if (!isset($longitude)) $longitude = '';
global $wpdb, $paged, $max_num_pages;
/**
* Get current paged value, for pagination
*/
if ( get_query_var('paged') ) {
$paged = get_query_var('paged');
} else if ( get_query_var('page') ) {
$paged = get_query_var('page');
} else {
$paged = 1;
}
/*
* Get option from settings, how many posts per page to display
*/
$post_per_page = 9999;
/*
* Calculate offset for pagination
*/
$offset = ($paged - 1)*$post_per_page;
/*
* Define variables
*/
$sort = 'ASC'; // Sort posts
$range = get_field('radius', $item->ID);
$city_name = get_field('city_name', $item->ID);
$search_lat = get_field('location_latitude', $item->ID);
$search_lng = get_field('location_longitude', $item->ID);
$table_name = $wpdb->prefix . 'my_geodata';
$table_name_2 = $wpdb->prefix . 'postmeta';
/*
* Construct basic SQL query
* Query will return all posts sorted by post title
*/
$sql_query1 = "SELECT SQL_CALC_FOUND_ROWS * FROM $wpdb->posts";
$sql_join1 = " INNER JOIN $table_name AS geo ON ($wpdb->posts.ID = geo.post_id)";
$sql_join1_2 = " INNER JOIN $table_name_2 m1
ON ($wpdb->posts.ID = m1.post_id)";
$sql_join1_3 = " INNER JOIN $table_name_2 m2
ON ($wpdb->posts.ID = m2.post_id)";
$sql_where1 = " WHERE (
$wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'stores'
AND (m1.meta_key = 'store_level' AND m1.meta_value != 'Featured')
AND (m2.meta_key = 'store_city' AND m2.meta_value != '$city_name')
)";
$sql_group1 = " GROUP BY {$wpdb->posts}.ID";
$sql_order1 = " ORDER BY $wpdb->posts.post_title $sort";
$sql_limit1 = " LIMIT $offset, $post_per_page";
/*
* If latitude and longitude are defined expand the SQL query
*/
if( $search_lat && $search_lng ) {
/*
* Calculate range
* Function will return minimum and maximum latitude and longitude
*/
$minmax = bar_get_nearby( $search_lat, $search_lng, 0, $range );
/*
* Update SQL query
*/
$sql_where1 .= "
AND (
(
(geo.lat BETWEEN '$minmax[min_latitude]'
AND '$minmax[max_latitude]'
)
AND (
geo.lng BETWEEN '$minmax[min_longitude]'
AND '$minmax[max_longitude]'
)
)
)";
}
/*
* Construct SQL query and get results
*/
$sql1 = $sql_query1 . $sql_join1 . $sql_join1_2 . $sql_join1_3 . $sql_where1 . $sql_group1 . $sql_order1 . $sql_limit1;
$query1 = $wpdb->get_results($sql1, OBJECT);
/*
* Calculate pagination
*/
$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
$max_num_pages = ceil($sql_posts_total / $post_per_page);
if ( $query1 ){
echo '<div class="w2dc-listings-block">';
echo '<div class="store-listings-container"><h3>Nearby stores</h3>';
/**
* Loop through posts
*/
global $post;
/**
* Calculate the distance from the location to the store
*/
$lat2 = get_field('store_latitude', $item->ID);
$lng2 = get_field('store_longitude', $item->ID);
$distance = getDistance( $search_lat, $search_lng, $lat2, $lng2 );
function mysort($a, $b) {
return strcmp($a['distance'], $b['distance']);
}
usort($option, 'mysort');
foreach ($query1 as $post):
/**
* Calculate the distance from the location to the store
*/
$lat2 = get_field('store_latitude', $item->ID);
$lng2 = get_field('store_longitude', $item->ID);
$distance = getDistance( $search_lat, $search_lng, $lat2, $lng2 );
setup_postdata($post);
get_template_part( 'content', 'store-list' );
endforeach;
}
wp_reset_postdata();
$content = ob_get_contents();
ob_clean();
return $content;
}
As I said, this is working to the extent that it displays the correct stores, and calculates the distance, but what I need is to order the results by distance.
I have tried to use usort but I have to concede that I’m not at all sure what I’m doing!
In terms of research, I’m found some related info, but can’t see how to apply it to this case.
- http://www.mullie.eu/geographic-searches/
- Pass extra parameters to usort callback
- Order Foreach loop results
A nudge in the right direction would be greatly appreciated.
