Reorder results of select query by distance

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 ( ( ( 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 havre 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.

  • Pass extra parameters to usort callback
  • Order Foreach loop results

A nudge in the right direction would be greatly appreciated.

Category:php Views:4 Time:2018-12-30

Related post

  • Iterating result of Select Query 2010-04-12

    I have a question related to select query. here i am explaining down below. i have a table with the following data **Column1(Primary Key) Column2 Column3** ------ --------- -------------- 1 C 2 C 3 Null 4 H 5 L 6 H my problem is i have to replace the

  • Unexpected results in select query and in dataset 2011-05-09

    Today I observed some unexpected results from a select query and DataSet. With just simple query I am getting all the values from one field and displaying that in DataSet. But while filling in DataSet it is not the same values which are there in actu

  • Pl/Sql Storing multiple result of select query in single object in oracle 2013-08-22

    I have a scenario,a procedure in which there is for loop in which there is a select query which returns different result each time it run. I have to store each and every result in single variable or array or cursor.Please suggest!!.Below provided is

  • MySQL - How to put a result of Select Query to existing one? 2012-01-31

    Let's say i have two tables, Item and Transaction. Table Item ID Name 1 Copper Wire 2 Bolt 3 Screw 4 Rubber Band 5 Pipe Table Transaction Trans_ID Item_ID Qty T1 1 1 T1 2 2 T1 3 1 T1 4 2 T2 1 1 T2 3 2 I needs a select query showing a table like this

  • reorder results of mysql query php 2010-11-15

    I run a mysql query that gets specific results from a table. I then want to print these results in two html tables. The first ordered by one column which is already done by putting ORDER BY into mysql query. But then I want to print the results order

  • Return result of select query only if not empty 2011-08-22

    Working with Sql Server. Writing a stored procedure. Here is the pseudocode for what I want to achieve: IF EXISTS ( SELECT field1 FROM t1 WHERE field1 = ... AND field2 = ...) BEGIN SELECT field1 FROM t1 WHERE field1 = ... AND field2 = ... END any bet

  • tqsl - result from select query to operate as variable in subqueries? 2011-07-27

    For each user, I want to produce an aggregate result. However, I keep running into the problem of the subquery returning more than 1 result. SELECT **[user]**, ((SELECT SUM(amount) FROM transactions WHERE [user] = **[user]** AND [type] = 'credit' ) -

  • SQL Server 2005 Setting a variable to the result of a select query 2008-10-04

    How do I set a variable to the result of select query without using a stored procedure? I want to do something like: OOdate DATETIME SET OOdate = Select OO.Date FROM OLAP.OutageHours as OO WHERE OO.OutageID = 1 Then I want to use OOdate in this query

  • How can I use a select query to insert with a control file in Oracle? 2009-08-12


  • Scalar Function with select query as argument 2014-08-05

    is it possible to have a function in SQL which accepts the result of a select Query? like SCalarFunc(Select * from CustomQuery) and also I want to make a another select query back from the Function? can anybody give a sample? --------------Solutions-

  • how can i add select query result into dataset later again select query run 2009-04-08

    i want to add select query result into dataset, so i can write new query to run on it to get net dataset but how? Original query: MyDATASET=( select x, y,z from table1 union all select k,l,m from table2 ) i wan to this select * from this.MyDATASET --

  • Adding Row Numbers To a SELECT Query Result in SQL Server Without use Row_Number() function 2009-10-29

    i need Add Row Numbers To a SELECT Query without using Row_Number() function. and without using user defined functions or stored procedures. Select (obtain the row number) as [Row], field1, field2, fieldn from aTable UPDATE i am using SAP B1 DIAPI, t

  • Are the results deterministic, if I partition SQL SELECT query without ORDER BY? 2009-11-10

    I have SQL SELECT query which returns a lot of rows, and I have to split it into several partitions. Ie, set max results to 10000 and iterate the rows calling the query select time with increasing first result (0, 10000, 20000). All the queries are d

  • SQL Server PRINT SELECT (Print a select query result)? 2009-12-22

    I am trying to print a selected value, is this possible? Example: PRINT SELECT SUM(Amount) FROM Expense --------------Solutions------------- You know, there might be an easier way but the first thing that pops to mind is: Declare @SumVal int; Select

  • CodeIgnator: How execute a select query with where clause and return the result as table view 2010-03-01

    In codeigantor framework How i execute a select query with where clause and put the value into a table and return the table?? example: function abc($input) { $query=........where name='.$input.'; ........ ....... return table; } i do i

  • Print results of a SELECT query as preformatted text in PHP? 2010-04-14

    I'm looking for an easy and quick way to print out the results of a MySQL SELECT query in PHP as preformatted text. What I would like is to be able to pass a query object to a function and get a printout of the recordset like the command line MySQL c

  • Require reasonably random results from an SQL SELECT query within a Joomla article (Cache enabled) 2010-05-10

    Setup: Joomla website on LAMP stack I have a MySQL table containing some records, these are queried by a simple SELECT on the Joomla article, as pasted below. This specific Joomla website has Caching turned on in Joomla's Global Configuration. I need

  • Simple check for SELECT query empty result 2010-05-21

    Can anyone point out how to check if a select query returns non empty result set? For example I have next query: SELECT * FROM service s WHERE s.service_id = ?; Should I do something like next: ISNULL(SELECT * FROM service s WHERE s.service_id = ?) t

  • MySQL: Include COUNT of SELECT Query Results as a Column (Without Grouping) 2010-09-29

    I have a simple report sending framework that basically does the following things: It performs a SELECT query, it makes some text-formatted tables based on the results, it sends an e-mail, and it performs an UPDATE query. This system is a generalizat

  • large result set of mysql select query 2010-12-17

    What would be the easiest way to split an sql file of 80mb into multiple smaller sql files? thanks. dani. --------------Solutions------------- You can use the LIMIT and OFFSET statements to select a certain number of rows. For example: SELECT column

  • Is it possible to sort the results of a SELECT query in 2 different ways based on a conditional? 2011-01-26

    The following is invalid syntax but it is what I'm trying to say essentially: SELECT * FROM categories ORDER BY menu_order ASC WHERE parent = '1' || IF parent = '0' ORDER BY id ASC; Is it possible to order the results of a select query differently ba

  • How to keep a table that contains select query results 2011-02-24

    Hi I have 2 select queries that are returned to two seperate tables. While i am processing one of them inside a function i need the other query result to be kept inside a global parameter. How do i save the query result so it wont be overwritten when

  • how read two records at once from sql select query results? 2011-03-05

    i want to read two records each time from select query results in PHP ? --------------Solutions------------- like this: while($row1 = mysql_fetch_array($result)) { $row2 = mysql_fetch_array($result); // Do something } EDIT: Then it would be like this

  • Detecting no results in adodb select query 2011-03-14

    I'm using ADODB connection in C. The code works more or less fine but I'm getting errors when there is no result for my query and I try to read it. Relevant code: __object *con, *rec; con = __object_create("ADODB.Connection"); if(con!=NULL) con->O

  • How to order results of a query randomly & select random rows. (MySQL) 2011-04-08

    Please note I am a beginner to this. I have two questions: 1) How can I order the results of a query randomly. example query: $get_questions = mysql_query("SELECT * FROM item_bank_tb WHERE item_type=1 OR item_type=3 OR item_type=4"); 2) The best meth

Copyright (C), All Rights Reserved.

processed in 0.088 (s). 11 q(s)