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.

1. Trencin
2. 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();
    });

CodePudding user response：

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
       

CodePudding user response：

you can use a case expression, so if more lines are in the result, keep ordering alphabeticaly:

select * from (
select 
city,
region,
case when city = region then region 
else CONCAT(region,city)
end as sorter
from cities
  ) as temp
  order by temp.sorter

playground http://sqlfiddle.com/#!18/a4dc06/4

results in

city          region    sorter
Trenc         Lucenec   LucenecTrenc
Trencin       Trencin   Trencin
another city  Trencin   Trencinanother city
other city    Trencin   Trencinother city