How to create tree based on the following db?

PHP
1

Hi,

I have this database:

[code]CREATE TABLE IF NOT EXISTS categories (
catg_id mediumint(9) NOT NULL AUTO_INCREMENT,
catg_pid tinyint(1) NOT NULL DEFAULT ‘0’,
catg_name varchar(50) NOT NULL,
catg_desc text NOT NULL,
catg_status tinyint(1) NOT NULL DEFAULT ‘1’,
PRIMARY KEY (catg_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

–
– Dumping data for table categories

INSERT INTO categories (catg_id, catg_pid, catg_name, catg_desc, catg_status) VALUES
(1, 0, ‘Toys’, ‘’, 1),
(2, 0, ‘Computer’, ‘’, 1),
(3, 1, ‘Stuffed Toys’, ‘’, 1),
(4, 1, ‘Mechnical Toys’, ‘’, 1),
(5, 3, ‘Teddy Bear’, ‘’, 1),
(6, 3, ‘Barbie Doll’, ‘’, 1),
(7, 2, ‘Hardware’, ‘’, 1),
(8, 2, ‘Software’, ‘’, 1),
(9, 4, ‘Airplane & Helicopter’, ‘’, 1),
(10, 4, ‘Robot’, ‘’, 1),
(11, 0, ‘Category 5’, ‘’, 1);[/code]

How do I create a tree based on the following ?

Thanks.

2

What do you mean by tree ?? Do you want to fetch data from DB ?

3

Hi,

Yes from the database using php recursion.

Thanks.

4

Step 1: Pull all information. (Pulling a indeterminate height Adjacency tree from the database is difficult. If you want to pull trees from the database directly i’d recommend a Nested Set Model instead).
Step 2: Form the tree; the form you do this in depends greatly on what you want to do with the data; what are you trying to do?

5

Hi,

I am trying to do a 3 level category / subcategory dropdown.

Category
|____Subcategory 1
|_____Subsubcategory 1

Something like this.

Thanks.

6

Okay, if you can guarantee you will only have 3 levels, then a self-table query would work…

SELECT t1.catg_name AS category, t2.catg_name as subcategory, t3.catg_name as subsubcategory
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.pid = t1.catg_id
LEFT JOIN categories AS t3 ON t3.pid = t2.catg_id
7

Hi,

I just wanted to reduce this function I had made:

[code]function getCategoryTree ($catg_id)
{
global $conn;

$q0 = $conn->prepare("SELECT catg_id, catg_name FROM categories WHERE catg_pid = '0' AND catg_status = '1' ORDER BY catg_name");
$q0->execute();

$retdata = "<select name='cid'>\n";

while($a0 = $q0->fetch(PDO::FETCH_OBJ)) 
{
	if ($catg_id == $a0->catg_id) {
		$retdata .= "<option value='" . $a0->catg_id . "' selected>" . $a0->catg_name . "</option>\n";
	}
	else {
		$retdata .= "<option value='" . $a0->catg_id . "'>" . $a0->catg_name . "</option>\n";
	}

	$q1 = $conn->prepare("SELECT catg_id, catg_name FROM categories WHERE catg_pid = :catg_pid AND catg_status = '1' ORDER BY catg_name");
	$q1->bindParam(':catg_pid', $a0->catg_id, PDO::PARAM_INT); $q1->execute();

	while($a1 = $q1->fetch(PDO::FETCH_OBJ)) 
	{
		if ($catg_id == $a1->catg_id) {
			$retdata .= "<option value='" . $a1->catg_id . "' selected>" . str_repeat("&nbsp;", 3) . $a1->catg_name . "</option>\n";
		}
		else {
			$retdata .= "<option value='" . $a1->catg_id . "'>" . str_repeat("&nbsp;", 3) . $a1->catg_name . "</option>\n";
		}
		
		$q2 = $conn->prepare("SELECT catg_id, catg_name FROM categories WHERE catg_pid = :catg_pid AND catg_status = '1' ORDER BY catg_name");
		$q2->bindParam(':catg_pid', $a1->catg_id, PDO::PARAM_INT); $q2->execute();

		while($a2 = $q2->fetch(PDO::FETCH_OBJ)) 
		{
			if ($catg_id == $a2->catg_id) {
				$retdata .= "<option value='" . $a2->catg_id . "' selected>" . str_repeat("&nbsp;", 6) . $a2->catg_name . "</option>\n";
			}
			else {
				$retdata .= "<option value='" . $a2->catg_id . "'>" . str_repeat("&nbsp;", 6) . $a2->catg_name . "</option>\n";
			}
		}
		
	}
}

$retdata .= "</select>\n";
return ($retdata);

}[/code]

Though it does what I want it to do but I thought maybe if there was a way to achieve it via sql.

Thanks.

8

That… is fairly tangled.

SELECT t1.catg_id AS catid,  t1.catg_name AS category,t2.catg_id AS subcatid,  t2.catg_name as subcategory, t3.catg_name as subsubcategory, t3.catg_id AS subsubcatid 
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.pid = t1.catg_id
LEFT JOIN categories AS t3 ON t3.pid = t2.catg_id
ORDER BY category, subcategory, subsubcategory

(Query Untested. Test for yourself)
This should put your tree items in order - which is the key to doing it all in a single (or two single, in my case) loop(s).

$option = array(); //An array for holding the relevant data.
while($row = $query->fetch()) {
  if($row['subsubcategory'] != null) { // This item was a level 3 item.
    $option[] = array("id" => $row['subsubcatid'], "name" => "&nbsp;&nbsp;".$row['subsubcategory']);
  } elseif($row['subcategory'] != null) { // This item was a level 2 item.
    $option[] = array("id" => $row['subcatid'], "name" => "&nbsp;".$row['subcategory']);
 } else { // This was a top level item.
    $option[] = array("id" => $row['catid'], "name" => $row['category']);
 }
}
//Display resultant set of options.
foreach ($option as $display) {
 echo "<option value='".$option['id']."'".(($option['id'] == $catg_id) ? " selected" : "").">".$option['name']."</option>\n";
}

(You could do all this in the first While, but i dont really like putting multiple echo statements echoing the same non-dynamic strings.)

9

Hi,

Thanks for the code, but its not working. The data is not coming on dropdown, when I did a print_r it showed categories / subcats coming in different order.

Thanks.

10

What order? May need to tweak the ORDER BY (I thought it sorted null-first… i may have gotten that wrong…) [You may find it easier to paste your print_r, if the data isnt sensitive]