Categories and subcategories and subsub...

[RoLitoral]

First of all, I must say that I am reading this forum since 3 days ago searching for some help.

Still, no good answer (for me) to my problem.

 

So, what I have:

- a table CATEGORIES;

- four columns: id, categoryname, parentname and observations.

I already managed to insert data into the table from a form, also to view, edit and change it.

There are no limitations: every category can be parent for another new inserted subcategory (but a subcategory can have just one parent at a time), I can change subcategories to have another parent etc.

 

NOW!

I want to:

1. retrieve the name of the TOPCAT (select * from, limit 1... simple)

2. display it name as a top tablecell

3. with his name as parentname to retrieve all his childs (select * from categorii where previous categoryname is a parentname)

4. display their names into subcells under the top cell (somehow, managed)

5. retrieve all childs using names from 4. (biiiig problem starting from here...)

6. display under

and so on for every subsubsub...

 

Using while in while in while lops, all I was able to do so far was to get even one branch from the top to the bottom, even just topcell and first row of subcells.

 

My final work should look like a upside down tree.

 

 

Pls, for now just don't ask for my php code... I already delete it 3 times...

 

I wish to see a clean and simple idea, I am still a newbie.

 

Thanks in advance.

[Barand]

You need a recursive function

 

<?php

function displayCats ($name, $level) {
    $sql = "SELECT categoryname FROM category WHERE parentname='$name' ";
    $res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');
    while ($row = mysql_fetch_assoc($res)) {
        $indent = str_repeat('----', $level);
        echo  $indent . $row['categoryname'] . '<br>';
        displayCats($row['categoryname'], $level+1);
    }
}

displayCats('', 0);
?>

 

BTW, more efficient using id instead of name.

[RoLitoral]

Thanks for the answer.

I put that code to work, but really nothing is happening.

No error, no result

 

BTW, this is how it really looks (i changed the names before to be more... english-like):

 

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

require_once("functii.php");
include("head.php");


function displayCats ($name, $level) {
    $sql = "SELECT numecat FROM categorii WHERE subcat='$name' ";
    $res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');
    while ($row = mysql_fetch_assoc($res)) {
        $indent = str_repeat('----', $level);
        echo  $indent . $row['numecat'] . '<br>';
        displayCats($row['numecat'], $level+1);
    }
}

displayCats('', 0);
?>

[artacus]

Ok, first you should use parent_id instead of parent_name. Its much easier to join that way. (Oops, I see that Barand already mentioned that.)

 

His recursive function will work pretty well for the display page... once you get it working.

 

But for heavier lifting you might need a better data structure. I don't know what your categories are about, but consider a query that might be like "Show all items on the same trunk as the leaf 'fur parka'" So you'd have to find the ultimate parent of 'fur parka' which might be 'coats' and then proceed back down that branch. Its really hard to do w/ a single db query when you don't know how many levels you are going to be nested.

 

There used to be a great tutorial over a phpriot but I don't think they are around any more. You can read more about how to handle nested trees on wikipedia.

 

http://en.wikipedia.org/wiki/Tree_data_structure

[artacus]

Ok, I found a resource for you.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

The top 1/3 of the page describes the adjacent model, which is what you are doing right now. But take a look at the bottom 2/3 of the page, The Nested Set Model. That's really what you should be using in your situation. And since the page is mysql specific, all your queries are practically written for you if that's what you're using.

 

[RoLitoral]

OK, there is no problem for me to use id instead of name

 

I dont know what you mean by "once you get it workin". As I say before, I am still a newbie.

I have to do something with that code? Is not functional as it is?

 

Repeating myself, all I want from my query is just to get the topparent and display his subcats and so on.

 

I also read that tutorial in phpriot, but is way to hard for me to understand...

About wiki, yes, that picture of an basic tree represents exactly what I want

 

 

So... if somebody can give me a hand...

[artacus]

I dont know what you mean by "once you get it workin". As I say before, I am still a newbie.

I have to do something with that code? Is not functional as it is?

 

We don't know every detail about your environment and such. We'll give you the big picture "fix" and some details but its up to you to meld that knowledge into your particular situation.