-- --------------------------------------------------------
-- ==============================
-- MYSQL Custom Sorting II
-- Don Waterfield
-- http://www.addabyte.com
-- anrpgpgmr@yahoo.com
-- -------------------------------
--
-- 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 ;# MySQL returned an empty result set (i.e. zero rows).

--
-- Dumping data for table `myorderfields`
--
INSERT INTO `myorderfields` (`idx`, `mytables_idx`, `name`, `desc`) VALUES
(1, 1, 'name', NULL);# Affected rows: 1

-- --------------------------------------------------------
--
-- 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 ;# MySQL returned an empty result set (i.e. zero rows).

--
-- Dumping data for table `mytables`
--
INSERT INTO `mytables` (`idx`, `name`, `desc`) VALUES
(1, 'animals', 'the animals table');# Affected rows: 1

-- --------------------------------------------------------
--
-- 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 ;# MySQL returned an empty result set (i.e. zero rows).

--
-- 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);# Affected rows: 10


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




// THE SQL STATEMENT TO BE PROGRAMMATICALLY CREATED:
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')



// THE FOLLOWING IS TO BE SAVED TO A TEST SORT PAGE NAMED TESTSORT.PHP

include("includes/config_words.php");

// build FIELD 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>";