StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You're definitely going to need to store your values differently. When I've done this I've used a table something along the lines of</p> <pre><code>CREATE TABLE `geo_db` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `post_id` int(11) DEFAULT NULL, `lat` float DEFAULT NULL, `lng` float DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `post_id` (`post_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; </code></pre> <p>You could also use a single column of GEOMETRY type, but this is fine for comparing geo points. Then in your plugin, you'll modify the sql used in WP_Query</p> <pre><code>function geo_posts_fields( $sql, WP_Query $wp_query ){ global $wpdb; // get your user coords here. explode $user_geocode or store in custom db as well. $lat = 45; $lng = -122; if( $wp_query->query_vars['orderby'] == 'geocode' ) $sql .= $wpdb->prepare( ", ROUND( ( 3959 * acos( cos( radians(geo_db.lat) ) * cos( radians( %f ) ) * cos( radians(%f) - radians(geo_db.lng)) + sin(radians(geo_db.lat)) * sin( radians(%f)))), 1 ) AS `distance` ", $lat, $lng, $lat ); return $sql; } function geo_posts_join( $sql, WP_Query $wp_query ){ global $wpdb; if( $wp_query->query_vars['orderby'] == 'geocode' ) $sql .= " LEFT JOIN geo_db ON $wpdb->posts.ID = geo_db.post_id "; return $sql; } function geo_posts_orderby( $sql, WP_Query $wp_query ){ if( $wp_query->query_vars['orderby'] == 'geocode' ) $sql = " `distance` ASC, ".$sql; return $sql; } function geo_posts_request( $sql, WP_Query $wp_query ){ // just to see whats going on var_dump( $sql ); return $sql; } add_filter( 'posts_fields', 'geo_posts_fields', 10, 2 ); add_filter( 'posts_join', 'geo_posts_join', 10, 2 ); add_filter( 'posts_orderby', 'geo_posts_orderby', 10, 2 ); add_filter( 'posts_request', 'geo_posts_request', 10, 2 ); </code></pre> <p>Finally, you'll need to register a query var in order to use orderby as an argument with get_posts, I'd recommend making a new WP_Query</p> <pre><code>$geo_posts = new WP_Query( array( 'post_type' => 'any', 'orderby' => 'geocode' ) ); while( $geo_posts->have_posts() ): $geo_post->the_post(); // etc </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload