This problem is already solved in Oracle. It it the CONNECT BY PRIOR
statement which recursively queries a table.
The good news is that I read in the MySQL manual, that this feature will be
implemented in MySQL in the Real Near Future :-).
(MySQL manual: "1.8.2 Things That Must be Done in the Real Near Future",
page 47 in the PDF manual.)
At 15:57 2002.06.06. +0200, you wrote:
>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
---------------------------------------------------------------------
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
