Multi-level categories

Hi All

I've been searching the web for a solution to my problem but not found anything concrete. I hope someone can point me in the right direction here.

Basically I want to have multi-level categories on my site. I want to display these on my main page as you would find on a classifieds website for instance.

Probably the best method of achieving this that I've found is to have a table of categories and then have a 'parent' field. If a category has a parent field then it is one level below its parent in the hierarchy. My problem with this method is how to efficiently output the data on to my site.

eg.

Get first category.
If (no parent id) then {
output
} else {
output it after its parent id
}


How do I do that? I'm thinking maybe I could do a loop to go through all entries in the table and then put them all into an array of some sort? Then I could loop through the array and output to the page?

Does anyone have a better method or advise how I would do the above?? Ideally I would like to display child categories in alphabetical order below their parent categories.

Thanks in advance

well, if you have a table that has all of your categories in it (parents and children), then you can use one query that determines the category name (if it's a parent) and another query if it's a child that will also get the parent. all you'd have to do is a simple join:

for a regular parent:
SELECT title FROM table WHERE id=[pid]

for a child:
SELECT c.title, p.title as parent_title FROM table c LEFT JOIN table p ON c.parent=p.id WHERE c.id=[cid]

another way I've done this in the past is having a category table and then a services table (but this was also a case where there would only ever be a category with a subcategory, and subcategories would never have children of their own), and the services table would just have an extra field that had the parent's ID. both might have their advantages and disadvantages, but I might go with my first method if I were to do it again now.

hope that answered your question, as I'm not entirely sure what you're trying to do. either way, creating an array and then looping through it to figure out the parent is incredibly inefficient. if the SQL doesn't make sense to you, feel free to ask questions.

and, just to be clear, the SQL I've given above would ideally be used to display a "title" for a category and also a subcategory with the parent (meaning, the SQL I gave you is for when you actually browse to a category or subcategory). to display all of the categories in a list with all of their respective subcategories, you'd have to do a bit more. but, first let's see if we're on the right track.

Thanks for your reply Kows,

I don't understand that SQL too well actually. It would help a lot if you explain what it's actually doing.

What I would ideally like is to have 1 table called 'categories'. There would be a field in this table called 'parentid' and this would denote the parent/child relationship. eg.

categoryid = 1
categoryname = apple
parentid = 2 (where this id is another category called 'fruit')

Categories could be multi-level, so 'apple' itself could have subcategories such as 'granny smith', 'golden delicious' etc.

THEN

I would like to display all the categories in an ordered list. Such as:

Quote:

---

1. fruit
i. apple
- granny smith
- golden delicious
ii. banana
iii. pineapple
2. vegetable
i. carrot
etc.

---

Thanks for your help!

A little recursive function could handle this for you... Assuming your table looks like this:

Code:

---

categoryid        categoryname              parentid
1                fruit                  0
2                apple                  1
3                granny smith            2
4                golden delicious        2
5                banana                  1
6                pineapple              1
7                vegetable              0
8                carrot                  7

---

...then the following function would get the job done:

Code:

---

<?php
function getChildren($id){
  $sql_result = mysql_query("SELECT * FROM mytable WHERE parentid=$id");
  if(mysql_num_rows($sql_result) > 0){
    while($sql_row = mysql_fetch_assoc($sql_result)){
      echo $sql_row['categoryname']."<br/>";
      getChildren($sql_row['categoryid']);
    }
  }
}

getChildren(0);
?>

---

Some list formatting would make it look better, of course (<br> = laziness).

And kows might have a better SQL based solution.

the only problem I have with that is the amount of queries being used. with a large list, you could have 20-30 queries on every page load.

on that note, I don't have any real solutions (that are unlike Samba's) that would allow you the flexibility to let any item have children. any array-based solutions are kind of unintuitive and feel clunky.

nonetheless, I made a solution using arrays anyway. if you can make sense of it, then you could use it. if you can't, then maybe not; if you want to change it in the future, it's best to understand the code in the first place. it uses another recursive function, like Samba's, but only queries the database once. there might even be a better solution doing something like this, but I'm feeling a little out of it lately. anyhow, if you'd like an explanation of any of this, let me know. I added a few comments to help either way.

the following code will produce this result (without indentation -- I added that for readability):

HTML Code:

---

<ol>
  <li>fruit
    <ol>
      <li>apple
        <ol>
          <li>granny smith</li>
          <li>golden delicious</li>
          <li>apple child</li>
        </ol>
      </li>
      <li>pineapple
        <ol>
          <li>pineapple child</li>
        </ol>
      </li>
      <li>banana</li>
    </ol>
  </li>
  <li>vegetable
    <ol>
      <li>carrot</li>
    </ol>
  </li>
</ol>

---

PHP Code:

---

  //get the child's ID and title, and get its parent ID and title.
  $sql = "SELECT c.id, c.title, c.pid as parent_id, p.title as parent_title FROM categories c LEFT JOIN categories p ON c.pid=p.id";
  
  $query = mysql_query($sql);
  
  $items = array();
  
  while($row = mysql_fetch_assoc($query)){
  
    //parent id
    $pkey = $row['parent_id'];
    //child id
    $ckey = $row['id'];
    //store this
    $items[$pkey]['children'][$ckey] = $row['title'];
  
  }
  
  //create our list
  createList($items);

  function createList($array, $first = true){
    //we need access to the original array
    global $items;

    //first is a flag on whether or not this is the first item in the array
    //we use this flag so that you don't need to initially call the function using createList($array[0]['children'])
    if($first){
      $array = $array[0]['children'];
    }
    echo "<ol>\n";
    foreach($array as $key => $value){
      echo "<li>{$value}";
      //if this item does have children, display them
      if(isset($items[$key]['children'])){
        echo "\n";
        createList($items[$key]['children'], false); //set $first to false!
      }
      echo "</li>\n";
    }
    echo "</ol>\n";
  } 


---

edit: cleaned some stuff up.

Quote:

---

the only problem I have with that is the amount of queries being used. with a large list, you could have 20-30 queries on every page load.

---

This is true. Could you cache the output?

yes, you most definitely could (and a large site might cache almost everything that they could). I suppose it just comes down to how many items are expected to be on this list and the traffic it's going to encounter, too.

Thanks for your replies and help! I'll look over the code this evening. I'm surprised there isn't a more efficient method of achieving this in PHP - many sites use similar navigation, how do they do it? You mention caching the output - how is this done? Is it automatic or could I set anything to enable it? Overall, I think the main requirement for me is to have a scalable list which can be any number of levels deep and still easy to edit and maintain; and of course it shouldn't put a significant load on the server.

caching stuff is something you would do yourself, it's not something you just enable with PHP. there may be classes or something else out there that could do it for you, though.

the idea behind caching is to execute all of the code once, then capture the output (using output buffering or storing it in a variable), and then storing that output in a database or a file. then when the page is being requested you check if a cached file exists and then include it. if it doesn't exist, you run the code again. you can set some sort of expiration or even have a cron job that runs and deletes cached files or cached database entries every so often, or whatever.

the code I posted shouldn't be too hard on the server; it only runs one SQL query. it might look or feel clunky, though, like I said before.

Thanks guys, I'll try it out and let you know how I got on.