Jump to content

Building a catalog...


cmgmyr

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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);
?> 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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";
}
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.