-- --------------------------------------------------------
-- ==============================
-- MYSQL Custom Sorting I
-- Don Waterfield
-- http://www.addabyte.com
-- anrpgpgmr@yahoo.com
-- -------------------------------

--
-- Basic create database command
--
CREATE DATABASE test;
USE test;




-- --------------------------------------------------------
--
-- Table structure for table `myorderfields`
--
CREATE TABLE IF NOT EXISTS `myorderfields` (
  `idx` tinyint(4) NOT NULL AUTO_INCREMENT,
  `mytables_idx` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  `desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `name` (`name`),
  KEY `mytables_idx` (`mytables_idx`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `myorderfields`
--
INSERT INTO `myorderfields` (`idx`, `mytables_idx`, `name`, `desc`) VALUES
(1, 1, 'name', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `mytables`
--
CREATE TABLE IF NOT EXISTS `mytables` (
  `idx` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `mytables`
--
INSERT INTO `mytables` (`idx`, `name`, `desc`) VALUES
(1, 'animals', 'the animals table');
-- --------------------------------------------------------
--
-- Table structure for table `animals`
--
 CREATE TABLE IF NOT EXISTS `animals` (
  `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`idx`),
  KEY `names` (`name`,`active`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `animals`
--
INSERT INTO `animals` (`idx`, `name`, `active`) VALUES
(1, 'cat', 1),
(2, 'dog', 1),
(3, 'horse', 1),
(4, 'bird', 1),
(5, 'turtle', 1),
(6, 'fish', 1),
(7, 'eel', 1),
(8, 'clam', 1),
(9, 'zebra', 1),
(10, 'whale', 1);



-- --------------------------------------------------------
--
-- Extracting the custom ordering field in a statement
--
select name from animals
order by (
  select name
  from myorderfields
  where mytables_idx = (select idx from mytables where name = 'animals')
)