Categorys and a sub category

[x1nick]

Been trying to follow this but struggling a little here to get the output I want

 

server version: 5.1.41

 

Table structures

 

--
-- Table structure for table `tpl_category`
--

CREATE TABLE IF NOT EXISTS `tpl_category` (
  `categoryid` int(10) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(20) COLLATE utf8_bin NOT NULL,
  `catfolder` varchar(20) COLLATE utf8_bin NOT NULL,
  `subcategory` tinyint(1) NOT NULL DEFAULT '0',
  `subcategoryid` int(10) NOT NULL DEFAULT '0',
  `moduleid` int(10) NOT NULL,
  `themeid` int(10) NOT NULL,
  PRIMARY KEY (`categoryid`),
  UNIQUE KEY `categoryname` (`categoryname`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tpl_category`
--

INSERT INTO `tpl_category` (`categoryid`, `categoryname`, `catfolder`, `subcategory`, `subcategoryid`, `moduleid`, `themeid`) VALUES
(1, 'Admin', 'admin/', 0, 0, 0, 1),
(2, 'Templates', 'tpl/', 1, 1, 2, 1);

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

--
-- Table structure for table `tpl_define`
--

CREATE TABLE IF NOT EXISTS `tpl_define` (
  `tplid` int(10) NOT NULL AUTO_INCREMENT,
  `tplname` varchar(25) COLLATE utf8_bin NOT NULL,
  `tpltag` varchar(25) COLLATE utf8_bin NOT NULL,
  `tplfile` varchar(30) COLLATE utf8_bin NOT NULL,
  `minify` tinyint(1) NOT NULL,
  `tpltype` int(10) NOT NULL,
  `lastupdate` int(10) NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`tplid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;

--
-- Dumping data for table `tpl_define`
--

INSERT INTO `tpl_define` (`tplid`, `tplname`, `tpltag`, `tplfile`, `minify`, `tpltype`, `lastupdate`, `categoryid`) VALUES
(1, 'Admin Login', 'ADMIN_LOGIN', 'login.tpl', 1, 1, 1283964629, 1),
(2, 'Admin HTML', 'ADMIN_INDEX', 'index.tpl', 1, 1, 1283967051, 1),
(3, 'Admin Dashboard', 'ADMIN_DASH', 'dashboard.tpl', 1, 1, 1283964597, 1),
(4, 'Templates Browse', 'ADMIN_TPL_BROWSE', 'tpl_browse.tpl', 1, 1, 1283990584, 2);

 

My current query

 

select *,`subfolder`.`catfolder` as `subfolder` from `tpl_define` 
								join `tpl_category`
									on `tpl_define`.`categoryid` = `tpl_category`.`categoryid`
								 left join `tpl_category` `subfolder`
									on `tpl_category`.`subcategoryid` = `subfolder`.`categoryid`

 

Now this outputs the following

 

Array
(
    [tplid] => 1
    [tplname] => Admin Login
    [tpltag] => ADMIN_LOGIN
    [tplfile] => login.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283964629
    [categoryid] => 
    [categoryname] => 
    [catfolder] => 
    [subcategory] => 
    [subcategoryid] => 
    [moduleid] => 
    [themeid] => 
    [subfolder] => 
)

Array
(
    [tplid] => 2
    [tplname] => Admin HTML
    [tpltag] => ADMIN_INDEX
    [tplfile] => index.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283967051
    [categoryid] => 
    [categoryname] => 
    [catfolder] => 
    [subcategory] => 
    [subcategoryid] => 
    [moduleid] => 
    [themeid] => 
    [subfolder] => 
)

Array
(
    [tplid] => 3
    [tplname] => Admin Dashboard
    [tpltag] => ADMIN_DASH
    [tplfile] => dashboard.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283964597
    [categoryid] => 
    [categoryname] => 
    [catfolder] => 
    [subcategory] => 
    [subcategoryid] => 
    [moduleid] => 
    [themeid] => 
    [subfolder] => 
)

Array
(
    [tplid] => 4
    [tplname] => Templates Browse
    [tpltag] => ADMIN_TPL_BROWSE
    [tplfile] => tpl_browse.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283990584
    [categoryid] => 1
    [categoryname] => Admin
    [catfolder] => admin/
    [subcategory] => 0
    [subcategoryid] => 0
    [moduleid] => 0
    [themeid] => 1
    [subfolder] => admin/
)

 

What I want to do:

Basically rows within tpl_define can either be within a root category or a sub category.

So a result for tplid '1'

no subfolder as this is under a root category

 

Array
(
    [tplid] => 1
    [tplname] => Admin Login
    [tpltag] => ADMIN_LOGIN
    [tplfile] => login.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283964629
    [categoryid] => 1
    [categoryname] => Admin 
    [catfolder] => admin/
    [subcategory] => 0
    [subcategoryid] => 
    [moduleid] => 1
    [themeid] => 1
    [subfolder] => 
)

 

Result tplid 4 which belongs in a sub category

Array
(
    [tplid] => 4
    [tplname] => Templates Browse
    [tpltag] => ADMIN_TPL_BROWSE
    [tplfile] => tpl_browse.tpl
    [minify] => 1
    [tpltype] => 1
    [lastupdate] => 1283990584
    [categoryid] => 1
    [categoryname] => Admin
    [catfolder] => admin/
    [subcategory] => 0
    [subcategoryid] => 0
    [moduleid] => 0
    [themeid] => 1
    [subfolder] => tpl/
)

 

Note all I require is when its in a sub category is the folder (catfolder as subfolder)

[fenway]

Sorry, I dont' follow.