Re: SV: building tree view in mysql?
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:0
>1.1 0001:0001:0
>1.2 0001:0002:0
>2. 0002:0
>2.1. 0002:0001:0
>2.2 0002:0002:0
>
>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
>0 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"
>.
> > 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 > $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";
> > 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.
> > > >
> > &
Re: SV: building tree view in mysql?
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:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
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
0 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"
.
> 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 $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";
> 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
> > > >
> > >
> >
Re: SV: building tree view in mysql?
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:0
1.1 0001:0001:0
1.2 0001:0002:0
2. 0002:0
2.1. 0002:0001:0
2.2 0002:0002:0
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
0 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" .
> 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 $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";
> 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
> > > >
> > >
> >
SV: building tree view in mysql?
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" .
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";
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
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
