[eluser]JimmyJ[/eluser]
Ok, here we go. Thought this mind be a good problem solver for all you mysql coding junkies out there. I'm trying to create a simple menu for a simple cms system. All the pages are stored in a database like so:
Code:
CREATE TABLE `pages` (
`pageID` int(3) NOT NULL auto_increment,
`pageParent` int(3) NOT NULL default '0',
`metaTitle` varchar(200) NOT NULL,
`metaDesc` varchar(255) NOT NULL,
`metaKeywords` varchar(100) NOT NULL,
`pageTitle` varchar(150) NOT NULL,
`pageDesc` text NOT NULL,
`altUrl` varchar(100) NOT NULL,
`linkName` varchar(25) NOT NULL,
`pageOrder` int(3) NOT NULL,
`templateID` int(3) NOT NULL,
`dateCreated` datetime NOT NULL,
`dateModified` datetime NOT NULL,
PRIMARY KEY (`pageID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `pages`
--
INSERT INTO `pages` (`pageID`, `pageParent`, `metaTitle`, `metaDesc`, `metaKeywords`, `pageTitle`, `pageDesc`, `altUrl`, `linkName`, `pageOrder`, `templateID`, `dateCreated`, `dateModified`) VALUES
(1, 0, 'Website Home', 'This is the desc for page 1', 'keywords here...', 'Home', 'this is the text...', '', 'Home', 1, 1, '2007-12-13 19:05:13', '2007-12-13 19:05:16'),
(2, 0, 'Page 2', 'This is the desc for page 2', 'page 2 keywords...', 'Demo page 2', 'This is the desc', '', 'Information', 2, 2, '2007-12-13 19:06:14', '2007-12-13 19:06:17'),
(3, 1, 'Sub page 1', 'desc of subpage 1', 'keywords for sub1', 'Subpage 1', 'blah blah', '', 'Company', 3, 1, '2007-12-13 19:07:05', '2007-12-13 19:07:08'),
(4, 2, 'Sub page 2', 'desc for page 2', 'keywords for page 2', 'Subpage 2', 'blahdblah...', '', 'What we do', 4, 2, '2007-12-13 19:07:42', '2007-12-13 19:07:45');
What i'm looking to do is spit them out in an hierarchical order using the pageParent into an ul li list - for example:
Code:
<ul>
<li>toplink
<ul>
<li>sublink
<ul>
<li>subsublink</li>
<li>subsublink</li>
<li>subsublink</li>
</ul>
</li>
<li>sublink</li>
<li>sublink</li>
</ul>
</li>
<li>toplink</li>
<li>toplink</li>
</ul>
I've got my code for spitting them out in a straight ul li like so:
Code:
<ul>
<li><a href="/">Home</a></li>
<?php
$sql = "SELECT pageID, pageParent, metaTitle, linkName, pageOrder FROM pages ORDER BY pageOrder ASC LIMIT 1,999";
$query = $this->db->query($sql);
foreach($query->result() as $row){
?>
<li><?php echo anchor(base_url() . '/keyword/' . $row->pageID . '/' . url_title($row->metaTitle, 'dash'), $row->linkName); ?></li>
<?php
}
?>
</ul>
So the big question is how the hell do i do this? I've spoken to a friend of mine and he's passed me some of his code for a project we worked on a while ago, but it looses me too. I've pasted it below:
Code:
<ul id="nav">
<?php
function recurse_over_categories($children, $firstLevel = true)
{
global $page_vars;
$count = 0;
foreach($children as $child) {
if(($firstLevel == true) && (Site::GetConfig("brandDisplayLimit") <= $count)) {
break;
}
echo("<li><a href=\"$child[url]\" title=\"$child[name]\">$child[name]</a>");
if(sizeof($child["children"]) > 0) {
echo("<ul>");
recurse_over_categories($child["children"], false);
echo("</ul>");
}
$count++;
echo("</li>");
}
}
recurse_over_categories($page_vars["categories"]);
?>
</ul>
Hopefulyl someone can help. I'll release my cms here once it's done and you can take a look or use it for your own purposes. It's very simple to style - which is my biggest concern