Changeset 9640

Timestamp:

03/29/2020 01:47:54 AM (6 years ago)

Author:

dd32

Message:

Events API: Add an exact-match clause to the SQL ordering to ensure that the closest match for the provided location name is returned when there exist multiple variants of the same city with different accents.

See #5117.

File:

• sites/trunk/api.wordpress.org/public_html/events/1.0/index.php (modified) (2 diffs)

sites/trunk/api.wordpress.org/public_html/events/1.0/index.php

@@ -301 +301 @@
                         FIELD( %s, country  ) DESC,
                         FIELD( %s, timezone ) DESC,
-                        population DESC
+                        population DESC,
+                        BINARY LOWER( %s ) = BINARY LOWER( name ) DESC
                 LIMIT 1";
 
-        $prepared_query = $wpdb->prepare( $query, $location_name, $country, $timezone );
+        $prepared_query = $wpdb->prepare( $query, $location_name, $country, $timezone, $location_name );
         $db_handle      = $wpdb->db_connect( $prepared_query );
 
@@ -320 +321 @@
                                 FIELD( %s, country  ) DESC,
                                 FIELD( %s, timezone ) DESC,
-                                population DESC
+                                population DESC,
+                                BINARY LOWER( %s ) = BINARY LOWER( LEFT( name, %d ) ) DESC
                         LIMIT 1";
 
-                $prepared_query = $wpdb->prepare( $query, $wpdb->esc_like( $location_name ) . '%', $country, $timezone );
+                $prepared_query = $wpdb->prepare( $query, $wpdb->esc_like( $location_name ) . '%', $country, $timezone, $location_name, mb_strlen( $location_name ) );
                 $db_handle      = $wpdb->db_connect( $prepared_query );