tree in a select box

[salman1]

hi all,

I am trying to create a tree in a select box from a table which contains parent & child categories in a hirearchy.

 

Where the out put would look some ting like this :

 

Cat 1

  |____Child 1

  |____Child 2

Cat 2

  |____Child 3

 

and so on for each parent child group.

 

My sql  table is structured like this.

 

ID              Cat Name          Parent

================================

1          |        Cat1        |          0

2          |        Cat2        |          0

3          |      Child1        |          1

4          |      Child2        |          1

5          |      Child3        |          2

 

and so on.

How would i query mySQL and create the HTML select box like this:

 

<select>

<option value=1>Cat 1</option>

<option value=3>  |____Child 1</option>

<option value=4>  |____Child 2</option>

<option value=2>Cat 2</option>

<option value=5>  |____Child 3</option>

 

 

plz if any 1 khow help me

 

thanks

 

salman

[Daniel0]

Hmm... I would fetch all the vertices in the tree first, and then generate the trees from that. Something like this should do it:

 

<?php
// SELECT id, name, parent FROM stuff ORDER BY parent DESC, id;

/**
* Data set from above query.
*/
$vertices = array(
array(
	'id' => 5,
	'name' => 'Child3',
	'parent' => 2,
),
array(
	'id' => 3,
	'name' => 'Child1',
	'parent' => 1,
),
array(
	'id' => 4,
	'name' => 'Child2',
	'parent' => 1,
),
array(
	'id' => 1,
	'name' => 'Cat1',
	'parent' => null,
),
array(
	'id' => 2,
	'name' => 'Cat2',
	'parent' => null,
),
);

/**
* Generate tree
*/
$subtrees = $trees = array();
foreach ($vertices as $vertex) {
$v = array(
	'id' => $vertex['id'],
	'name' => $vertex['name'],
	'children' => array(),
);

if (isset($subtrees[$vertex['id']])) {
	$v['children'] = $subtrees[$vertex['id']];
}

if ($vertex['parent'] === null) {
	$trees[] = $v;
}
else if (!isset($subtrees[$vertex['parent']])) {
	$subtrees[$vertex['parent']] = array($v);
}
else {
	$subtrees[$vertex['parent']][] = $v;
}
}
unset($subtrees);

/**
* Generate HTML
*/

function getSubtreeOptions(array $subtreeRoot, $level = 0)
{
$html = sprintf('%s<option value="%d">%s%s</option>' . PHP_EOL,
	str_repeat("\t", $level + 1),
	$subtreeRoot['id'],
	$level > 0 ? str_repeat(' ', $level) . '|_' : null,
	$subtreeRoot['name']);

foreach ($subtreeRoot['children'] as $child) {
	$html .= getSubtreeOptions($child, $level + 1);
}

return $html;
}

echo '<select name="stuff">' . PHP_EOL;
foreach ($trees as $root) {
echo getSubtreeOptions($root);
}
echo '</select>';

 

That will output:

<select name="stuff"> 
<option value="1">Cat1</option> 
	<option value="3"> |_Child1</option> 
	<option value="4"> |_Child2</option> 
<option value="2">Cat2</option> 
	<option value="5"> |_Child3</option> 
</select>

 

In your table you should represent parent=NULL for tree roots. It's not an optimal way of storing trees in a relational database, however. It's good enough if you always only fetch the entire tree, but if you at some occasions only need a particular subtree that will be more difficult. Have a look at modified pre-order tree traversal/nested set.

[salman1]

your code is very good but a little prob in a function in this line

 

function getSubtreeOptions(array $subtreeRoot, $level = 0)

{

 

array is ot accepted.

 

can you plz say how can i use the mysql_fetch_array to use this code. plz help me...

 

thanks

[Daniel0]

Well, you have to do something like this first:

 

$res = mysql_query('SELECT id, name, parent FROM stuff ORDER BY parent DESC, id') or trigger_error('Query failed: ' . mysql_error(), E_USER_ERROR);
$vertices = array();
while ($vertex = mysql_fetch_assoc($res)) {
$vertices[] = $vertex;
}

 

You'll of course have to adjust the query.

[Moncho]

This is a great code , 4 years leater still works like charm. =)

 

Pls i have just one question.

 

I want to ad doble "mark" -> to my subcategories inside subcategories.

 

for this part 

 

 

$level > 0 ? str_repeat(' ', $level) . '->' : null,
$subtreeRoot['name']);

 

I tried to add

$level > 1 ? str_repeat(' ', $level) . '->' : null,
$subtreeRoot['name']);

 

But this is not working

 

i need

 

Movies

  ->Horror

      ->-> SUbcategory of horror