Re: [PHP-DB] Generating view of tree?
Craig -- ...and then Craig Kohtz said... % ... % The script I've included works for a table named "structure" with the % following fields: Sounds interesting. Care to actually post the script? ;-) I'd like to see it. TIA & HAND :-D -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! pgp0.pgp Description: PGP signature
RE: [PHP-DB] Generating view of tree?
What's your table structure look like? I've done this in a couple projects. I use a parent child relationship. Usually I make it so you can expand and collapse the tree by clicking on a plus or minus, the script I've attached doesn't have this feature for simplicity sake. parent child description 1 0 Category 2 1Sub-category 3 2 Sub-sub-category 4 1Another sub-category 5 0 Category 6 5Sub-category etc... Create a recursive function (a function that calls itself) that starts where the child = 0, then looks for child = parent. Keeping track of the indenting is tricky, but not impossible. Since it is recursive, it probably does use a lot of queries, so don't know if it's what you want or not. The script I've included works for a table named "structure" with the following fields: structure - s_parent int not null auto_increment, primary key (s_parent) s_child int s_desc varchar(255) I tested the script on this table just now, after taking out a lot of junk that was doing other things (adding graphics, keeping track of expansion, etc). I don't warrent or support this script in any way shape or form. To the best of my knowledge it works as described above, and you can use it however you like, but like I said, it's just for use as an example. Oh, and if you use it, include the comment at the top please. :) Thanks. -Original Message- From: Blain [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 9:35 PM To: Leif K-Brooks; [EMAIL PROTECTED] Subject: AW: [PHP-DB] Generating view of tree? If you can sort it and give all entrys a indent number its easy to read the tree out. -Ursprungliche Nachricht- Von: Leif K-Brooks [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 27. Marz 2003 03:19 An: [EMAIL PROTECTED] Betreff: [PHP-DB] Generating view of tree? I have a table with a tree. Thing is, I need to generate a view of it like: Category Sub-category Sub-sub-category Another sub-category Another category Sub-category Any way to do this, without using a huge number of queries? Using MySQL. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Generating view of tree?
Also just saw a new PEAR package posted that helps you
work with nested sets. Haven't tried it yet.
http://pear.php.net/package-info.php?package=DB_NestedSet
olinux
--- Paul Burney <[EMAIL PROTECTED]> wrote:
> on 3/26/03 9:18 PM, Leif K-Brooks at
> [EMAIL PROTECTED] appended
> the following bits to my mbox:
>
> > I have a table with a tree. Thing is, I need to
> generate a view of it like:
> > Category
> > Sub-category
> > Sub-sub-category
> > Another sub-category
> > Another category
> > Sub-category
> >
> > Any way to do this, without using a huge number of
> queries? Using MySQL.
>
> This comes upon the list every few weeks. Check the
> archives for more
> information.
>
> Basically, in MySQL there is no way to do it without
> using a large number of
> queries if you have the traditional table layout
> with a record_id and a
> parent_id in each, so that the table relates to
> itself.
>
> The only suggestion I can really offer is to make
> sure you are indexing the
> parent_id as well as the record_id.
>
> In the php code, make it a little less nasty by
> using a function
> recursively, for example:
>
> function list_sub_cats($p = 0) {
>
> $str = '';
> $q = 'SELECT cat_id,cat_name FROM cats WHERE
> cat_parent="' . $p . '"';
> $r = mysql_query($q,$dbh);
> if (mysql_num_rows($r) > 0) {
> $str .= '';
> while ($s = mysql_fetch_assoc($r)) {
> $str .= '' . $s['cat_name'];
> $str .= list_sub_cats($s['cat_id']);
> $str .= '';
> }
> $str .= '';
> }
> return $str;
> }
> ?>
>
> Calling list_sub_cats() above should return a nested
> unordered list in HTML
> of your categories (hasn't been tested, though).
>
> If you were using Oracle, you could use a CONNECT BY
> term in your query or
> write a stored procedure to give you back the tree.
> See this site for
> details:
>
>
>
> If you aren't tied to that database structure, you
> could investigate the
> must faster de-normalized alternative nested set
> model that is often
> mentioned on this list:
>
>
>
>
>
> Hope that helps.
>
> Sincerely,
>
> Paul Burney
>
Re: [PHP-DB] Generating view of tree?
--- Paul Burney <[EMAIL PROTECTED]> wrote: > on 3/26/03 9:18 PM, Leif K-Brooks at > [EMAIL PROTECTED] appended > the following bits to my mbox: > > > I have a table with a tree. Thing is, I need to > generate a view of it like: > > Category > > Sub-category > > Sub-sub-category > > Another sub-category > > Another category > > Sub-category > > > > Any way to do this, without using a huge number of > queries? Using MySQL. > > This comes upon the list every few weeks. Check the > archives for more > information. > > Basically, in MySQL there is no way to do it without > using a large number of > queries if you have the traditional table layout > with a record_id and a > parent_id in each, so that the table relates to > itself. > There is one way - grab the entire table and loop it into a multi-array and process it with PHP. $categories[parent_id][category_id] So all subcats of category_id 5 are in $categories[5] Of course this will not be the best approach for large category structures. You can use basically the same code flow as the recursive loop Paul posted to go thru this array. instead of query for subcats you already have them in the array. __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Generating view of tree?
If I understand correctly and you have the data all in one table,
you can just query all the data that goes into the report, sorted
by categories, and output the data in a loop. This code will give
you an ordered list indented and numbered by level. It's not
perfect, but give you the idea...
$qstr="SELECT * FROM table ORDER BY cat1, cat2, cat3";
$rslt=mysql_query($qstr);
$cat1="x";
$cat2="x";
$cat3="x";
while ($row = mysql_fetch_array($rslt)) {
if ($cat1!=$row['cat1']) {
$indent="1-";
$cat1=$row['cat1'];
echo "$indent $cat1";
}
if ($cat2!=$row['cat2']) {
$indent=" 2-";
$cat2=$row['cat2'];
echo "$indent $cat2";
}
if ($cat3!=$row['cat3']) {
$indent="3-";
$cat3=$row['cat3'];
echo "$indent $cat3";
}
}
Hope that helps.
--
Best regards,
Randy
Wednesday, March 26, 2003, 8:18:50 PM, you wrote:
> I have a table with a tree. Thing is, I need to generate a view of it like:
> Category
> Sub-category
> Sub-sub-category
> Another sub-category
> Another category
> Sub-category
> Any way to do this, without using a huge number of queries? Using MySQL.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Generating view of tree?
on 3/26/03 9:18 PM, Leif K-Brooks at [EMAIL PROTECTED] appended
the following bits to my mbox:
> I have a table with a tree. Thing is, I need to generate a view of it like:
> Category
> Sub-category
> Sub-sub-category
> Another sub-category
> Another category
> Sub-category
>
> Any way to do this, without using a huge number of queries? Using MySQL.
This comes upon the list every few weeks. Check the archives for more
information.
Basically, in MySQL there is no way to do it without using a large number of
queries if you have the traditional table layout with a record_id and a
parent_id in each, so that the table relates to itself.
The only suggestion I can really offer is to make sure you are indexing the
parent_id as well as the record_id.
In the php code, make it a little less nasty by using a function
recursively, for example:
0) {
$str .= '';
while ($s = mysql_fetch_assoc($r)) {
$str .= '' . $s['cat_name'];
$str .= list_sub_cats($s['cat_id']);
$str .= '';
}
$str .= '';
}
return $str;
}
?>
Calling list_sub_cats() above should return a nested unordered list in HTML
of your categories (hasn't been tested, though).
If you were using Oracle, you could use a CONNECT BY term in your query or
write a stored procedure to give you back the tree. See this site for
details:
If you aren't tied to that database structure, you could investigate the
must faster de-normalized alternative nested set model that is often
mentioned on this list:
Hope that helps.
Sincerely,
Paul Burney
