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
<?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>”;
?>