[PHP] Chris-Re: [PHP] Category and sub-category logic
Hey, Sorry I didnt reply, was having some computer problems. Thanks for the link and the advise, will look into it. Cheers, -Ryan We will slaughter you all! - The Iraqi (Dis)information ministers site http://MrSahaf.com - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: David Otton [EMAIL PROTECTED]; Ryan A [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, August 15, 2003 2:04 AM Subject: RE: [PHP] Category and sub-category logic David Otton mailto:[EMAIL PROTECTED] on Thursday, August 14, 2003 4:58 PM said: The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). Which is why you should use the Modified Preorder Tree Traversal method instead. With that method you've got one table that defines the categories AND their relationships to each other. Your SQL queries end up looking something like this. SELECT name FROM categories WHERE lft = x AND rgt = y Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Category and sub-category logic
Read this article: http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17 /4047/index.html It explains the 4 different methods that you can use when working with hierarchical data. -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 3:21 PM To: [EMAIL PROTECTED] Subject: [PHP] Category and sub-category logic Hi, I am thinking of making a program for personal use which does a very simple thing, just displays listings under categories, eg: under main category Auto there would be cars,bikes etc under banking there would be financing,loans etc (I as admin create master and sub-categories) then if you click on cars you would either see a sub category or all the listings there I have never done a project like this so am a bit confused but I am pretty sure quite a few of you must have done this because its a bit common on the net and while I am kind of new to php, most of you guys are fossils :-D So far the logic I have worked out is: Create a master_category table for the main categories, a child_table for the subs, a the_listings table for the details which will have a reference (number or word) field which will be to keep a reference as to which category/sub-category it belongs to.. Tell me if my logic is wrong or I missed anything The part where I am confused is, on the front page (say index.php) how do I display all the categories and sub categories in the correct order? eg: Auto --Cars --Bikes Banking --Finances --Loans Women --Boring but REALLY good looking --Great fun but not-so-good-looking Any ideas? I searched hotscripts and google but found crappy programs like phpyellow and zclassifieds which are really no good to me. If you know of something that already does the above kindly share it with me. Thanks, -Ryan --- - The government announced today that it is changing it's emblem to a condom because it more clearly reflects the government's political stance. A condom stands up to inflation, halts production, destroys the next generation, protects a bunch of pricks, and gives you a sense of security while you're actually getting screwed. --- - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Category and sub-category logic
I'm trying to get a handle on this algorithm as well. If you add additional children to a parent record, doesn't that require renumbering all the rgt and lft values in the lineage? For example (table shamelessly stolen from a website): Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 If I want to add 'Joe' as a subordinate to 'Fred', doesn't that mean I have to change the lft values for Fred, Chuck, and Albert? Yes, deleting definitely seems easier. I'm curious about adding... Mark --- Chris W. Parker [EMAIL PROTECTED] wrote: David Otton mailto:[EMAIL PROTECTED] on Thursday, August 14, 2003 4:58 PM said: The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). Which is why you should use the Modified Preorder Tree Traversal method instead. With that method you've got one table that defines the categories AND their relationships to each other. Your SQL queries end up looking something like this. SELECT name FROM categories WHERE lft = x AND rgt = y Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Category and sub-category logic
From: Mark [EMAIL PROTECTED] I'm trying to get a handle on this algorithm as well. If you add additional children to a parent record, doesn't that require renumbering all the rgt and lft values in the lineage? For example (table shamelessly stolen from a website): Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 If I want to add 'Joe' as a subordinate to 'Fred', doesn't that mean I have to change the lft values for Fred, Chuck, and Albert? Yes, deleting definitely seems easier. I'm curious about adding... Yes, you will have to update all of the right and left numbers to the right (or higher) than the node where you are inserting. Deleting is basically the same method done in reverse, but can get complicated when you delete a node and have to determine what to do with the children. I'd recommend, if you're really interested in this, to do some searching on nested set tutorials. You could specifically search for Joe Celko who has written a lot of information on the topic. While it seems like a big deal to have to update all of the numbers, it's really not. It can be done with a single query (with subselects). Yes, it's more costly than inserting a name into an adjacentcy list model, but it's worth it for all that you gain from the nested set model. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Category and sub-category logic
Wouldn¹t it just be easier to do a relationship table as in Personnel emp ID parent 'Albert' 1 0 'Bert'2 1 'Chuck' 3 1 'Donna' 4 0 'Eddie' 5 4 'Fred'6 0 'Joe' 7 6 This way you know the ID of the person and the parentID if they have one, otherwise they are a 0 which means they are the parent.. Seems like this would be a much easier way of doing it doesn¹t it? And only one SQL query... Rick The only way to have a friend is to be one. - Ralph Waldo Emerson From: Mark [EMAIL PROTECTED] Date: Fri, 15 Aug 2003 07:18:41 -0700 (PDT) To: Chris W. Parker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [PHP] Category and sub-category logic I'm trying to get a handle on this algorithm as well. If you add additional children to a parent record, doesn't that require renumbering all the rgt and lft values in the lineage? For example (table shamelessly stolen from a website): Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 If I want to add 'Joe' as a subordinate to 'Fred', doesn't that mean I have to change the lft values for Fred, Chuck, and Albert? Yes, deleting definitely seems easier. I'm curious about adding... Mark --- Chris W. Parker [EMAIL PROTECTED] wrote: David Otton mailto:[EMAIL PROTECTED] on Thursday, August 14, 2003 4:58 PM said: The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). Which is why you should use the Modified Preorder Tree Traversal method instead. With that method you've got one table that defines the categories AND their relationships to each other. Your SQL queries end up looking something like this. SELECT name FROM categories WHERE lft = x AND rgt = y Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Category and sub-category logic
--- CPT John W. Holmes [EMAIL PROTECTED] wrote: From: Mark [EMAIL PROTECTED] I'm trying to get a handle on this algorithm as well. If you add additional children to a parent record, doesn't that require renumbering all the rgt and lft values in the lineage? For example (table shamelessly stolen from a website): Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 If I want to add 'Joe' as a subordinate to 'Fred', doesn't that mean I have to change the lft values for Fred, Chuck, and Albert? Yes, deleting definitely seems easier. I'm curious about adding... Yes, you will have to update all of the right and left numbers to the right (or higher) than the node where you are inserting. Deleting is basically the same method done in reverse, but can get complicated when you delete a node and have to determine what to do with the children. I'd recommend, if you're really interested in this, to do some searching on nested set tutorials. You could specifically search for Joe Celko who has written a lot of information on the topic. Thanks. I'll take a look. I'm considering changing to this algorithm from my basic parent-ID table. While it seems like a big deal to have to update all of the numbers, it's really not. It can be done with a single query (with subselects). Yes, it's more costly than inserting a name into an adjacentcy list model, but it's worth it for all that you gain from the nested set model. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Category and sub-category logic
From: Richard Baskett [EMAIL PROTECTED] Wouldn¹t it just be easier to do a relationship table as in Personnel emp ID parent 'Albert' 1 0 'Bert'2 1 'Chuck' 3 1 'Donna' 4 0 'Eddie' 5 4 'Fred'6 0 'Joe' 7 6 This way you know the ID of the person and the parentID if they have one, otherwise they are a 0 which means they are the parent.. Seems like this would be a much easier way of doing it doesn¹t it? And only one SQL query... Possibly. It depends on what you're trying to model. We're getting way off topic for PHP, though. What you gave above is called an adjacency list model and the other example given was a nested set (or tree) model. Each has it's advantages and disadvantages. I recommend counsulting your good friend Google if you're interested in the details. :) ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Category and sub-category logic
Ryan A mailto:[EMAIL PROTECTED] on Thursday, August 14, 2003 3:21 PM said: Read this http://www.sitepoint.com/article/1105 and you will know what you should do. The database organization you want to use is called Modified Preorder Tree Traversal. It takes only one table to create all the relationships needed. If you have any questions about this technique send me an email as I've got some experience with this method. Chris. p.s. The easiest way (I think) to learn this method is to actually use your own data and draw it out on a piece of paper (as opposed to trying to visualize the map in your head). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Category and sub-category logic
On Fri, 15 Aug 2003 00:20:58 +0200, you wrote: Hi, I am thinking of making a program for personal use which does a very simple thing, just displays listings under categories, eg: under main category Auto there would be cars,bikes etc under banking there would be financing,loans etc (I as admin create master and sub-categories) So far the logic I have worked out is: Create a master_category table for the main categories, a child_table for the subs, a the_listings table for the details which will have a reference (number or word) field which will be to keep a reference as to which category/sub-category it belongs to.. Tell me if my logic is wrong or I missed anything The part where I am confused is, on the front page (say index.php) how do I display all the categories and sub categories in the correct order? You need a pig's ear on the database table. It's really simple once you've seen it done. Basically, each row in the database contains a reference to its parent's primary key. Call our table category. categoryid parentid title 1 0 Auto 2 1 Cars 3 1 Bikes 4 0 Banking 5 4 Finances 6 4 Loans SELECT title FROM category WHERE parentid = 0 gives us the root elements in the tree (Auto, Banking) while SELECT title FROM category WHERE parentid = 4 gives us the children of Banking (Finances, Loans). The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). The fun thing about this structure is writing the delete function. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Category and sub-category logic
David Otton mailto:[EMAIL PROTECTED] on Thursday, August 14, 2003 4:58 PM said: The advantage of doing it this way is that your tree structure is generic and can have many levels. The disadvantage is that you may need many SQL queries to fully traverse the tree (though people rarely want to do this, and sub-selects, clever joins or post-query processing can reduce the overhead). Which is why you should use the Modified Preorder Tree Traversal method instead. With that method you've got one table that defines the categories AND their relationships to each other. Your SQL queries end up looking something like this. SELECT name FROM categories WHERE lft = x AND rgt = y Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
