Sorting rows in PHP/MySQL that match certain criteria

I have a database table with 6 fields that holds information about restaurants:

id

name

type

category_1

category_2

category_3

There are three "types" of restaurants: gold, silver, and bronze. Gold restaurants are featured on all 3 of their category pages (category_1, category_2, and category_3). Silver restaurants are featured on their top two categories (category_1 and category_2). Bronze restaurants are featured on their first category only (category_1). On my category pages, I need to display all restaurants in those categories, but Feature the restaurants who qualify to be featured (ie, they need to be listed at the top). I'm not sure how to do this with PHP / MySQL. I can query restaurants in the categories just fine, but how do I tell it to put Featured restaurants at the top? This is what I would need to do on the category "Mexican":

Display all mexican restaurants. Promote Gold restaurants that have "Mexican" listed in any of their category fields, Promote Silver restaurants that have "Mexican" in their top two category fields, and promote Bronze restaurants that have "Mexican" in it's top category开发者_开发问答 field.

I've been thinking of many different ways to do this and can't figure it out.

This is a conception problem. You shouldn't have three "category_X" columns. Instead, you should have a separate table (restaurant_category for instance). Basically, your table structures would look like this :

restaurant (restaurant_id, name, type)
restaurant_category (restaurant_id, category_name)

You could (and should) even normalize your tables by creating a category table and a type table, but for the sake of this question, I just assume you won't. Then, you add one row in restaurant_category for a bronze restaurant, two for a silver and three for a gold, and you can use a query like this one :

    SELECT name
      FROM restaurant
INNER JOIN restaurant_category
     USING (restaurant_id)
     WHERE category_name = 'Mexican'
  ORDER BY FIELD(type, 'Gold', 'Silver', 'Bronze'); -- if you had normalized
                                                    -- you'd use a column like
                                                    -- type_order to order by

This should help you out with your existing table structure.
I'm assuming type contains bronze, silver, and gold in this query.

SELECT * FROM restaurants 
WHERE category_1 = 'Mexican' 
  OR (category_2 = 'Mexican' AND type in ('silver','gold'))
  OR (category_3 = 'Mexican' AND type = 'gold')
ORDER BY Field(type,'gold','silver','bronze'), name asc;

Add this to your order by:

ORDER BY FIELD(type,"gold","silver","bronze")