phpmyadmin Question posted in
The official documentation can be found here.

WordPress sql query results vs sql query in phpmyadmin query different order

I’m bit confused about wordpress, i have following query to calculate distance between posts :

public function distance($latitude, $longitude){
    global $wpdb;
    $sql = "SELECT l.post_id,( 6371 * acos( cos( radians($latitude) ) * cos( radians( l.latitude ) ) 
       * cos( radians(l.longitude) - radians($longitude)) + sin(radians($latitude)) 
       * sin( radians(l.latitude)))) AS distance 
    FROM localisation as l
    HAVING distance < 50
    ORDER BY distance ASC";
    $output = $wpdb->get_results($sql, ARRAY_A);
    dump($output) // <--- Here i test the result

}

The output displays the posts from the closest to the farthest (around 50km) except that when I copy/paste the same query I don’t have the same result (the products are ordered from the closest to the farthest but not the same order)

Output from this query :

post_id. | distance. 
1245       0.25029229952593496
8547       0.25029229952593496
78451      0.25029229952593496
45874      0.4428014744990856

But output from phpmyadmin :

post_id. | distance. 
78451      0.25029229952593496
8547       0.25029229952593496
1245       0.25029229952593496
45874      0.4428014744990856

Order are not the same, even sorting is good

Have you an idea? why i have get different order ?

Thank you

Tags:

2

Answers


  1. 0

    I think that’s because you should order by ‘distance’ first and then you should order by ‘id’. Php my admin ordered by id ‘DESC’ by default but wp ordered it by id ‘ASC’,
    You should try in you wp query something like this

    ORDER BY distance ASC, ID ASC

    or

    ORDER BY distance ASC, ID DESC
    Login or Signup to reply.
  2. 0

    ORDER both column.

    ORDER BY
    post_id ASC, distance ASC if you want the first result

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search