categorties/sections (parents)

[knowj]

I have a MySQL table

id | name | parent
1 item1
2 item2 item1
3 item3 item1
4 item4 item1
5 item5 item2
6 item6 item2

Basically i want the items to be ordered by their parent e.g.

item1
item2
item5
item6
item3
item4


<label>Section Parent</label><br/>
<select name="section">
<option></option>
<?php
$product->view_sections();
$fields = array(
'id' => 'text',
'name' => 'text',
'parent' => 'text');
$sections = $product->drowstoarray($fields);
for($i=0;$i<=count($sections[$i]);$i++)
{
if (!empty($sections[$i]['parent']))
{
$space = '&nbsp;&nbsp;';
}
echo '<option value="'.$sections[$i]['id'].'">'.$space.$sections[$i]['name'].'</option>'."\n\r";
$space = '';
}
?>
</select>


function view_sections()
{
$this->query = "SELECT * FROM `sections` ORDER BY `name` ASC";
$this->execute();
}

Thanks in advance.

[BrainDonor]

wouldn't this do it?



$this->query = "SELECT * FROM `sections` ORDER BY `parent`, `name`";

[knowj]

but each parent isnt going to have a defining parent so i would need some way of relating each name (that is a parent) to its childern

[BrainDonor]

what if you select unique parent and use that in a while loop to gather the child items in their own loop?

[knowj]

use 2 SQL querys?

[bokeh]

Using code from the tread I directed you to yesterday (http://www.webdeveloper.com/forum/showpost.php?p=546565&postcount=14) I came up with the following:<?php

# Start: config
mysql_connect('localhost', $_SERVER["MYSQL_USER"], $_SERVER["MYSQL_PASS"]);
mysql_select_db('test');
$tablename = 'my_test_table';
$root_item = 'item1';
# End: config

// create a test table
mysql_query("
CREATE TABLE `$tablename` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`parent` VARCHAR( 255 ) NULL ,
`parent_id` INT UNSIGNED NULL
) ENGINE = innodb
");

// fill it with your test data
mysql_query("
INSERT INTO `$tablename`
(`name`,`parent`)
VALUES
('item1',null),
('item2','item1'),
('item3','item1'),
('item4','item1'),
('item5','item2'),
('item6','item2')
");

// build a 3D array of your tree structure
$query = "SELECT * FROM `$tablename` WHERE `name` = '$root_item' LIMIT 1";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0)
{
$branch[$root_item] = branches(mysql_fetch_assoc($result), $tablename);
}
else
{
$branch = 'no result returned';
}

// drop the test table
mysql_query("DROP TABLE `$tablename`");

//test output
header('Content-Type: text/plain');
print_r($branch);

function branches($row, $tablename)
{
$query = "SELECT * FROM `$tablename` WHERE `parent` = '{$row['name']}'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$branch[$row['name']] = branches($row, $tablename);
}
return !empty($branch)?$branch:null;
}


?>

[knowj]

thanks for the help this is how i decided to solve the problem in the end:

Returning the sections(navigation)
<dl>
<?php
$product->view_parent_sections();
$fields = array(
'id' => 'text',
'name' => 'text',
'parent' => 'text');
$sections = $product->drowstoarray($fields);
for($i=0;$i<=count($sections[$i]);$i++)
{
echo '<dt><a href="index.php?id='.$sections[$i]['id'].'">'.$sections[$i]['name'].'</a></dt>'."\n\r";
$product->view_child_sections($sections[$i]['id']);
$child = $product->drowstoarray($fields);
if (!empty($child))
{
for($n=0;$n<=count($child[$n]);$n++)
{
echo '<dd><a href="index.php?id='.$child[$n]['id'].'">'.$child[$n]['name'].'</a><dd/>'."\n\r";
}
}
$child = '';
}
?>
</dl>

class_database

function drowstoarray($array)
{
$i = 0;
while ($row = mysql_fetch_assoc($this->results))
{
foreach($array as $name=>$value)
{
$return[$i][$name] = $this->check_type($row[$name], $value);
}
$i++;
}
return $return;
}

function check_type($input, $type)
{
//usable types: text, image, video, price
switch ($type)
{
case 'text':
return $input;
break;
case 'image':
return '<img src="'.$input.'"/>';
break;
case 'video':
return '<object width="100" height="100"><param name="movie" value="'.$input.'"></param><param name="wmode" value="transparent"></param><embed src="'.$input.'" type="application/x-shockwave-flash" wmode="transparent" width="100" height="100"></embed></object>';
break;
case 'price':
return '&pound;'.$input;
break;
default:
return $input;
break;
}
}


class_products

function view_parent_sections()
{
$this->query = "SELECT * FROM `sections` WHERE `parent`='0' ORDER BY `name` ASC";
$this->execute();
}

function view_child_sections($id)
{
$this->query = "SELECT * FROM `sections` WHERE `parent`='$id' AND `parent`>'0' ORDER BY `name` ASC";
$this->execute();
}