CODEWITHSUNDEEP
phpmysqlsorting
CaptainQuint
CaptainQuint

Reputation: 342

Sorting a MySQL search result with Priority

I know that this question has been asked a few times on here, but looking through those threads yielded me no help. I sort my search results alphabetically using the ORDER BY "column Name". But, I want to have one search result always on top.

So I'll sort them all by their name column for example, but I always want Zaboomafoo to be the first result. How would I go about doing this? Here's my PHP:

$sql = "SELECT * FROM `" . $db_table . "` WHERE (UPPER(city) LIKE UPPER('%$searchq%') OR UPPER(postal) LIKE UPPER('%$searchq%')) AND disable = 1 ORDER BY name";

while($row1 = mysqli_fetch_array($query))
        {
            $name = $row1['name'];
            $business  =$row1['business'];
            $description = $row1['description'];
            $email = $row1['email'];
            $phone = $row1['phone'];
            $image = $row1['image'];
        }

And then I ouput that information into a variable called output, and echo it to the screen.

Upvotes: 1

Views: 206

Answers (3)

Konstantin
Konstantin

Reputation: 3450

Just for variety sake:

SELECT name
     ...
    , priority = CASE name
            WHEN 'Zaboomafoo' THEN 999
            WHEN 'Barzaboma' THEN 111
        END
FROM some_table
ORDER BY priority DESC
    , name

Upvotes: 0

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

If you only want one specific name to be always on top you can do

SELECT ... ORDER BY name = 'Zaboomafoo' DESC, name;

If you want to pin multiple rows you're probably better off with adding a separate priority column and do

SELECT ... ORDER BY priority DESC, name;

Upvotes: 0

bobwienholt
bobwienholt

Reputation: 17610

Try something like this:

SELECT *
FROM table_name
WHERE (UPPER(city) LIKE UPPER('%$searchq%') 
  OR UPPER(postal) LIKE UPPER('%$searchq%')) 
  AND disable = 1 
ORDER BY IF(name = 'Zaboomafoo', 0, 1), name

Upvotes: 1

Related Questions