• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Hierarchical <ul><li> from MySql

#1
[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>
&lt;?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){
?&gt;
<li>&lt;?php echo anchor(base_url() . '/keyword/' . $row->pageID . '/' . url_title($row->metaTitle, 'dash'), $row->linkName); ?&gt;</li>
&lt;?php
}
?&gt;
</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">
        &lt;?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"]);
?&gt;
        </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 Smile


Messages In This Thread
Hierarchical <ul><li> from MySql - by El Forum - 12-15-2007, 04:51 PM
Hierarchical <ul><li> from MySql - by El Forum - 12-16-2007, 02:25 AM
Hierarchical <ul><li> from MySql - by El Forum - 12-16-2007, 09:22 PM
Hierarchical <ul><li> from MySql - by El Forum - 12-16-2007, 09:40 PM
Hierarchical <ul><li> from MySql - by El Forum - 12-17-2007, 04:20 PM
Hierarchical <ul><li> from MySql - by El Forum - 12-18-2007, 06:00 AM
Hierarchical <ul><li> from MySql - by El Forum - 12-18-2007, 08:53 AM
Hierarchical <ul><li> from MySql - by El Forum - 12-18-2007, 09:04 AM
Hierarchical <ul><li> from MySql - by El Forum - 12-20-2007, 02:01 AM
Hierarchical <ul><li> from MySql - by El Forum - 10-25-2009, 01:49 PM
Hierarchical <ul><li> from MySql - by El Forum - 10-26-2009, 04:14 AM
Hierarchical <ul><li> from MySql - by El Forum - 08-04-2010, 09:06 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.