Hello
I think, there's a better solution:
Use one database - field for sorting (for example SORT). Then you need
to fill in
values in this field, that satisfy your needs:
Root - Item
1. Hello You
1.1. test1
1.2. test2
2. cool
2.1 cool i
2.2 cool ii
...
The values of the sort - fields will be:
1. 0001:00000
1.1 0001:0001:00000
1.2 0001:0002:00000
2. 0002:00000
2.1. 0002:0001:00000
2.2 0002:0002:00000
now you only need something like that:
select * from ... where ... order by SORT
that's very fast, witch is far more important that the overhead you have
to fill in
the SORT - values. There are other advantages:
If you count the :, denn you can see how deep you are in the Tree. If
your user
clicked on the tree and you know the value of the sort - fields the user
clicked on,
you know, whether the item you are outputting "is on the way" to your
selected item.
If it is, then it is a prefix of the item the user clicked on (you need
to remove
00000 for that).
hope that helps?
greetings from Switzerland
Patrick
Carsten Gehling wrote:
> Sorry I wasn't really awake, and didn't notice which list you'd posted
on
> :-) Here's an example in PHP (not tested):
>
> ==============================================
> $cat_list = array();
> $sql = "select id, parent_id, category from your_table where order by
> parent_id, category ";
> $res = mysql_db_query($dbName, $sql) or die("Fejl ved query: $sql<hr>"
.
> mysql_error());
> while ($row = mysql_fetch_assoc($res))
> $cat_list[] = $row;
>
> showcattree(0, 0, $cat_list);
>
> function showcattree($parent_id, $niveau, &$cat_list) {
> $tabStr = str_repeat(" ", $niveau*3);
>
> for ($i=0; $i<count($cat_list); $i++) {
> $local_id = $cat_list[$i]["id"];
> $local_parent_id = $cat_list[$i]["parent_id"];
> $local_navn = $cat_list[$i]["titel"];
>
> if ($local_parent_id == $parent_id) {
> echo "$tabStr$local_navn<br>";
> showcattree($local_id, $niveau+1, $cat_list);
> $count++;
> }
> }
> }
> ==============================================
>
> Please note that cat_list is given as a by-reference parameter.
Otherwise
> you'd be copying the whole array for each recursive function call.
>
> The trick is, that to make a tree you always need to use recursive
> functions. The performance is gained by loading the complete list from
> database at once (instead of calling a mysql SELECT statement for each
> branch of the tree).
>
> - Carsten
>
> > -----Oprindelig meddelelse-----
> > Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
> > Sendt: 27. maj 2002 20:33
> > Til: [EMAIL PROTECTED]
> > Emne: SV: building tree view in mysql?
> >
> >
> > Well you'll neew to do some coding in your application language.
> > What are you using to connect to MySQL? Perl? PHP? Java?
> >
> > Tell me, and I'll give you an example where you only query the
> > database once (for optimum performance).
> >
> > - Carsten
> >
> > > -----Oprindelig meddelelse-----
> > > Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
> > > Sendt: 27. maj 2002 20:50
> > > Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > > Emne: Re: building tree view in mysql?
> > >
> > >
> > >
> > > Yeah, thats exactly what I wanted to do. But how do I make MySQL
sort it
> > > like that? Show the parent first, and then all its childs, so the
> > > application can determite if it's subcategory by checking if
> > the parent is
> > > was changed.
> > >
> > > Sagi
> > >
> > > From: "olinux" <[EMAIL PROTECTED]>
> > >
> > >
> > > > create a table like this:
> > > > id | parent_id | category
> > > >
> > > > parent_id points to the parent categories id. Top
> > > > level categories get parent_id of '0'
> > > >
> > > > ex:
> > > > 1 | 0 | auto
> > > > 2 | 1 | repair
> > > > 3 | 2 | body
> > > > 4 | 2 | windshields
> > > > 5 | 1 | detailing
> > > > 6 | 2 | tires
> > > >
> > > > Tree would look like:
> > > > auto
> > > > repair
> > > > body
> > > > windshields
> > > > tires
> > > > detailing
> > > >
> > > > olinux
> > > >
> > >
> > >
> > >
> > >
> > >
---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail
<[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
