Building a catalog...

[cmgmyr]

So i'm going to be building a catalog soon and i'm just stuck on one "little" detail. I would like an unlimited number of categories, sub-categories, sub-sub-categories, and possibly sub-sub-sub-categories,etc... Is there an "easier" way to do this instead of just making 3-4(or more) tables? is there a way I can do it in like 2? It's not a big deal if I have to make more tables, but I'm just trying to be as efficient as possible.

 

Example:

 

- Buckets

 

--1 gal

---blue

----holes

----no holes

---red

----holes

----no holes

 

--5 gal

---blue

----holes

----no holes

---red

----holes

----no holes

 

Thanks!

-Chris

[Barand]

All that's required is a data structure like

 

 

id

parent_id

category

 

eg

[pre]

id  |  parent  |  category      |

-----+----------+-----------------+

  1  |    0    |    aaa          |

  2  |    0    |    bbb          |

  3  |    1    |    cccc        |

  4  |    3    |    ddddd        |

  5  |    4    |    eeeeee      |

  6  |    2    |    fff          |

[/pre]

 

Although there is also this

 

http://www.sitepoint.com/article/hierarchical-data-database/2

[cmgmyr]

Great! Thanks for that it makes sense now.

[cmgmyr]

What is the best way to cycle through these categories and sub-categories?

 

Thanks

[Barand]

If you are using the id, parent_id method, you use a recursive function.

[cmgmyr]

Yes, I am using the parentid method. I'm just having a little trouble getting started. Here is what I have so far:

$db = new mysql;

$sql = "SELECT * FROM categories WHERE parent_id = 0 ORDER BY cat_name";

// Perform a query selecting five articles
$result = $db->query($sql); 

// Display the results
while ($row = $db->fetch($result)) {
	$cat_name = stripslashes($row['cat_name']); 
	$cat_id = stripslashes($row['cat_id']);
	echo "$cat_name ";

	//See how many Sub-Categories there are
	$count = 0; //Clear count
	$sql = "SELECT * FROM categories WHERE parent_id = ".$cat_id;
	$sub = $db->query($sql);
	$count = $db->numRows($sub);

	if($count){
		echo "($count) <br />";
		//Continue to find more sub-categories

	}else{
		echo "<br />";
	}
}

 

Thanks

[Barand]

try

<?php
include 'db.php';

function listCats($id, $level=0) {
    $sql = "SELECT cat_id, cat_name FROM categories
            WHERE parent_id = '$id'
            ORDER BY cat_name";
    $res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');
    while (list($id, $name) = mysql_fetch_row($res)) {
        $indent = str_repeat('-', $level*5);
        echo "$indent $name<br />";
        listCats($id, $level+1);
    }
}

listCats(0);
?> 

[cmgmyr]

Thanks! That worked great! Just one more thing...

require "includes/class.mysql.php";

$db = new mysql;

listCats(0);

function listCats($id, $level=0) {
global $db;
$sql = "SELECT cat_id, cat_name FROM categories WHERE parent_id = '$id' ORDER BY cat_name";
    $result = $db->query($sql);
    while (list($id, $name) = $db->fetchRow($result)) {
    
    $indent = str_repeat('-', $level*5);
        echo "$indent $id. $name<br />";
        listCats($id, $level+1);
    }
}

 

Can I use anything other then global for $db?

 

Thanks

[Barand]

you could pass it to the function

 

listCats($db, 0);

 

function listCats ($db, $id, $level=0) {

...

}

[cmgmyr]

Simple enough

 

Thanks again, I think that should be it for now.

[cmgmyr]

OK...so right now I have gotten all of the categories to display correctly with the current amount of products. I have found if the current category had children categories. Now what I want to do is make a list out of them. So that all categories are in the <li></li> tags, but if the catefory has children then add <ul>, then close the ul when all of the children for that parent are done. Currently I have the code outputting the begginning ul ok, but I can't seem to figure out how to close them all correctly, it's getting some of them...but not all.

 

I have attached the small (test) database, the current (incorrect) output, and the correct output (that I manually altered). And here is the current script that I'm using:

<?php 
session_start();
require "class.mysql.php";

$db = new mysql;

listCats(0);

session_destroy();

function listCats($id, $level=0) {
global $db;
$sql = "SELECT cat_id, cat_name FROM categories WHERE parent_id = '$id' ORDER BY cat_name";
    $result = $db->query($sql);

while (list($id, $name) = $db->fetchRow($result)) {
    
    $indent = str_repeat('-', $level*2);

	//See how many products this category has
	$products = "SELECT count(*) FROM products WHERE cat_id = '$id'";
    	$count_products = $db->fetchOneCol($products);
	if($count_products > 0){
		$count_products = "($count_products)";
	}else{
		$count_products = "";
	}

	//Get parent id
	$parent = "SELECT parent_id FROM categories WHERE cat_id = '$id'";
    	$parent_id = $db->fetchOneCol($parent);

	//See if there are any child catagories
	$child = "SELECT count(*) FROM categories WHERE parent_id = '$id'";
    	$count_child = $db->fetchOneCol($child);
	if($count_child > 0){
		$end = "\n<ul>\n";
		$show_child = $count_child;

		if(!$_SESSION[$id]){
			$_SESSION[$id] = $count_child;
		}

	}else{
		$end = "";
		$show_child = "0";
		if($_SESSION[$parent_id]){
			$_SESSION[$parent_id]--;
			if($_SESSION[$parent_id] == 0){
				$end = "\n</ul>\n";
			}
		}
	}
        
	echo "$begin<li><a href=\"page.php?cat_id=$id\">$name</a></li>\n$end";
        
	listCats($id, $level+1);
    }
}

?>

 

Thanks

 

[attachment deleted by admin]

[Barand]

try

<?php 

listCats(0);

session_destroy();

function listCats($id, $level=0) {

$sql = "SELECT a.cat_id, a.cat_name, COUNT(b.cat_id) as num_child,
            COUNT(p.prod_id) as num_prod
            FROM categories2 a
                LEFT JOIN categories2 b ON a.cat_id = b.Parent_id
                LEFT JOIN products2 p ON a.cat_id = p.cat_id
            WHERE a.parent_id = '$id' 
            GROUP BY a.cat_id
            ORDER BY a.cat_name";
    $result = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

    echo "<ul>\n";
while (list($id, $name, $kc, $kp) = mysql_fetch_row($result)) {
    
    $indent = str_repeat('-', $level*2);
	$prod_count = $kp ? "($kp)" : '';
        
        echo "<li>$indent <a href=\"page.php?cat_id=$id\">$name</a> $prod_count</li>\n";
        
	if ($kc) listCats($id, $level+1);
    }
    echo "</ul>\n";
}
?>

[cmgmyr]

wow...you are amazing! thank you so much!

[Barand]

BTW, I forgot to take out the $indent bit - not needed with UL tags

[cmgmyr]

no problem, i got it thanks again for all the help! hopefully nothing else will come up.