Generating Unordered List with PHP + CodeIgniter from a MySQL Database

Question

I am trying to build a dynamically generated unordered list in the following format using PHP. I am using CodeIgniter but it can just be normal php.

This is the end output I need to achieve.

<ul id="categories" class="menu">
<li rel="1">
    Arts &amp; Humanities
    <ul>
        <li rel="2">
            Photography
            <ul>
                <li rel="3">
                    3D
                </li>
                <li rel="4">
                    Digital
                </li>
            </ul>
        </li>
        <li rel="5">
            History
        </li>
        <li rel="6">
                        Literature
        </li>
    </ul>
</li>
<li rel="7">
    Business &amp; Economy
</li>
<li rel="8">
    Computers &amp; Internet
</li>
<li rel="9">
    Education
</li>
<li rel="11">
    Entertainment
    <ul>
        <li rel="12">
            Movies
        </li>
        <li rel="13">
            TV Shows
        </li>
        <li rel="14">
            Music
        </li>
        <li rel="15">
            Humor
        </li>
    </ul>
</li>
<li rel="10">
    Health
</li>

And here is my SQL that I have to work with.

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` mediumint(8) NOT NULL auto_increment,
  `dd_id` mediumint(8) NOT NULL,
  `parent_id` mediumint(8) NOT NULL,
  `cat_name` varchar(256) NOT NULL,
  `cat_order` smallint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So I know that I am going to need at least 1 foreach loop to generate the first level of categories.

What I don't know is how to iterate inside each loop and check for parents and do that in a dynamic way so that there could be an endless tree of children.

Thanks for any help you can offer.

Tim

Chris · Accepted Answer · 2010-05-25 00:23:10Z

4

If you're outputting every category, then I'd probably do it in two steps:

First, grab all the relevant data from the database

$items = array();

$res = mysql_query(...);
while ($row = mysql_fetch_assoc($res)) {
 $items[$row['parent_id']][] = $row;
}

Then you have all your items grouped by their parent. You can then use a recursive function to process them all:

function showMenu($items, $parent = null) {
 // Assuming root elements have parent_id of 0:
 $index = $parent == null ? '0' : $parent;

 if (empty($items[$index])) {
  // No children, don't output anything
  return;
 }

 echo '<ul', $parent == null ? ' id="categories" ... ' : '', '>';

 foreach ($items[$index] as $child) {
  echo '<li rel="', $child['id'], '">', htmlentities($child['cat_name']);
  showMenu($items, $child['id']);
  echo '</li>';
 }

 echo '</ul>';
}

showMenu($items);

The function is recursively called for each item in the menu, and checks to see if that item has any children. If it does, it prints out the relevant <li/> entries and calls itself again to print out the children of that item.

answered May 25, 2010 at 0:23

Chris

10.5k1 gold badge40 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Over a year ago

Hey Dude, Thats amazing. Works so well. Thank-you so much for taking the time to make and share that code. I am wondering if there is an easy way to make the script generic enough so that it can handle multiple lists (so using dd_id in the DB as the list identifier) ? Once again, thankyou so much Tim

Chris Over a year ago

@Tim - No problem. As for making it generic, how about putting the initial bit (that builds the $items array) in a function which takes an $id param, then just modify the SQL query to select where dd_id = $id?

Tim Over a year ago

Hey Chris, My friend and I have been trying to nut this out but I think ive ended up confusing myself. Could you (if you have any time) post an example of how I can generate lists based on the dd_id field in the db ?

Collectives™ on Stack Overflow

Generating Unordered List with PHP + CodeIgniter from a MySQL Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related