I am doing autocomplete query system for cities and I have problem, that I want to prioritize main city of region from another results showed for exact phrase for ex. "%Trenc%". If city and region are the same, I would like to place it as first result and then everything other. Trencin Trenc My idea is create another column in

How to push value selected from db as first if it is equal with value in other column?

I am doing autocomplete query system for cities and I have problem, that I want to prioritize main city of region from another results showed for exact phrase for ex. “%Trenc%”.

|id| city     | region  |
...
|12 | Trenc    | Lucenec |
...
|23 | Trencin  | Trencin |

If city and region are the same, I would like to place it as first result and then everything other.

Trencin
Trenc

My idea is create another column in db as “Priority” and all main cities mark as “main.” But is it possible to do it just by SQL or JS?

This is my code:

    // inicialize object
    $db_handler = new DBController();

    // Search box value assigning to $city_name variable.
    if (isset($_POST['search_location'])) {

        $city_name = '%' . $_POST['search_location'] . '%';

        $query = "SELECT city, region FROM cities WHERE city LIKE ?";

        $selected = $db_handler->runQuery( $query, 's', array($city_name) );

        if (!empty($selected)) {
            foreach ($selected as $key => $search_result) {
                if ($search_result['city']) echo '<div class=add-item__custom-select-box-items>' . $search_result['city'] . ' (okres ' . $search_result['region'] . ')</div>';
            }
        }

    }

// Location query autocomplete action after key up
    $('#search-location').on('keyup click', function() {
        event.preventDefault();
        event.stopPropagation();
        // Assigning search box value to jQuery variable named as "cityName".
        var cityName = $('#search-location').val();
        //Validating, if "name" is empty.
        if (cityName == "" || cityName.length <= 2) {
           //Assigning empty value to "display" div in "add.php" file.
           $('.add-item__custom-select-box').html("");
        }
        // If name is not empty.
        else if (cityName.length > 2) {
           // AJAX is called.
           $.ajax({
               // AJAX type is "Post".
               type: "POST",
               // Data will be sent to "ajax.php".
               url: "_inc/autoQueryLocation.php",
               // Data, that will be sent to "ajax.php".
               data: {
                   // Assigning value of "name" into "search" variable.
                   search_location: cityName
               },
               // If result found, this funtion will be called.
               success: function(html) {
                   // Assigning result to "display" div in "search.php" file.
                   $('.add-item__custom-select-box').html(html).show();
               }
           })
        }
    });

    // Location query load data after click on option
    $('.add-item__input-wrapper').on('mousedown','.add-item__custom-select-box-items', function() {
        $('#search-location').val($(this).text());
        $(this).remove();
    });

    // Location query autocomplete action after focus out
    inputFormElem.on('focusout', function() {
        $('.add-item__custom-select-box-items').remove();
    });

Answer

You can use a CASE expression in the ORDER BY to ensure that records where city and region match come before all other records.

SELECT *
  FROM cities c
 WHERE c.city LIKE '%Trenc%'
 ORDER BY 
       CASE 
          WHEN c.city = c.region THEN 0
          ELSE 1
       END

The 0 and 1 in the case expression are using for sorting. So when the city and region match, the case expression will result in 0, otherwise, the case expression will result in 1. Since 0 comes before 1, it means the rows where city and region match will come back first in the query result.

Advertisement

Answer