MYSQL Custom Field Sorting: With Maintenance Tables

Sorting in SQL is usually just a matter of selecting the field of data the tuples or rows should be iterated upon to produce the end result recordset. At times a custom or unnatural sort is required.

This article investigates the use of a natural custom sort by using a database table to maintain sort fields. The output is custom in the statements do not need to be tuned again. Sorting is maintained by merely updating the records of the database table only.

Define the MYSQL Custom Ordering Tables

Log into the cPanel of your website and enter phpMyAdmin. If there is no database defined click the SQL button and paste this into the SQL window:

CREATE DATABASE test;

USE test;

Click the OK button and the SQL statements will be executed. When complete, click on the newly created database named 'test' and the phpMyAdmin interface will be updated with the objects for the newly created database. Click on the SQL button and paste the following in:

-- --------------------------------------------------------

-- 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 `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 ;

INSERT INTO `myorderfields` (`idx`, `mytables_idx`, `name`, `desc`) VALUES

(1, 1, 'name', NULL);

-- --------------------------------------------------------

-- 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 `name` (`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);

Click the OK button and the SQL statements above will be executed. The objects to support this new method of sorting are then created.

For this example the test data table 'animals' has already been created and for the purposes of this demonstration, test data has also been created to define a table entry in the table mytables and a defined sort field in table myorderfields. The mini-system is in place and populated with data.

Make Use of MYSQL Custom Ordering Tables

Whenever data from the animals table is requested an SQL statement is send in the form:

select name from animals

where active = 1

order by

(

select name

from myorderfields

where mytables_idx = (select idx from mytables where name = 'animals')

)

That statement above will search through the mytables table and extract the idx for the 'animals' table, then use that in building the statement to extract the current ordering field in the myorderfields table and finish building the original SQL statement and deliver the content to the calling application.

This method can save a webmaster a lot of time while also allowing the website maintainers an easy method of changing the custom sorting of a MYSQL driven website without requiring the help of the webmaster.