A natural sort can be considered to be making use of an existing column in a table to sort data according to data in that column. An unnatural sort can be thought of as making use of an existing column but not sorting the data according to that columns ‘natural’ sort order. The resulting sort is defined in a different order and is usually hard-coded in the SQL statements. Hard-coding usually makes a technique of no value in the quickly changing business world.
In MYSQL Custom Field Sorting, a simple sort order integer field could be added to the table myorderfields and all the fields picked up in that sort order. This method goes a bit further in defining a new table so that a FIELD sort can be constructed over any table in any order.
Define the MYSQL Custom Ordering Tables And Test Data
To begin build the database tables by inserting the following definitions into an SQL prompt in phpMyAdmin:
Using Custom MYSQL Tables to Create Custom Sorting
With the above tables in place and populated with test data, the following .PHP file will build the following SQL statement to test the custom sort:
Since most hosting companies have limited the use of MYSQL procedures, it is easiest to rely on the power of PHP in order to accomplish building the dynamic SQL statement and driving it to produce the resultset. The following should be saved to a .PHP file called testSort.php uploaded to the server:
echo “<table width=’30%’ cellpadding=’0′ cellspacing=’0′ valign=’top’ align=’center’
border=’1′><tr><td colspan=’3′ align=’center’><b>Custom MYSQL
The config file will simply contain the connection string to the database and should not be left in the website root folder. Website folder security should always be practiced and the database config file should be held in an inner folder, and not in root.
All soucecode contained in this article is available for free download.
Running the test script on the server builds the correct sql statement including the FIELD sort clause from the database tables utilizing the sort order built from the custom tables. Using this method multiple sorts can be persisted over any table on any website and maintained by almost anyone. This method also lives quite comfortably with the custom sorting method in MYSQL Custom Field Sorting.
Complex sorting in MYSQL can be maintained easily using and building on this method.