Categories - products how to build the menu three?

PHP
1

Hi

I have the following problem:

I have categories and products tables. Here they are:

Categories:

–
– Table structure for table categories


CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(37) NOT NULL,
  `parentid` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `metatags` text NOT NULL,
  `sorder` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL,
  `categoryphoto` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;

And here is my products table:


CREATE TABLE IF NOT EXISTS `products` (
  `productid` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `productcode` varchar(255) DEFAULT NULL,
  `productname` varchar(255) DEFAULT NULL,
  `shortdescription` text,
  `description` text,
  `price` varchar(255) DEFAULT NULL,
  `productphoto` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`productid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

I would like to build my menu three using those tables.

First, I would like to get first root category (which has parent id NULL) and to check if this category has sub categories. Then, if there are any, I should display them one by one, and for each of this categories I have to make the same check, that is if any of those categories has subcategories underneath.

Theoretically there may be unlimited levels of categories-subcategories.

If there are no, then I should display the products for each category.

I will appreciate if anyone can help me with this task. Links to tutorials for this topic will aslo do the trick.

Regards, Zoreli

2

Just dashing by – and I have never personally faced this same situation so forgive me if I am leading you up the garden path – but you could try this [google]sql menu tree hierarchical[/google], one of which (the SO post) mentions recursion and why you should really look at this old Sitepoint article http://www.sitepoint.com/hierarchical-data-database/ .

3

Thanks for pointing the article Cupe. Any other example of writing recursing functions in php will be also a great time saver.

Regards, Zoreli

4

After the check that I did yesterday, I am still helpless, and I need some help.

Any code snippet or example will be deeply appreciated.

Regards, Zoreli

5

Here is another article that talks about getting a categories and subcategories tree from your database. No PHP code though. And adding the products table in the equation might complicate things a bit.

6

You can use a recursive function, as others have explained.

Here is some code. It’s OOPHP so you if you know OO you will want to handle the database connection and want to be able to perform the query within the class.

If you don’t know OO PHP, the easy way is to add global $con (of whatever your connection var is) to the top of each function in the class that uses a query (this isn’t strictly a best practice though).

Or convert it to procedural, simply by making the method functions, but you’ll probably want to find a way to maintain the catlist variable.

I haven’t tested the code, but it should work.

To get the categories.

I’ve added $parentid (which would actually be the category id to start from) to the first method, so that you can start deeper within the chain (say start at the 3rd level) if you need to.



// Instantiate the category manager class
require_once('./class_categorymanager.php');
$categorymanager = new categorymanager();


// Create an array of all categories
$result = $categorymanager->fetch_category_list('0');

if(mysql_num_rows($result) > 0)
{
     while($row = mysql_fetch_array($result))
     {	
          $index_array[$row['id']] = array('title' => $row['name'],'parentid' => $row['parentid']);
     }
		
	 // Now generate the category list
     $categorylist.= $categorymanager->create_categorylist_list('0');
}


// Outout the categorylist or do whateveryou want to do with it
echo($categorylist);

Then the category manager class would contain


class categorymanager
{
	private $catlist;
        private $depth = '0';
	
	
	
	// -----------------------------------------------------------------------------------
	public function fetch_categorylist_list($parentid)
	{
				
		if(empty($parentid))
		{
			$parentid = '0';
		}
				
		// Get the categories (add your own ordering)
		$result = mysql_query("SELECT * FROM categories where visible='1'");			
	
		
		// Return the result
		return $result;
	}	
	
	// -----------------------------------------------------------------------------------
	
	
	public function create_category_list($parentid)
	{
		global $vue, $db, $index_array;
		
		$has_child = false;
		
		
		foreach($index_array as $key => $value)
        {
			if($value['parentid'] == $parentid) 
            {
				if($has_child === false)
				{      
					// If this is the first child print set has_child to true and increase the depth            
					$has_child = true;

					$this->depth++;
               	}



				// Construct the depth marker
				// You will want to adjust this to whatever you need, i.e. ul and li's or whatever
				$depthmarker = "";
				
				for($i=2; $i <= $this->depth; $i++)
				{
					if($this->depth > '1')
					{
						$depthmarker.= "- - ";
					}
				}
				
				
				// Create the category row				
				$cat['id'] = $key;
				$cat['name'] = $value['name'];
				
				
				// Create the category list item
				$catlist = '<a href="yourscript.php?catid=' . $cat['id'] . '">' . $cat['name'] .'</a>';
				
				// Add it to the classes catlist var
				$this->catlist.= $catlist;
				
				
				// Call the function again to generate sub categories of this category
				$this->create_category_list($key);
			}
		}
 		
		
		if($has_child === true) 
		{
			// Decrease the depth
			$this->depth = $this->depth -1;
		}
		
		
		
		// Return the category list
		return $this->catlist;
	}	
	// -----------------------------------------------------------------------------------
}

Be warned though, recursive functions can add an overhead if you have really deep, complex or large category trees. Of course under normal circumstances this wont be a problem.