category / sub-category / sub-category -query question

mmilano

re: mysql

these are the fields in my "categories" table.

category_id
parent_id
category_name

i need to make a tree and display these in order. here are some examples of what is inside this table:

1  NULL  tv's
2  NULL  cameras
3  1        Big Screens
4  1        Plasmas
5  2        digital
6  2        35mm
7  5        Smart Media

i need each of the sub-categories to go under the parent categories. i can do this with many many queires, but i would like to find the most efficient way to display these categoreis with sub categories. the idea here is to be able to have infinate sub-categories nested inside categories.

so the results of my query will order it like this:

tv's
     Big Screens
     Plasmas

cameras
     digital
          smart media
     35mm

anyone have experience with this?

Phantazm

Well... I'm not sure this method is the most efficient way to do something like this, and this only works one level deep...

Parent
Sub1
Sub2
Sub3

And NOT

Parent
Sub1
SubSub1
SubSub2
Sub2
Etc

Anyway, here's the code (this works, but is still under development. I use this to populate a javascript "dropdown" menu bar)

  include ("global.php");
  $link = mysql_connect("localhost","user","secret") or die("Fatal Error: Unable to connect to mySQL!");
  mysql_select_db("eba") or die("Fatal Error: Unable to open eba database!");
  $query = "SELECT LinkID,LinkText,LinkURL,LinkParent,LinkAccessBit FROM navigation WHERE LinkVisible=1 AND LinkMenu=$menu ORDER BY LinkOrder";
  $result = mysql_query($query) or die("Fatal Error: mySQL Query Failed");
  // Build Menu Arrays
  while ($data = mysql_fetch_array($result))
  {
    $id = $data['LinkID'];
    $text = $data['LinkText'];
    $url = $data['LinkURL'];
    $parent = $data['LinkParent'];
    $accessbit = $data['LinkAccessBit'];
    if ($bit[$accessbit]==1)
    {
// Here is where the arrays are built (Main and Sub Menus)
      if ($parent==-1)
      {
        $main_array[]="\"<center>$text</center>\", \"$url\"";
        $sort_array[]=$id;
      }
      else
      {
        $sub_array[]="dhtmlMenu.addItem(new NavBarMenuItem(\"$text\", \"$url\"));";
        $sub_sort_array[]=$parent;
      }
    }
  }
  $ctr=-1;
  $ct=0;
  foreach($main_array as $entry)
  {
    $ct++;
  }
  $bs=((800/$ct)-5);
  $barsize="($bs,100)";
  foreach($main_array as $entry)
  {
    $ctr++;
    $navigation_array = "$navigation_array
dhtmlMenu = new NavBarMenu$barsize;
dhtmlMenu.addItem(new NavBarMenuItem($entry));";
    $ctr2=-1;
    $x=$sort_array[$ctr];
    foreach($sub_sort_array as $xyz)
    {
      $ctr2++;
      if ($xyz==$x)
      {
        $navigation_array = "$navigation_array\n$sub_array[$ctr2]";
      }
    }
    $navigation_array = "$navigation_array\nmyNavBar1.addMenu(dhtmlMenu);\n";
  }
  echo(template("MainIndex"));

Like I said, probably not the most effieient way to do it, but it works!

Also, I know this post is a bit vague at explaining how this works, but take a close look at the 4 arrays and you'll probably get the idea. If not, I'll check back tomorrow and try to help more. 🙂

mmilano

i really do appreciate the input, .. but it is important that i can make unlimited levels.

i have been working on a store layout for a little while now, but i decided to scrap it so i can offer more freedom to the store owners. =/ i am so stuck it is sad.

chriskl

What database are you using. If you're using anything by MySQL you can write a stored procedure to do it.

eg:

-- This function takes a category ID and returns a comma
-- delimited string of parent category IDs.
--
-- This code is example only - you are not permitted to use it yourself, you'll need to rewrite...
CREATE OR REPLACE FUNCTION getpath(int4) RETURNS text AS '
DECLARE
	base_cat ALIAS FOR $1;
	parent_cat INT4;
	temp TEXT;
	max_loops INT4;
	num_loops INT4;
BEGIN
	-- Max number of loops is 10
	max_loops := 10;
	num_loops := 0;
	temp := '''';

-- If any params are NULL, return NULL - this means function
-- can be defined isstrict.
IF base_cat IS NULL THEN 
	RETURN NULL;
END IF;

-- Get the next parent category
-- We sort DESC to get the NULLs at the top to minimise tree length
SELECT INTO parent_cat parent_id FROM library_cat_cat_map WHERE child_id=base_cat ORDER BY parent_id DESC LIMIT 1;
IF FOUND THEN
	temp := CAST(base_cat AS TEXT);
ELSE
	RETURN ''-2'';
END IF;
WHILE parent_cat IS NOT NULL LOOP
	-- Prepend to string
	temp := CAST(parent_cat AS TEXT) || '','' || temp;

	-- Get the next parent category
	SELECT INTO parent_cat parent_id FROM library_cat_cat_map WHERE child_id=parent_cat ORDER BY parent_id DESC LIMIT 1;		

	-- Max loops detection and breakout
	num_loops := num_loops + 1;
	IF num_loops >= max_loops THEN
		RETURN ''-1'';
	END IF;
END LOOP;

-- Return comma delimited string
RETURN temp;

END;
' LANGUAGE 'plpgsql'
WITH (isstrict);