Question 42222 in PHP Question posted in
Official manual can be found here.

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

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();
    });
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search