|
|
Posted by Rik on 02/08/07 14:49
Jan Morten Sørensen <jms@sensewave.com> wrote:
> "P Pulkkinen" <perttu.POISTATAMA.pulkkinen@POISTATAMA.elisanet.fi> skrev
> i
> meddelandet news:39Eyh.7701$T22.2430@reader1.news.saunalahti.fi...
>>
>> "Jan Morten Sørensen" <jms@sensewave.com> kirjoitti
>> viestissä:52056$45ca8cfe$54d23962$9774@news.chello.no...
>>
>> > I have this all stored in a mysql-database in the following tables
>> > Category
>> > id(integer, autonumber)
>> > name(text)
>> > language(integer)
>>
>> > Category_relationships
>> > id(integer, autonumber)
>> > childid(integer)
>> > parentid(integer)
>> > language(integer)
>> ¨
>> Hello. Like other person already mentioned, there is some database
>> optimisation need. Parent_id in category table itself would be
>> sufficient,
>> becuse table CAN refer to itself, also. Not that it would greatly make
> thing
>> so much different though. But then in quiries you can do SOMETHING like:
>>
>> select * from category as thechild, category as theparent where
>> thechild.parentid = theparent.id (and then other conditions);
>> (sorry if there's some typos etc., my mysql syntax is in "passive
>> memory"
>> :-)
> What then if I wanted to have multiple parents maybe even from different
> tree levels?
> (Is that possible with your solution?)
>
> I wrote a solution to this last nite after posting this question and it
> was
> smashing. I am thinking about publishing it somewhere because I know
> others
> have the same problem as I do.
A relational table isn't that uncommon.
I'd trim it down though:
Category
id(integer, autonumber)
name(text)
language(integer)
Category_categories
parent_id (int, index)
child_id (int, index)
(possibly: order (int, index), for a custom order of subcategories in a
categorie)
Keep in mind that 'main' categories (with no parent) should have an entry
in the category_categories with parent_id either 0 (as this will never be
assigned as autonumber, or NULL). Be very, very aware that this could
result in everlasting loop when for instance id 1 is both a parent and a
child of id 2...
Now, the next bit has some unwanted overhead, but is a rather simple way
to do it (untested, might need some debugging):
<?php
$list = array(0 => array('childs' => 'array'));
$list_result = mysql_query('SELECT `id`, `name`,`language` FROM
`category`');
while($row = mysql_fetch_assoc($list_result)){
$list[intval($row['id'])] = $list;
}
$relations_result = mysql_query('SELECT `parent_id`, `child_id` FROM
`category_categories` ORDER BY `parent_id`, `order`');
while($row = mysql_fetch_assoc($relations_result)){
if(!isset($list[$row['parent_id']]['childs']))
$list[$row['parent_id']]['childs'] = array();
$list[$row['parent_id']]['childs'][] =& $list[$row['child_id']];
}
function html_nested_lists($array, $safety = 0){
if($safety == 30){
trigger_error('html_nested_lists: nesting to deep, 30 levels allowed');
return false;
}
$safety++;
if(!is_array($array) || empty($array)) return '';
$return = '<ul>';
foreach($array as $item){
$return .= '<li>'.$item['name'];
if(isset($item['childs'])) $return .=
html_nested_lists($item['childs'],$safety);
$return .= '</li>';
}
return $return;
}
echo html_nested_lists($list[0]['childs']);
?>
--
Rik Wasmus
Navigation:
[Reply to this message]
|