CODEWITHSUNDEEP
phpsql
Michiel
Michiel

Reputation: 8083

Order column by keyword

I have a database filled with records and by using this MySQL-syntax:

 public static function allfood($db) {
    $sql = "SELECT DISTINCT sort, sortname FROM tblDishes WHERE foodanddrinks = 'e'";
    $result = $db->listing($sql);
    return $result;
  }

I get all the results ordered alphabetically. But I would like to determine my own order. For example Rabbitshould be displayed before Apple. Is there a SQL-syntax which allows me to organize my own order of display?

EDIT
This is stated in my dbconnections.phpfile.

public function listing($sql) {
    $result = mysql_query($sql, $this->_connection);
    while($row=mysql_fetch_array($result)) {
      $return[] = $row;
    }
    return $return;
  }

And this is the thing I tried, but I get an error in the dbconnections.php file 

public static function allfood($db) {
        $sql = "SELECT DISTINCT sort, sortname FROM tblDishes WHERE foodanddrinks = 'e' order by case sortname 
        when 'Rabbit' then smth1
        when 'Apple' then smth2
        when 'Fish' then smth3
        when 'Pasta' then smth4
        when 'Snacks' then smth5";
        $result = $db->listing($sql);
        return $result;
      }

Upvotes: 0

Views: 133

Answers (4)

k102
k102

Reputation: 8079

This may help you:

SELECT * FROM tblDishes WHERE foodanddrinks = 'e'
order by case field_name
when 'Rabbit' then smth1
when 'Apple' then smth2
...
else smth666 
end

Upvotes: 1

R Hill
R Hill

Reputation: 1804

It's a bit of an overhead, but it should work.

If you have a separate table containing keywords and a numeric priority, you could add a join to that table in your query, and then order by the priority column.

If you've only got, say, ten fixed keywords, you'll probably want to use order by case, like other respondents have suggested. If you've got dozens of keywords, or a list that's updated dynamically or with any notable frequency, you might want to consider a priority lookup table.

Also if the keywords have a lot of repetition, you might want to think about normalising them out into their own table anyway, with priority as a field.

Upvotes: 0

Shakeeb Ahmed
Shakeeb Ahmed

Reputation: 1808

You can do this by adding another column to the table named DisplayOrder and then Order the table by it.

Upvotes: 3

Pateman
Pateman

Reputation: 2757

First of all, you are not sorting the data in the snippet you posted. If you want an inversed alphabetical order, simply use:

public static function allfood($db) {
  $sql = "SELECT * FROM tblDishes WHERE foodanddrinks = 'e' ORDER BY foodanddrinks DESC";
  $result = $db->listing($sql);
  return $result;
}

EDIT: This may be worth your while, too. http://imthi.com/blog/programming/mysql-order-by-field-custom-field-sorting.php

Upvotes: 0

Related Questions