sql

sql

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:

— ——————————————————–
— 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 `mycustomorderfields`
CREATE TABLE IF NOT EXISTS `mycustomorderfields` (
`idx` tinyint(4) NOT NULL AUTO_INCREMENT,
`mytables_idx` tinyint(4) NOT NULL DEFAULT ‘0’,
`fname` varchar(50) NOT NULL,
`sorder` int(1) NOT NULL,
`svalue` varchar(255) NOT NULL,
`desc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`idx`),
KEY `fname` (`fname`),
KEY `svalue` (`svalue`),
KEY `sorder` (`sorder`),
KEY `mytables_idx` (`mytables_idx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
— Dumping data for table `mycustomorderfields`
INSERT INTO `mycustomorderfields` (`idx`, `mytables_idx`, `fname`, `sorder`, `svalue`, `desc`) VALUES
(1, 1, ‘name’, 1, ‘fish’, NULL),
(2, 1, ‘name’, 2, ‘turtle’, NULL),
(3, 1, ‘name’, 3, ‘eel’, NULL),
(4, 1, ‘name’, 4, ‘whale’, NULL),
(5, 1, ‘name’, 5, ‘clam’, NULL),
(6, 1, ‘name’, 10, ‘cat’, NULL),
(7, 1, ‘name’, 8, ‘dog’, NULL),
(8, 1, ‘name’, 7, ‘horse’, NULL),
(9, 1, ‘name’, 9, ‘bird’, NULL),
(10, 1, ‘name’, 6, ‘zebra’, 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);

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:

SELECT DISTINCT a.name, a.idx, m.sorder
FROM animals a, mycustomorderfields m
WHERE m.mytables_idx = (
SELECT idx
FROM mytables
WHERE name = ‘animals’)
AND m.fname = ‘name’
AND a.name = m.svalue
AND a.active =1
ORDER BY FIELD(
name, ‘fish’, ‘turtle’, ‘eel’, ‘whale’, ‘clam’, ‘zebra’, ‘horse’, ‘dog’, ‘bird’, ‘cat’ )
LIMIT 0 , 30

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:

<?php
include(“includes/config.php”);
//=========================
// build FIELD sort clause
//————————-
$i=0;
$fieldstr=””;
$tablename=”animals”;
$sort=”name”;
$sql = “select svalue from mycustomorderfields
where mytables_idx = (select idx from mytables where name = ‘”.$tablename.”‘)
and fname = ‘”.$sort.”‘
order by sorder”;
// in for testing
//echo $sql.”<br>”;
$res=@mysql_query($sql);
while ($rs=@mysql_fetch_array($res)) {
$i++;
$val = $rs[“svalue”];
if($fieldstr==””)
{
$fieldstr=”‘”.$val.”‘”;
} else {
$fieldstr.=”,'”.$val.”‘”;
}
//echo $i.”<br>”;
}
// in for testing
//echo $fieldstr.”<br>”;
//==================================================================
// build sorted SQL statement with FIELD clause and produce results
//——————————————————————
$sql=”select distinct a.name, a.idx, m.sorder
from animals a, mycustomorderfields m
where m.mytables_idx = (select idx from mytables where name = ‘”.$tablename.”‘)
and m.fname = ‘”.$sort.”‘
and a.name = m.svalue
and a.active = 1
ORDER BY FIELD(name, ” . $fieldstr . “)”;
// in for testing
//echo $sql.”<br>”;

echo “<table width=’30%’ cellpadding=’0′ cellspacing=’0′ valign=’top’ align=’center’

border=’1′><tr><td colspan=’3′ align=’center’><b>Custom MYSQL

Sorting</b></td></tr><tr><td width=’8%’>sorder</td><td

width=’14%’>name</td><td width=’8%’>idx</td></tr>”;
$res=@mysql_query($sql);
while ($rs=@mysql_fetch_array($res)) {
$name = $rs[‘name’];
$idx = $rs[‘idx’];
$sorder = $rs[‘sorder’];

echo

“<tr><td>”.$sorder.”</td><td>”.$name.”</td>
<td>”.$idx.”</td></tr>”;
}
echo “</table>”;
?>

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.

Leave a Reply

Your email address will not be published.