[Resolved] Unlimited category hierarchy

[liam_d]

unlimited category hierarchy

Hi all i am looking for a way to do this:

cat1
--sub1
---subsub1
----subsubsub1
-----subsubsub1

That kinda thing, at the moment i am using this:

$sql = 'SELECT parent.catname AS parent_catname

            , sub1.catname

            , sub2.catname

    FROM categories AS parent

LEFT OUTER

    JOIN categories AS sub1

        ON sub1.cparent = parent.catid

LEFT OUTER

    JOIN categories AS sub2

        ON sub2.cparent = sub1.catid

WHERE parent.cparent = 0 AND parent.ourscript = 0

ORDER

    BY parent.catname

    , sub1.catname

    , sub2.catname';

But the only as you can tell goes up to a maximum of 2 subcategory's, i have to add more to the sql query for each new sub category.
Is there any real easy way to do what i want?

 

[silentwind]

field:
- cat_id
- cat_name
- cat_parent (which is taken from cat_id)

now, in coding, you'll have to select it recursively:

<?php

	function recursive_category($parent_id, $max = 2, $deep = 0){
		$sql = "SELECT cat_id, cat_name WHERE cat_parent = '$parent_id'";
		$rs = mysql_query($sql);
		while($r = mysql_fetch_array($r)){
			echo  str_repeat("-", $deep)." ".$r['cat_name'];
			if($max == 0){
				break;
			}
			recursive_category($r['cat_id'], $max - 1, $deep + 1);
		}
	}
?>

This should be enough. Sorry, I don't test the code yet, it's just a quick algorithm post.
I'll review it again once I've time.

 

[liam_d]

Thank you so much, i have been posting on devshed for months about it, they have become so nitpicky and unhelpful over there it is unreal.

Thanks so much, will test in a minute and report back

Edit -> For the "$max = 2" i take it that is how many max i want to show right?

What is the "$deep = 0" for ?

 

[Daniel]

$deep = 0 means it doesn't have to be set, if it was explicitly set, it would be 0.

It's used here

str_repeat("-", $deep)

That would repeat "-" 0 times... Returning "".

Each time its run, it adds an extra "-" I believe, haven't used it myself though

Top
-1
--2
---3

http://www.php.net/str_repeat

Dan

 

[liam_d]

I don't get it though, if max controls how many i want to show max, surely that makes "Deep" not needed??

 

[liam_d]

Would it be possible to give me an example of it in use showing how i can use it to display an unlimited amount of categories and subcategories etc etc??

Thanks, will give rep and all my NP$ to someone who can!

 

[liam_d]

I fixed a small error in the function, but it doesn't work right at all, see my post.

function recursive_category($parent_id, $max = 2, $deep = 0)
{
	$sql = 'SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = "' . $parent_id . '"';
	echo $sql;
	$rs = mysql_query($sql) or die(mysql_error());
	while($r = mysql_fetch_array($rs))
	{
		echo  str_repeat("-", $deep)." " . $r['cat_name'];
		if($max == 0)
		{
			break;
		}
		recursive_category($r['cat_id'], $max - 1, $deep + 1);
	}
}

But all it seems to echo out is this:

SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = "0" SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""-- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""--- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- --- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- --- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT `catid`, `catname` FROM `categories` WHERE `cparent` = ""----- ---- SELECT

When its sposed to do this
php
---something
-----something else

etc etc

any ideas?

 

[Peter]

what version of mySQL are you using? if supported in the version you use, you could look to use subqueries. The problem with the way that function is working that it will continually look for child directories for every parent directory you have. Best having mySQL do this rather than php continually calling to mySQL.

 

[liam_d]

Well the reason i wanted it was i used to use this:

$sql = 'SELECT parent.catname AS parent_catname
            , sub1.catname
            , sub2.catname
    FROM categories AS parent
LEFT OUTER
    JOIN categories AS sub1
        ON sub1.cparent = parent.catid
LEFT OUTER
    JOIN categories AS sub2
        ON sub2.cparent = sub1.catid
WHERE parent.cparent = 0 AND parent.ourscript = 0
ORDER
    BY parent.catname
    , sub1.catname
    , sub2.catname';

Which i am pretty sure is what you mean, only thing is, i don't want people who use the script have to add "sub3.catname" etc etc everytime they want more category support...

 

[Peter]

no that is a join not a subquery.

had a quick look, here is an article about doing it with joins:-

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

Scroll down about half way until you get to:-

"We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:"

This looks to be exactly what you are looking for.

 

[liam_d]

That page confused the hell out of me to be honest, and i couldn't find the part you said

 

[Peter]

OK can you post the actual structure you have for your database tables that hold the categories.

 

[liam_d]

I have decided to try and use Ajax for it instead now, which i need a little help with, will make a new post for it, thanks.