MySQL sorting single field in multi orders

hi i am trying to sort a field with multiple orders for example here is my table

1st rule is to sort by Status. in following order: Active, Inactive, Merged, Promo A, Promo B, Promo C, Promo D, Defunct

Once they are sorted like this within each of these categories the 2nd rule in following Order: Traditional, Native, Salvation, Amm, Nav

3rd rule is to sort by the State within These categories By Alphabetical A-Z

if i cannot do this in mysql so can i do this in php? with sort function

i just need an idea or trick how i can do this thanks for help

-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO users VALUES ('1', 'testing', 'Inactive', 'Traditional', 'AZ');
INSERT INTO users VALUES ('2', 'testing 2', 'Merged', 'Native', 'AK');
INSERT INTO users VALUES ('3', 'testing 3', 'Promo A', 'Salvation', 'DC');
INSERT INTO users VALUES ('4', 'testing 4', 'Promo B', 'Salvation', 'PH');
INSERT INTO users VALUES ('5', 'testing 5', 'Promo C', 'Amm', 'PO');
INSERT INTO users VALUES ('6', 'testing 6', 'Promo D', 'Mai', 'AZ');
INSERT INTO users VALUES ('7', 'testing 7', 'Defunct', 'Nav', 'AK');
INSERT INTO users VALUES ('8', 'test 8', null, 'ABc', 'Dd');
INSERT INTO users VALUES ('9', 'test 9', 'asd', null, null);
INSERT INTO users VALUES ('10', 'test 10', 'Active - Full Membership', null, null);

Answer

use case

select 
  *
from
  users
order by
(
case status
  when 'Active'   then 1
  when 'Inactive' then 2
  when 'Merged'   then 3
  when 'Promo A'  then 4
  when 'Promo B'  then 5
  when 'Promo C'  then 6
  when 'Promo D'  then 7
  when 'Defunct'  then 8
  else 9999
end
),
(
case category
  when 'Traditional' then 1
  when 'Native'      then 2
  when 'Salvation'   then 3
  when 'Amm'         then 4
  when 'Nav'         then 5
  else 9999
end
), state;

Advertisement

Answer